SQL Server triggers

Question

I'm creating a trigger in SQL Server after INSERT, UPDATE and DELETE. Is there a way to know inside the trigger code if it is running on the insert update or delete statement, because I need different behaviors on each case, or I need to create a trigger for each one of them.

Thanks in advance,

Paulo

Answer 1

There are datasets called INSERTED and DELETED. Depending on the action that invoked the trigger, there's data in these datasets. For updates, IIRC, the original data is in DELETED, the new data is in INSERTED.

See here.

Answer 2

There are logical temporary "inserted" and "deleted" table which you can access.

if there are some rows in both tables that means its updated.

You can get the new values by selecting querying "inserted" table.

Select * from inserted

and you can get the old values which are being updated by querying the "deleted" table.

Select * from deleted

Answer 3

You can use the AFTER keyword to designate separate triggers for each event if that suits you better.

Eg:-

CREATE TRIGGER FooIns ON TableBar
AFTER INSERT

CREATE TRIGGER FooUpd ON TableBar
AFTER UPDATE

Answer 4

If you need to implement "different behavior", then what is the point of trying to write only 1 trigger, instead of one trigger per operation?

Answer 5

Just use separate triggers!! It has been mentioned that Triggers are evil, but they become more evil if you start to include convoluted code to try and determine the action (Insert/Update/Delete) If anyone works on your triggers in the future, they'll only question WTF was he thinking creating unnecessary complexity!

Also it's worth remembering that updates can effect multiple rows so when the trigger is fired the 'deleted' & 'inserted' tables can have many rows!

Answer 6

As devio has stated, if you are doing different things for an INSERT and a DELETE, you could use two triggers. Just specify FOR INSERT or FOR DELETE after the CREATE TRIGGER line.

If you want to use one trigger, then, as Ismail hinted at, you could inspect the values of the temporary "inserted" and "deleted" tables that are given to you inside a trigger.

If the "deleted" table is empty then this trigger was fired by an INSERT statement; If "deleted" is not empty and the "inserted" table is empty, then the trigger was fired by a DELETE statement. If neither are empty, then an UPDATE statement will have been used.