Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ozSQLServer
Starting Member
32 Posts |
 Posted - 2011-12-19 : 20:27:56
|
Hi,If I have a DML trigger as follows:CREATE TRIGGER trg_OrdersON sa.OrdersAFTER INSERT, UPDATE, DELETEAS... How can I know in the body of the trigger which operation fired the trigger: INSERT, UPDATE or DELETE?Cheers,ozSQL |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-19 : 21:00:22
|
Within the trigger code, you can examine the INSERTED and DELETED virtual tables to determine what caused the trigger to fire:IF NOT EXISTS(SELECT * FROM INSERTED) PRINT 'Deleted';ELSE IF NOT EXISTS (SELECT * FROM DELETED) PRINT 'Inserted';ELSE PRINT 'Updated';RETURN; If no rows are affected (the trigger would still fire) you may get incorrect results. If that is a possibility, then you couldn't know which operation fired the trigger, but in that case it probably doesn't matter. |
 |
|
|
ozSQLServer
Starting Member
32 Posts |
Posted - 2011-12-19 : 22:03:18
|
| That sounds nice!Thank you.Cheers,ozSQL |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-12-19 : 23:20:01
|
quote:
Originally posted by ozSQLServer
Hi,If I have a DML trigger as follows:CREATE TRIGGER trg_OrdersON sa.OrdersAFTER INSERT, UPDATE, DELETEAS... How can I know in the body of the trigger which operation fired the trigger: INSERT, UPDATE or DELETE?Cheers,ozSQL
Wouldnt it be more logical that you create 3 different triggers for 3 different operations ?PBUH |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
how to know which operation fired the DML trigger