CODEWITHSUNDEEP
sql-servertriggers
Taryn
Taryn

Reputation: 247610

SQL Server Update Trigger

I have never used triggers before in SQL server and I have looked around online but haven't found an answer to my question. Basically I am trying to write an Trigger that will run after a record has been updated in a 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 be updating one record using a query like this:

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

I then 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 Guid that I want updated is being used by the primary table. But I can't figure out how I get the @Guid that I want updated into the trigger? Please help.

Thanks

Upvotes: 6

Views: 20884

Answers (2)

Martin Smith
Martin Smith

Reputation: 452947

Triggers in SQL Server operate on sets of rows not individual rows. You access these via the inserted and deleted pseudo tables. Assuming that you might want the value of isactive to cascade when previously inactive rows were made 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

Upvotes: 6

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

Both the answers already posted suffer from the same problem - they're marking the other rows as Inactive, whenever any update occurs on 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

Would be more appropriate

Upvotes: 12

Related Questions