We help IT Professionals succeed at work.

Writing Insert, Delete, Update trigger

purplesoup
purplesoup asked
on
It is possible to write an insert, delete and update trigger, but how can you tell if it is a delete? For insert and update you can use the IF UPDATE(..) - also I see the notes say don't use IF UPDATE for delete.

So my question is, in practise is it best to separate the insert and update triggers from the delete triggers, and if not, how can I tell if I am running a delete statement from my trigger?
Comment
Watch Question

I would advise against triggers. Use triggers when absolutely necessary. They can cause lots of problems, and impact on performance.

Just have your code do the inserts, updates, and deletes. This way you never have to be concerned with what is being triggered and when. Having the code do it, makes your program easier to maintain and debug. You also will not suffer from the performance issue.

You will also know whether it is an insert or update or delete that is occurring.
there are a few ways to do this.  First, you can always create three separate triggers the way you describe.

Second, you can query against the specialty tables.  Here's an example:

declare @trigger varchar(20)
begin try
    if exists (select top 1 * from inserted)
             set @trigger='inserted'
end try
begin catch
    begin try
         if exists (select top 1 * from updated)
             set @trigger='updated'
    end try
    begin catch
             if exists (select top 1 * from deleted)
                  set @trigger='deleted'
    end catch
end catch

Open in new window


Personally, I'd create separate triggers to keep the logic readable
btw, I agree with ajones.  Avoid triggers unless you have an outside process modifying your tables that you have no control over.
Top Expert 2012
Commented:
As you have pointed out, there are only the INSERTED and DELETED logical tables, so try it this way:
IF EXISTS(SELECT 1 FROM INSERTED)
    IF EXISTS(SELECT 1 FROM DELETED)
      PRINT 'Update'
    ELSE
      PRINT 'Insert'
ELSE
    PRINT 'DELETE'
Top Expert 2012

Commented:
jmcvinney,
You may want to double check this:
"if exists (select top 1 * from updated)"
yeah, I typed it in a hurry, company was at the door....sorry
purplesoupProgrammer

Author

Commented:
I had a look at just going with the exists checks without the try catch blocks and it is fine - you can flag what type of update you have just by looking at the different tables available.

Yeh I know the problems with triggers, but in this case it is a necessary evil, we don't want to have to get into some ancient desktop application code, and it is an area of functionality that doesn't need high performance.