在SQL Server 2008 R2中触发

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

我试图在SQL Server 2008 R2中创建一个触发器。我有三张桌子,他们有PK-FK -FK的关系。

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.

我在第一个表中有一列,列名为STATUS,列的值为'inactive'或'active',在第二个表中我有列FKNEWLoanreviewfieldid,其中有多个值C2,C4,C6,在第三个表中我有FKWarningtypeID具有不同的值像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'.

我必须创建一个触发器,以便如果FKwarningtypeID更新为值4,那么在第一个表(我有状态列)中,status的列值将被修改为“active”。

Can any body help me please.

任何身体都可以帮助我。

1 个解决方案

#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