– ================================================
– Template generated from Template Explorer using:
– Create Trigger (New Menu).SQL
–
– Use the Specify Values for Template Parameters
– command (Ctrl-Shift-M) to fill in the parameter
– values below.
–
– See additional Create Trigger templates for more
– examples of different Trigger statements.
–
– This block of comments will not be included in
– the definition of the function.
– ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: <Author,,Name>
– Create date: <Create Date,,>
– Description: <Description,,>
– =============================================
CREATE TRIGGER BW_POI_SetCountryID
ON BW_POI
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
update bw_poi
set countryid = case
when i.country = ‘us’ or i.country = ‘usa’ or i.country like ‘united states%’ then 0
when i.country = ‘canada’ then 1
when i.country = ‘mexico’ or i.country = ‘mx’ then 2
when i.country = ‘australia’ then 3
when i.country = ‘thailand ‘ then 4
else null
end
from inserted i
inner join bw_poi p on i.[id] = p.[id]
END
GO
**************************select country, countryid, *
from bw_poi
where countryid is null and country <> ”
–update bw_poi set country = country