Reputation: 6525
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
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
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