Skip to content
Advertisement

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!

Advertisement

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

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement