1

I know this is the easiest question you can find on this website but I wasn't able to find its answer on the internet.

How to check if the trigger was fired by an INSERT or DELETE?

Example

CREATE TRIGGER [dbo].[TestTrigger]
On [dbo].[Table1]
AFTER INSERT, DELETE
AS
    --if save
    --do some work

    --else if delete
    --do some work other work

I want to do some work if it's an insert and some other work if it is a delete.

Please provide a code snippet for the same.

9

As long as the trigger is only for insert/delete:

IF EXISTS (SELECT 1 FROM inserted)
   -- I am an insert
ELSE
   -- I am a delete

And in fact if you are doing things based on a join to inserted/deleted, it can be even simpler:

-- do something for an insert
-- this will only actually do anything when it's an insert
INSERT dbo.somewhereElse SELECT whatever, 'insert'
  FROM dbo.sourceTable AS s
  INNER JOIN inserted AS i ON s.id = i.id;

-- do something for a delete
-- this will only actually do anything when it's a delete
INSERT dbo.somewhereElse SELECT whatever, 'delete'
  FROM dbo.sourceTable AS s
  INNER JOIN deleted AS d ON s.id = d.id;

If you add the update action to the trigger, the logic gets more complex, of course:

IF EXISTS (SELECT 1 FROM inserted)
BEGIN
  IF EXISTS (SELECT 1 FROM deleted)
  BEGIN
    -- I am an update
  END
  ELSE
  BEGIN
    -- I am an insert
  END
END
ELSE
BEGIN
  -- I am a delete
END
3

Since my comment went unanswered, I have converted it to an answer.

I want to do some work if it's an insert and some other work if it is a delete.

You could create separate triggers, one for insert, and one for delete:

CREATE TRIGGER dbo.Table1_AI
ON dbo.Table1
AFTER INSERT
AS
... insert-specific actions
CREATE TRIGGER dbo.Table1_AD
ON dbo.Table1
AFTER DELETE
AS
... delete-specific actions

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.