TRIGGERS AFTER UPDATE,DELETE

  • I have a need for triggers during UPDATE and DELETE. The need in each case is "almost" identical, but there is a slight difference.

    Do I have to write two triggers ? or can I introduce some logic in the trigger definition ?

    example

    Create Trigger dbo.MyCombinedTrigger on dbo.MyTable

    ...

    AFTER UPDATE, DELETE

    ...

    Begin

    /*

    Common code for "Update" and "Delete" operations

    */

    if @@mysteryVariable = DELETE

    begin

    /* "Delete" specific code */

    end

    End

    Is there such a "@@mysteryVariable" or another technique to do this ?

    John

  • John,

    The easiest way to find out if this is UPDATE or DELETE event is to check rows in inserted table: for UPDATE event there will be rows in both tables - inserted and deleted, for DELETE event you will find rows only in deleted table.

    Is this what you are looking for?

    JBZ

  • Yes, thank you very much 🙂

    Just in case anyone else is wondering. Here is my test code:

    create table JDBT1 ( a int, b int);

    create table JDBT2 ( i int, d int);

    go

    create trigger tr1 on JDBT1 after insert, update, delete

    as

    begin

    declare @qI int,@qU int,@qD int;

    select @qI = count(*) from inserted;

    select @qD = count(*) from deleted;

    insert into JDBT2 ( i,d) values (@qI, @qD)

    end

    go

    insert into JDBT1 (a,b) values( 1,2);

    update JDBT1 set b=3;

    delete from JDBT1;

    select * from JDBT2;

    which gives a nice little result set of

    1, 0

    1, 1

    0, 1

    John

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply