When updating one row in a table, update trigger creates 3 rows in audit table

Question

When updating one row in a table, update trigger creates 3 rows in audit table

webskater

SSCarpal Tunnel

Points: 4214
More actions
January 15, 2018 at 3:38 pm

#325403

I have a table with one trigger on it.
CREATE TRIGGER [dbo].[trgPersonnel] ON [dbo].[Personnel]
FOR UPDATE
AS
SET NOCOUNT ON SET XACT_ABORT ON SET ARITHABORT ON
IF TRIGGER_NESTLEVEL() > 1 RETURN
DECLARE @User varchar(100) DECLARE @AuditDate smalldatetime
SET @User = suser_sname() SET @AuditDate = getdate()
INSERT Personnel_Audit SELECT AuditType = 'U', AuditDate = @AuditDate EditedBy = @User, PersonellID, Forename, Surname FROM Deleted
Every time one row in the dbo.Personnel table is updated (and PersonnelID is the Primary Key and it is being done in a procedure with WHERE PersonnelID = @PersonnelID) - 3 identical rows appear in table Personnel_Audit.
I really just want one row to record each update, not 3. Any help much appreciated.

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

1) Don't declare variables and put stuff into them and then use in SELECT like that. Just put the system function calls in the SELECT.

2) Are you 100% positive that there are no other triggers (on this or any other table), or any other code, that could be putting rows into the audit table?

3) Are you 100% positive that only ONE update is being done to the row that appears three times in the audit table?

4) I cannot see any possible way for their to be duplicate rows other than 2 and 3 above.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 2

TheSQLGuru - Monday, January 15, 2018 4:11 PM
1) Don't declare variables and put stuff into them and then use in SELECT like that. Just put the system function calls in the SELECT.
2) Are you 100% positive that there are no other triggers (on this or any other table), or any other code, that could be putting rows into the audit table?
3) Are you 100% positive that only ONE update is being done to the row that appears three times in the audit table?
4) I cannot see any possible way for their to be duplicate rows other than 2 and 3 above.

Thank you for you reply. As a matter of curiosity - what is wrong with 1)?

It was, I am afraid, no 3). The front end developer swore he was only calling a procedure once but it turns out he calls 3 stored procedures, one after the other, which all update the table. The time difference is nothing for the first two records and a couple of milliseconds for the third. I have to say I didn't write this - I've just stuck an audit table on the table and some triggers to try to find out what is going on. There is no error catching in the stored procedures - just blind faith in the front end that all 3 execute correctly one after the other.

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 3

webskater - Tuesday, January 16, 2018 6:54 AM
The time difference is nothing for the first two records and a couple of milliseconds for the third...

I guess it is 3ms. You can use datetime2 type for a higher precision.
Check how many times the procedure executes using SQL Server Profiler or Extended Events.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

webskater - Tuesday, January 16, 2018 6:54 AM
TheSQLGuru - Monday, January 15, 2018 4:11 PM
1) Don't declare variables and put stuff into them and then use in SELECT like that. Just put the system function calls in the SELECT.
2) Are you 100% positive that there are no other triggers (on this or any other table), or any other code, that could be putting rows into the audit table?
3) Are you 100% positive that only ONE update is being done to the row that appears three times in the audit table?
4) I cannot see any possible way for their to be duplicate rows other than 2 and 3 above.
Thank you for you reply. As a matter of curiosity - what is wrong with 1)?
It was, I am afraid, no 3). The front end developer swore he was only calling a procedure once but it turns out he calls 3 stored procedures, one after the other, which all update the table. The time difference is nothing for the first two records and a couple of milliseconds for the third. I have to say I didn't write this - I've just stuck an audit table on the table and some triggers to try to find out what is going on. There is no error catching in the stored procedures - just blind faith in the front end that all 3 execute correctly one after the other.

#1 is simply inefficient and thus and poor coding. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service