I have a trigger on my SQL Server table which takes user updates and log them, in case we need to revert, but it has a problem

The problem is, sometimes in a day that no one is changing anything, a random user just enter the page and the trigger saves a change. The problem is, not only it logs a change that never has occurred in that day/moment(Because he/she didn’t made a change), but it also gets a random data from INSERTED/DELETED, like we have a log of a change on may 5 2019 that has the date of change set in 2014, which is a long time ago.

My trigger is similar to this one below, just without personal information. We simulated this problem by making changes on a day, then trigger logs it correctly, after that we change the date on our computer, log in and wait a little bit, than it logs something random. Sometimes it takes a lot of time, and enter/exiting pages, but eventually something completely random appears from another date from long ago. Thanks for the help!

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tablelog]
ON [dbo].[tablechanged]
AFTER UPDATE
AS
BEGIN 
    declare @OLD_DATA nvarchar(2000);
    declare @NEW_DATA nvarchar(2000);
    declare @Counter    INT;
    declare @Occurrences    INT;
    declare @col varchar(1000);
    declare @SELECDELET nvarchar(2000);
    declare @SELECINSER nvarchar(2000);
    declare @user varchar(50);
    declare @cod int;
    declare @emp INT;
    declare @isReg bit;

    set @Occurrences = (SELECT COUNT(COLUMN_NAME) FROM information_schema.columns WHERE table_name = 'tablechanged')
    set @Counter = 0;
    set @user = (SELECT TOP 1 usuarioUltimaAlteracao FROM INSERTED);
    set @emp = (SELECT TOP 1 empCodigo FROM INSERTED);
    set @cod = (SELECT TOP 1 cedCodigo FROM INSERTED);  
    set @isReg = (SELECT TOP 1 alteracaoViaCadastro FROM INSERTED); 

    SELECT * INTO #Del FROM DELETED
    SELECT * INTO #Ins FROM INSERTED

    if(@isReg = 1)
    begin
        while @Counter < @Occurrences 
        begin
            set @Counter = @Counter + 1;        
            set @col = (select COLUMN_NAME FROM information_schema.columns WHERE table_name = 'tablechanged' and ordinal_position = @Counter);
            select @SELECDELET = 'SELECT @OLD_DATA='+@col+' FROM #Del' ;
            select @SELECINSER = 'SELECT @NEW_DATA='+@col+' FROM #Ins' ;
            exec sp_executesql @SELECDELET, N'@OLD_DATA nvarchar(40) OUTPUT', @OLD_DATA OUTPUT
            exec sp_executesql @SELECINSER, N'@NEW_DATA nvarchar(40) OUTPUT', @NEW_DATA OUTPUT  

            if(@OLD_DATA <> @NEW_DATA)
            begin
                INSERT INTO TABLELOG (OPE_DATE,OPE_USER,OPE_TABLE,OPE_COD,OPE_EMP,OPE_FIELD,OPE,OLD_DATA,NEW_DATA)
                VALUES (getdate(), @user, 'tablechanged', @cod, @emp, @col, 'UPDATE', @OLD_DATA,@NEW_DATA)
            end
        end
    end
END

Answer

SQL Server triggers fire for every statement. Not for every row. Your trigger is obviously broken for the case of a multi-row update.

In the case of a multi-row update, the value of @NEW_DATA after running

SELECT @NEW_DATA='+@col+' FROM #Ins' ;

will be the last value in #Ins, and without an ORDER BY, it’s undocumented which row it come from.

Advertisement

Answer