CODEWITHSUNDEEP
sqlsql-servertriggers
qplo
qplo

Reputation: 21

Insert and update trigger

I have a table with columns uploaddate and systemuser and a trigger to add user and date for new record

USE [ITPrinter]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trg01]
ON [dbo].[Printers]
FOR INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE Printers
    SET UploadDate = GETDATE(), SystemUser = SUSER_SNAME()
    WHERE id IN (SELECT ID FROM inserted)
END

It works fine.

Now I create 2 new columns, changedate and changeuser in the same table. My goal to update record which alter by user and date alter. BUT NOT CHANGE info in columns UploadDate and SystemUser.

This is my second trigger

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trg02]
ON [dbo].[Printers]
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE p
    SET p.ChangeDate = GETDATE(), p.ChangeUser = SUSER_SNAME()
    FROM Printers p
    INNER JOIN inserted i ON p.id = i.id

But this did not work, I get this error

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

Upvotes: 2

Views: 141

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

Based on The code and the error message we are dealing with Microsoft SQL Server.

looks like your database is configured for recursive triggers.

To disable directly recursive triggers (meaning, an update trigger fired by an update made in on the table by the same update trigger), you can disable recursive triggers by using alter database:

ALTER DATABASE MyDataBase SET RECURSIVE_TRIGGERS OFF;
GO

You can also set trigger nesting level to 0 to disable both direct and indirect trigger recursion, but I would recommend using this step with caution - especially if you use triggers to enforce business rules.

EXEC sp_configure 'nested triggers', 0 ;  
GO  
RECONFIGURE;  
GO

Upvotes: 3

Related Questions