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