Determine Whether Insert, Update, or Delete Trigger

[PGO01]

From within the trigger code - how can I tell whether the trigger has been fired from an insert, update, or delete?

The only thing I can think of is to check the Inserted and Deleted and use logic on these... for example in pseudocode:

IF COUNT(*) FROM Inserted IS NULL THEN Action = 'delete'
ELSE IF COUNT(*) FROM Deleted IS NULL THEN Action = 'insert'
ELSE Action = 'update'

Is there a better way?

 

[gmmastros]

If you want to perform different actions depending on whether the trigger was called as a result of Insert, Update, or delete, why don't you separate the logic by creating 3 different triggers?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

You could create 3 different triggers one for UPDATE one for INSERT and one for DELETE.
Other way is:

SELECT INSERTED.*
FROM INSERTED
LEFT JOIN DELETED ON INSERTED.PK = DELETED.PK
WHERE DELETED.PK IS NULL
--- The code above will give you all newly inserted records

SELECT DELETED.*
FROM DELETED
LEFT JOIN INSERTED ON DELETED.PK = INSERTED.PK
WHERE INSERTED.PK IS NULL
--- The code above will give you all deleted records

SELECT INSERTED.*
FROM INSERTED
INNER JOIN DELETED ON INSERTED.PK = DELETED.PK
--- The code above will give you all updated records

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PGO01]

Thankyou both for your answers.

I am tempted to use separate triggers, but more triggers means more maintenance - and I'd rather maintain 4 triggers than 12.

The tables in question might have several thousand inserts/updates at once, so doing the joining/selecting might have performance impacts, which I would like to avoid.

Is there really no simple way like a nice little SCOPE_INS_UPD_DEL variable or something?

 

[RiverGuy]

I am tempted to use separate triggers, but more triggers means more maintenance - and I'd rather maintain 4 triggers than 12.

Sometimes, best practices can and should outweigh maintenance requirements.

 

[bborissov]

No, because in one batch (remember triggers are fired AFTER the batch is completed) you may have different type of actions: some records are deleted, other updated and other inserted.

Several thousand INSERT/UPDATE/DELETE? in ONE batch?
What is that system for? I am not saying this is impossible, just curious

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RiverGuy]

They're probably talking about something like UPDATE MyTable SET SomeColumn = SomeValue WHERE DateColumn >= ThreeMonthsAgo (which could results in thousands of updates), which while not the most common regular transaction on a database is a frequent type of query to code into a system for various tasks.

 

[gmmastros]

In my opinion, if the code for insert, update, and delete is significantly different (no common elements), then it makes the most sense to separate in to different triggers. But, if you want to know whether a trigger is happening based on insert, update, or delete, you can play around with this...

In the code I show below, I create a new table, and then create a trigger on that table. This allows you to play with the various execution paths to see what's going on. Once you are satisfied that this is working properly, implement the same logic in your real trigger (and then drop the test table).

Create Table TestTrigger(Id Int)

Create TRIGGER TestTrigger_Trigger
   ON  testTrigger
   For INSERT,DELETE,UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

  Declare @Insert Bit
  Declare @Delete Bit

  If Exists(Select * From Inserted)
    Set @Insert = 1
  Else
    Set @Insert = 0

  If Exists(Select * From Deleted) 
    Set @Delete = 1
  Else
    Set @Delete = 0

  If @Insert = 1 and @Delete = 1
    Begin
      Select 'Update'
    End
  Else If @Insert = 1
    Begin
      Select 'Insert'
    End
  Else If @Delete = 1
    Begin
      Select 'Delete'
    End
  Else
    Begin
      Select 'No Change'
    End

END

Once you have the table and the trigger, play around with some queries to see what happens.

Ex:

[tt][blue]
Insert Into TestTrigger Values(1)
Update TestTrigger Set Id = 7 Where Id = 8
Update TestTrigger Set Id = 7
Delete From TestTrigger Where ID = 20
Delete From TestTrigger
[/blue][/tt]

Run each query separately and see what the output of the trigger shows. You should see all four combinations (Insert, Update, Delete, No Change).

Now... you're probably worried a little bit about performance, right? Well... I can tell you that the Exist checks at the beginning of this code is VERY fast. In fact, all it really needs to do is check to see if there is any data in the inserted or deleted tables. So, even if millions of rows are affected, this code will be very fast. Of course, I make no claims regarding the code you add later.

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Hmm, usually that is some kind of maintenance task for the table and it is expecting to be slow So a few seconds slower, that wouldn't be a problem IMHO.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Found this

http://www.eggheadcafe.com/software/aspnet/31611992/update-insert-delete-trig.aspx

but it doesn't tell anything new.

 

[PGO01]

The database is updated by accounting software, overnight, and at month and year end it updates columns like previous month/year balance which results in thousands of records being updated.

Obviously there are better ways to handle things like this in the source application, but it is quite old and I do not have cdontrol over it.

Thanks for all the replies. I will perform checks on the Inserted and Deleted to determine the action.

Regards,