CODEWITHSUNDEEP
sql-servertriggers
Daniel L. VanDenBosch
Daniel L. VanDenBosch

Reputation: 2725

SQL Server trigger firing 3 times

I have set up the following audit trigger to create copies of the record changes into my change log (should we ever need to see what happened to our data). The problem is every time I update the record, it creates 3 records in my log table.

Below you can see the result set and my code I have been using.

result set

Code:

/*  ==Scripting Parameters==
    Source Server Version : SQL Server 2016 (13.0.4446)
    Source Database Engine Edition : Microsoft SQL Server Standard Edition
    Source Database Engine Type : Standalone SQL Server

    Target Server Version : SQL Server 2017
    Target Database Engine Edition : Microsoft SQL Server Standard Edition
    Target Database Engine Type : Standalone SQL Server
*/

USE [STONE_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tr_set_for_update_audit_fields]
ON [dbo].[permission_types]
FOR UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO STONE_DB_CHANGE_LOGS.dbo.permission_types_log ([permission_type_id], [business_process], [active], [created_at], [created_by], [change_type], [change_user], [change_at])
        SELECT DISTINCT 
            inserted.id, 
            inserted.[business_process], inserted.[active],
            inserted.[created_at], inserted.[created_by],
            'U', SYSTEM_USER, GETDATE()
        FROM 
            inserted
        WHERE 
            1 = 1
            AND inserted.[created_at] < getdate();

        UPDATE [dbo].[permission_types]
        SET updated_at = GETDATE(), updated_by = SYSTEM_USER
        FROM [dbo].[permission_types] 
        INNER JOIN inserted ON permission_types.id = inserted.id
        WHERE 1 = 1
          --AND permission_types.updated_at < getdate() 
        ;

        PRINT 'Record Updated'
END

Upvotes: 0

Views: 3199

Answers (3)

Chithra
Chithra

Reputation: 1

USE the following code to avoid nesting

IF((SELECT trigger_nestlevel() ) > 1) RETURN

Upvotes: 0

Maur&#237;cio Pontalti Neri
Maur&#237;cio Pontalti Neri

Reputation: 1080

You need to prevent a database trigger from recursing

Check this solution

How do I prevent a database trigger from recursing?

Upvotes: 2

Ruslan K.
Ruslan K.

Reputation: 1981

You should add at the begin of trigger code like this:

IF NOT UPDATE(business_process)
    RETURN;

More info: UPDATE()

Upvotes: 1

Related Questions