Your description of UPDATE() and your use of it in this scenario shows that you were NOT aware of what UPDATE() is designed to do. The Trigger developer also needs to know what UPDATE() is DESIGNED to do. The Trigger developer needs to know what SQL Code exists, and how their code will interact with it.ģ. The Application SQL developer needs to know what triggers currently exist, and how their code will interact with them.Ģ. This goes for both application SQL and trigger SQL.Īwareness is necessary for any developer:ġ. ReplyĪny code needs to be tested in its environment. This is still a good message because you need to be AWARE of the code in use when writing triggers, and of the triggers in use when writing the code, but it’s not a bug in UPDATE(). The trigger with UPDATE() is then useful because other code which doesn’t alter Location won’t run the bulk of the trigger’s code. The better code filters the ROWS to be updated so that it’s not touching Location in rows where it doesn’t need to. It tells you when a column has been the target of an UPDATE, EVEN IF THAT UPDATE IS TO THE SAME VALUE. The misconception here is that UPDATE() is supposed to tell you when a column value has been altered. WHERE DisplayName = ‘Brent’ AND Location IS NULL You wrote the same code twice, and it’s that code that does not work with a trigger which uses UPDATE()īetter code to use with a trigger that uses UPDATE() is: COALESCE(Location,’Miami’) = CASE WHEN Location IS NULL THEN ” ELSE Location END.
0 Comments
Leave a Reply. |