Creating Tricker when Insert Update
– ================================================
– 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
CASE WHEN in sql command
SELECT Name, RatingID AS Rating,
CASE RatingID
WHEN ‘R’ THEN ‘Under 17 requires an adult.‘
WHEN ‘X’ THEN ‘No one 17 and under.‘
WHEN ‘NR’ THEN ‘Use discretion when renting.‘
ELSE ‘OK to rent to minors.‘
END AS Policy
FROM DVDs
ORDER BY Name;
backup restore sql server 2005
RESTORE DATABASE PersonnelTestFixDept
FROM DISK = ‘C:\db1.bak’
WITH REPLACE