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

  • 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.

  • 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

  • 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.

  • 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.

  • 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

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

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