Checking Insert, Update, or Delete in Trigger

Question

Post reply

Checking Insert, Update, or Delete in Trigger

sifan

SSC Enthusiast

Points: 148
More actions
December 15, 2003 at 3:45 am

#83608

Hi all,
I have created the following trigger.
CREATE TRIGGER dbo.HRUpdate
ON Employee
FOR INSERT, UPDATE, DELETE
I want to check inside the trigger whether this triggered by which event (INSERT, UPDATE, DELETE) because I need to separate process for each event. Is possible to check the event.
Please help me.
Thank you,
Shifan.
qaqa

qaqa

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

DavidT SSCommitted Points: 1727 More actions · Answer 1

This may not be the best way, but I guess you could check the existance of rows in the inserted and deleted tables, eg:

If there is an entry in the inserted table, but not the deleted table, you know it's an insert.

If there is an entry in both, it's an update.

If there is only an entry in the deleted table, it's a delete...

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

If you need to check which event caused the trigger to fire, I would write three different triggers. For me this way the code became more manageable.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

noeld SSC Guru Points: 96590 More actions · Answer 3

I agree with the others, it is better to keep the code in separated triggers but just for the fun of it this is how you can do it:

IF SELECT COUNT(*) FROM inserted > 0

AND

SELECT COUNT(*) FROM deleted = 0

BEGIN

-- Insert Code

END

IF SELECT COUNT(*) FROM inserted > 0

AND

SELECT COUNT(*) FROM deleted > 0

BEGIN

-- Update Code

END

IF SELECT COUNT(*) FROM inserted = 0

AND

SELECT COUNT(*) FROM deleted > 0

BEGIN

-- Delete Code

END

* Noel

yogiberr Hall of Fame Points: 3679 More actions · Answer 4

hi, does this mean that it is better to write a separate trigger for type of operation that is performed on a table?

eg

trig1 =INSERT

trig2 =DELETE

trig3 = UPDATE

cheers,

yogi

NPeeters SSChampion Points: 12523 More actions · Answer 5

Yep, in almost all case it is better to write separate triggers.

I can only see the use of one trigger for all actions if you just want to record 'changes' in your table in 'HISTORY_INSERT' and 'HISTORY_DELETE' tables.

This would mean all new values (being it an insert or an update) would get written to the HISTORY_INSERT table and all old values (being it a delete or an update) would get written to the HISTORY_DELETE table.

yogiberr Hall of Fame Points: 3679 More actions · Answer 6

Cheers,

I'll now start hacking my first 3 triggers!

ta, yogi.