SQL Server - after insert trigger - update another column in the same table

Question

I've got this database trigger:

CREATE TRIGGER setDescToUpper ON part_numbers  AFTER INSERT,UPDATE AS DECLARE @PnumPkid int, @PDesc nvarchar(128)  SET @PnumPkid = (SELECT pnum_pkid FROM inserted) SET @PDesc = (SELECT UPPER(part_description) FROM inserted)  UPDATE part_numbers set part_description_upper = @PDesc WHERE pnum_pkid=@PnumPkid  GO 

Is this a bad idea? That is to update a column on the same table. I want it to fire for both insert and update.

It works, I'm just afraid of a cyclical situation. The update, inside the trigger, fires the trigger, and again and again. Will that happen?

Please, don't nitpick at the upper case thing. Crazy situation.

Answer 1

It depends on the recursion level for triggers currently set on the DB.

If you do this:

SP_CONFIGURE 'nested_triggers',0 GO RECONFIGURE GO 

Or this:

ALTER DATABASE db_name SET RECURSIVE_TRIGGERS OFF 

That trigger above won't be called again, and you would be safe (unless you get into some kind of deadlock; that could be possible but maybe I'm wrong).

Still, I do not think this is a good idea. A better option would be using an INSTEAD OF trigger. That way you would avoid executing the first (manual) update over the DB. Only the one defined inside the trigger would be executed.

An INSTEAD OF INSERT trigger would be like this:

CREATE TRIGGER setDescToUpper ON part_numbers INSTEAD OF INSERT AS BEGIN     INSERT INTO part_numbers (         colA,         colB,         part_description     ) SELECT         colA,         colB,         UPPER(part_description)     ) FROM         INSERTED END GO 

This would automagically "replace" the original INSERT statement by this one, with an explicit UPPER call applied to the part_description field.

An INSTEAD OF UPDATE trigger would be similar (and I don't advise you to create a single trigger, keep them separated).

Also, this addresses @Martin comment: it works for multirow inserts/updates (your example does not).

Answer 2

Another option would be to enclose the update statement in an IF statement and call TRIGGER_NESTLEVEL() to restrict the update being run a second time.

CREATE TRIGGER Table_A_Update ON Table_A AFTER UPDATE  AS IF ((SELECT TRIGGER_NESTLEVEL()) < 2) BEGIN     UPDATE a     SET Date_Column = GETDATE()     FROM Table_A a     JOIN inserted i ON a.ID = i.ID END 

When the trigger initially runs the TRIGGER_NESTLEVEL is set to 1 so the update statement will be executed. That update statement will in turn fire that same trigger except this time the TRIGGER_NESTLEVEL is set to 2 and the update statement will not be executed.

You could also check the TRIGGER_NESTLEVEL first and if its greater than 1 then call RETURN to exit out of the trigger.

IF ((SELECT TRIGGER_NESTLEVEL()) > 1) RETURN;