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