CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008t-sqltriggers
Jim Evans
Jim Evans

Reputation: 6525

SQL Server 2008 Update Trigger

I need to create an update trigger on a table that it also updates the table if certain inserted values are present. Is this possible? I have tried and when the inserted value is present and fires the trigger the trigger does an update and fires itself again. Eventually - deadlock. Is there a way to accomplish this safely?

The Code:

CREATE TRIGGER dbo.trg_updateaddress on dbo.Customers
    AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @original_source varchar(50)
    SELECT  @original_source = address_source FROM deleted

    IF EXISTS (SELECT * FROM inserted i
               INNER JOIN deleted d
                    ON i.cust_id = d.cust_id
               WHERE i.address_source IS NOT NULL 
               AND   (i.[address] <> d.[address]
               OR    i.address2 <> d.address2
               OR    i.city <> d.city
               OR    i.[state] <> d.[state]
               OR    i.zip <> d.zip
               OR    i.country <> d.country
               OR    i.phone <> d.phone
               OR    i.cell <> d.cell
               OR    i.email <> d.email))
    BEGIN
         UPDATE customers
         SET    address_changed = GETDATE()
    END
    ELSE
    BEGIN
        UPDATE customers
        SET    address_source = @original_source
    END
END

Upvotes: 1

Views: 180

Answers (2)

Fred Phillips
Fred Phillips

Reputation: 23

You can prevent the trigger from firing on itself with NESTED_LEVEL

Put this after SET NOCOUNT ON;

IF trigger_nestlevel() < 2

This will prevent the trigger from firing on it's own update.

Note: trigger_nestlevel starts counting at 1 for the first update, so if you want the trigger to only fire once then set it to 2, if you want it to fire twice then set this to 3.

Upvotes: 1

RBarryYoung
RBarryYoung

Reputation: 56785

One thing that you can do is to disable trigger recursion in your database:

USE [master]
GO
ALTER DATABASE [yourDbName] SET RECURSIVE_TRIGGERS OFF WITH NO_WAIT
GO

This will prevent all triggers in the database from triggering themselves directly, while still allowing them to fire other triggers.

I normally recommend this setting anyway, as recursive triggers is not normally what you want, and a very specialized behavior.

Upvotes: 1

Related Questions