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

I've got this database trigger:

CREATE TRIGGER setDescToUpper
ON part_numbers
 AFTER INSERT,UPDATE
AS
DECLARE @开发者_C百科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.

---

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).

---

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;

---

Use a computed column instead. It is almost always a better idea to use a computed column than a trigger.

See Example below of a computed column using the UPPER function:

create table #temp (test varchar (10), test2 AS upper(test))
insert #temp (test)
values ('test')
select * from #temp

And not to sound like a broken record or anything, but this is critically important. Never write a trigger that will not work correctly on multiple record inserts/updates/deletes. This is an extremely poor practice as sooner or later one of these will happen and your trigger will cause data integrity problems asw it won't fail precisely it will only run the process on one of the records. This can go a long time until someone discovers the mess and by themn it is often impossible to correctly fix the data.

---

Yes, it will recursively call your trigger unless you turn the recursive triggers setting off:

ALTER DATABASE db_name SET RECURSIVE_TRIGGERS OFF 

MSDN has a good explanation of the behavior at http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx under the Recursive Triggers heading.

---

Yea...having an additional step to update a table in which you can set the value in the inital insert is probably an extra, avoidable process. Do you have access to the original insert statement where you can actually just insert the part_description into the part_description_upper column using UPPER(part_description) value?

After thinking, you probably don't have access as you would have probably done that so should also give some options as well...

1) Depends on the need for this part_description_upper column, if just for "viewing" then can just use the returned part_description value and "ToUpper()" it (depending on programming language).

2) If want to avoid "realtime" processing, can just create a sql job to go through your values once a day during low traffic periods and update that column to the UPPER part_description value for any that are currently not set.

3) go with your trigger (and watch for recursion as others have mentioned)...

HTH

Dave

---

It might be safer to exit the trigger when there is nothing to do. Checking the nested level or altering the database by switching off RECURSIVE can be prone to issues.

Ms sql provides a simple way, in a trigger, to see if specific columns have been updated. Use the UPDATE() method to see if certain columns have been updated such as UPDATE(part_description_upper).

IF UPDATE(part_description_upper)
  return

---

create or replace 
TRIGGER triggername BEFORE INSERT  ON 
table FOR EACH ROW 
BEGIN
/*
Write any select condition if you want to get the data from other tables
*/
:NEW.COLUMNA:= UPPER(COLUMNA); 
--:NEW.COUMNa:= NULL;
END; 

The above trigger will update the column value before inserting. For example if we give the value of COLUMNA as null it will update the column as null for each insert statement.