Identify triggers event /action in triggers script ?

  • Hi, all

    I just built a trigger for my table, both for insert and update. The purpose is to log history of the data table to another table.

    The question is, how am I gonna classify the trigger run under insert action or update action.

    The triggers is similar likethis :

    CREATE TRIGGER [sp_trrequest] ON [dbo].[TrRequest]

    FOR INSERT, UPDATE

    AS

    insert into trrequest_log

    select getdate(), * from inserted

    ... and i want to add a column in trrequest_log to identify insert or update action.

    please help. thanks

     

     

     

  • If the trigger runs for insert, the deleted table will be empty. You could do this like :

    CREATE TRIGGER [sp_trrequest] ON [dbo].[TrRequest]

    FOR INSERT, UPDATE

    AS

    BEGIN

     DECLARE @ins int, @del int, @type varchar(30)

     select @ins = count(*) from inserted

     select @del = count(*) from deleted

     if @ins = 0 AND @del = 0 return -- nothing updated/deleted

     if @del = 0 set @type = 'insert' else set @type = 'update'

     if @type = 'update'

     begin

      -- perform action for update

      print 'update'

     end

     else

     begin

      -- perform action for insert

      print 'insert'

     end

    end

     

  • If you just need a value, without other logic, try:

    select getdate(),  CASE WHEN EXISTS(SELECT 1 FROM deleted) THEN 'update' ELSE 'insert' END, * from inserted

     

    The EXISTS clause is faster than counting the rows.

     

     

  • Simple answer & most overlooked;is that you can create a trigger for each event like this:

    CREATE TRIGGER [sp_INS_trrequest] ON [dbo].[TrRequest]

    FOR INSERT

    AS

    BEGIN

    CREATE TRIGGER [sp_UPD_trrequest] ON [dbo].[TrRequest]

    FOR UPDATE

    AS

    BEGIN

    Faster, easier to debug, & specific to event!


    Regards,

    Coach James

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

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