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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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     lt  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     gt  1  RETURN

User · Answer

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

User · Answer

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

[sql-server] SQL Server - after insert trigger - update another column in the same table

Examples related to sql-server

Examples related to sql-server-2008

Examples related to triggers