SQL Server Update Trigger

Question

I had never used triggers before on a SQL server, and I looked it up on the Internet but could not find the answer to my question. Basically I am trying to write a trigger that will be fired after the record is updated in the table. This trigger will then update two additional tables based on the record that was updated in the first table.

The primary table with the trigger on it will update one record using this query:

UPDATE E.SM_T_RList SET IsActive = 0 WHERE Guid = @Guid 

Then I want the trigger to do something like this:

 ALTER TRIGGER [E].[IsActiveUpdate] ON [E].[SM_T_RList] AFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE E.SM_T_BInfo SET IsActive = 0 WHERE Guid = @Guid UPDATE E.SM_T_RMachines SET IsActive = 0 WHERE GUID = @GUID END 

The manual that I want to update is used by the main table. But I can not understand how I get @Guid, which I want to update in a trigger? Please, help.

thanks

Answer 1

Both answers already posted suffer from the same problem - they mark the other rows as inactive, whenever any update happens in your base table

Something like:

 ALTER TRIGGER [E].[IsActiveUpdate] ON [E].[SM_T_RList] AFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE E.SM_T_BInfo SET IsActive = 0 WHERE Guid IN (SELECT Guid FROM INSERTED where IsActive=0) UPDATE E.SM_T_RMachines SET IsActive = 0 WHERE Guid IN (SELECT Guid FROM INSERTED where IsActive=0) END 

It would be more appropriate

Answer 2

Triggers in SQL Server work with rowsets, not single rows. You access them through the inserted and deleted inserted - deleted . Assuming you might need a cascading isactive value when previously inactive lines were active, you could use something like this.

 ALTER TRIGGER [E].[IsActiveUpdate] ON [E].[SM_T_RList] AFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE E.SM_T_BInfo SET IsActive = i.IsActive FROM INSERTED i JOIN E.SM_T_BInfo e ON e.Guid = i.Guid UPDATE E.SM_T_RMachines SET IsActive = i.IsActive FROM INSERTED i JOIN E.SM_T_BInfo e ON e.Guid = i.Guid END