SQL - Best way to determine Operation Type in Trigger?

Question

Below is the code i'm using to determine if the operation in an insert/update/delete. This question is two part.

One - this is a correct way of determining the operation type. Two - Is this the best way of determining the operation type.

BEGIN
   DECLARE @ActionType   CHAR (1)

   IF NOT EXISTS (SELECT * FROM deleted) AND EXISTS (SELECT * FROM inserted)
      BEGIN
         SET @ActionType = 'I'
      END

   IF EXISTS (SELECT * FROM deleted) AND EXISTS (SELECT * FROM inserted)
      BEGIN
         SET @ActionType = 'U'
      END

   IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
      BEGIN
         SET @ActionType = 'D'
      END

      Select @ActionType;
End

Well, the best solution for me is to have three separate triggers - one for INSERT, one for DELETE, and a third for UPDATE - then it's clear from the beginning what kind of operation you're dealing with .... — marc_s
@marc_s - I agree that would remove ambiguity, but why maintain three triggers if they all perform the same task? — Michael

Michael Michael · Accepted Answer · 2014-04-18T17:16:17

One: yes. Two: you can be even more concise than that.

Here is the code I am using currently.

SELECT @Action = CASE 
            WHEN EXISTS(SELECT 1 FROM INSERTED) 
             AND EXISTS(SELECT 1 FROM DELETED) THEN 'U'
            WHEN EXISTS(SELECT 1 FROM INSERTED) THEN 'I'
            ELSE 'D' END;

SQL - Best way to determine Operation Type in Trigger?

1 Answers