CODEWITHSUNDEEP
sql-servertriggers
Shiva
Shiva

Reputation: 235

Updating the table in a trigger written in sql server

I am trying to create a trigger which is triggered when an insert, delete happens in a table 'Abschaetzung_has_Varianten' and updates a table called 'Flag'. I need to select an ID from the same table to update the Flag table. Is the syntax wrong in writing the SELECT of the ID? I don't seem to get the @abschID from the select. Could anyone help me in this. Thank you.

CREATE TRIGGER trig_update_flag on [Abschaetzung_has_Varianten]   
after insert, delete   
As  
Begin  
   DECLARE @x INT;  
   DECLARE @abschID INT;  
   DECLARE @value INT;  
   SELECT @value = 1;  
   SELECT @abschID = (SELECT TOP 1 Abschaetzung_ID FROM Abschaetzung_has_Varianten ORDER BY Abschaetzung_ID DESC);  
   SELECT @x = Count(*) FROM Flag WHERE AbschaetzID = @abschID   
   If @x > 0  
   Begin  
      UPDATE Flag Set [Flag] = @value WHERE AbschaetzID = @abschID;  
   end  
end

Upvotes: 1

Views: 838

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19184

Your code should be more like this:

CREATE TRIGGER trig_update_flag on [Abschaetzung_has_Varianten]   
after insert, delete  
as
begin
 UPDATE Flag Set [Flag] = 1
 WHERE AbschaetzID IN (SELECT DISTINCT Abschaetzung_ID FROM INSERTED)

 UPDATE Flag Set [Flag] = 1
 WHERE AbschaetzID IN (SELECT DISTINCT Abschaetzung_ID FROM DELETED)

end

INSERTED is a special trigger pseudo table that contains all of the updated or inserted records.

DELETED is a special trigger pseudo table that contains all of the deleted records.

This table may contain many records for one invocation of the trigger.

The code above is not the most efficient and may not suit your exact requirements but hopefully you get the idea.

Upvotes: 2

Related Questions