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 · 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

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

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

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

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

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

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

[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