How do I make one trigger fire but ignore the actions done by another trigger?

52 Views Asked by At

I have 2 triggers I need to make. The problem is that when a user inserts/updates a row, Trigger 1 fires, which updates the 3 columns defined below, which causes Trigger 2 to fire. But Trigger 2 is also firing indepedently because of the user's original action. I can't merge these triggers into one because of other constraints, so how do I make it so that the user's action causes Trigger 2 to only fire on the user's action and NOT because of Trigger 1?

Trigger 1: any time the row is updated, update 3 specific columns called UpdateDate, UpdatedByHostName, and UpdatedByLogin. This will tell us who last changed the other columns in the row

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trigUpdateStandardTableData_ProductionOrder] ON [dbo].[Production Order] AFTER UPDATE AS BEGIN SET NOCOUNT ON 
UPDATE [dbo].[Production Order] SET UpdateDate = GETDATE(), UpdatedByHostName = HOST_NAME(), UpdatedByLogin = SUSER_NAME() 
FROM [dbo].[Production Order] INNER JOIN inserted ii ON [dbo].[Production Order].RowPointer = ii.RowPointer 
END

Trigger 2: when a row is inserted/updated/deleted, copy the affected rows into another table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter TRIGGER [dbo].[trigLogChanges_ProductionOrder] 
   ON  [dbo].[Production Order]
   AFTER INSERT, UPDATE, DELETE
AS 
BEGIN    
    set nocount on
    set xact_abort on

    declare @ChangeBatchID nvarchar(36) = newid()
    declare @ChangeType nvarchar(7) = 'UNKNOWN'
    declare @ChangeDateTime datetime2 = getdate()

    if exists(select top 1 1 from inserted) and exists(select top 1 1 from deleted)
    begin    
        set @ChangeType = 'UPDATE'    
    end

    if exists(select top 1 1 from inserted) and not exists(select top 1 1 from deleted)
    begin    
        set @ChangeType = 'INSERT'    
    end

    if not exists(select top 1 1 from inserted) and exists(select top 1 1 from deleted)
    begin    
        set @ChangeType = 'DELETE'    
    end

    begin try

        begin tran

            if exists(select top 1 1 from deleted) --Either a DELETE or an UPDATE
            begin               
                --Insert into other table...        
            end

            if exists(select top 1 1 from inserted) --Either an INSERT or an UPDATE
            begin               
                --Insert into other table...    
            end 

        commit tran

    end try

    begin catch
        if xact_state() <> 0 rollback tran
        print(error_message())
    end catch

END
0

There are 0 best solutions below