Trigger in SQL Server 2008 R2

Question

I am trying to create a trigger in SQL Server 2008 R2. I have three tables and they have PK-FK -FK relationship.

I have a column in first table where column name is STATUS that column has value 'inactive' or 'active' and in second table I have column FKNEWLoanreviewfieldid which has multiple values C2,C4,C6 and in third table I have FKWarningtypeID has different value like 4,3,2,1.

I have to create a trigger so that if FKwarningtypeID is updated to value 4 then in first table (where I have status column) the column value of status will be modified to 'active'.

Can any body help me please.

Answer 1

You need something like this:

CREATE TRIGGER trg_UpdateYourColumn 
FOR UPDATE ON dbo.YourThirdTableHere
AS
    -- update the first table 
    UPDATE 
        dbo.YourFirstTableHere
    SET 
        -- set [Status] to 'Active'
        -- (I sure hope this isn't *REALLY* stored as a *STRING* value!)
        [Status] = 'Active'   
    FROM 
        dbo.YourFirstTableHere t1
    INNER JOIN 
        -- join first and second table on some common column            
        dbo.YourSecondTableHere t2 ON t1.XX = t2.XX  
    INNER JOIN 
        -- join second and third table on some common column
        dbo.YourThirdTableHere t3 ON t2.YY = t3.YY  
    INNER JOIN 
        -- join third table with the "Inserted" pseudo table
        Inserted i ON t3.ID = i.ID    
    INNER JOIN 
        -- join third table with the "Deleted" pseudo table
        Deleted d ON t3.ID = d.ID
    WHERE
        i.FKwarningtypeID = 4        -- new value is 4
        AND d.FKwarningtypeID <> 4   -- old value was *NOT* 4