2 Questions About Triggers
-
1) When any type of trigger is used in a transaction with multiple rows, does the trigger handle the rows one after the other, or is a cursor required to be used to ensure processing of each row? Most tables in our model have primary keys, but some do not. If different scenarios of multi-row transactions are handled in triggers in different ways, please describe the differences so we understand how the trigger is behaving. I cannot locate any documents that are clear on this topic.
2) Supposing a cursor must be used. If so, does that make the two Update functions that are available in the trigger irrelevant?
Arthur Lorenzini
Arthur Lorenzini -
The trigger is only fired once for each update or insert statement so, yes, it must handle the processing of multiple rows. The phrase "different scenarios of multi-row transactions are handled... in different ways" concerns me. A trigger should be consistent and work the same for a single row or for multiple. There may be branches of logic based on the data values - but not on the number of rows affected (I am sure there are some exceptions to this - but be careful)
Given that I do not understand the second question, this statement may not be an answer
. If you cursor through the "inserted" table available in the cursor, update calls can be made for each row. If the update affects the same table, be sure to surround it with an IF statement that will not be fired because of the UPDATE called from the trigger or you will have an infinite loop.Guarddata-
-
First explanation is correct and the 2nd question does not make sense.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Regarding the second question. What I mean is, if there are different types of multi-row transations, how are they handled differently? Different types imply a table with a key vs. a table without a key. Or an auto-incremented key vs. a non-auto-incremented key. Or any other ‘differences’ which I am unaware.
Arthur Lorenzini
Arthur Lorenzini -
no, all updates are handled the same to a trigger.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
I coded a few triggers. Here's my 2 bits.
In the trigger you have access to the "inserted" and "deleted" tables. They will let you do set based queries using all the inserted, updated, or deleted records.
For update triggers, having a key that is not updated (or an identity field that's not updated) will let you compare the before and after of each record. Otherwise, it can be difficult to know which "inserted" record corresponds to which "deleted" record. I think the order in the inserted and after table is not reliable (might depend on a clustered index).
If you must base your code on having only one record updated at a time, be sure to check @@ROWCOUNT at the very start before SET NOCOUNT ON. I would save it to a local. Just return if the rowcount is zero. Use a raiserror and rollback if it is greater than 1 unless you code for that case.
Also, the UPDATE() function will tell you if the column appears in the update query, not if it is updated to a different value.
Without some means of identifying the before and after of each row, the update trigger query better be simple or very well thought out.
Don't use a cursor in a trigger - bad for performance.
The code for INSERT, UPDATE, and DELETE triggers can sometimes be combined into one trigger using appropriate LEFT (insert or delete) and INNER JOINS (update) between the deleted and inserted tables.
Edited by - rstone on 07/25/2003 1:06:39 PM
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉 -
Thanks. Basically, what I hear is…. Don’t use cursors and find another way with set-based SQL.
So… many of my triggers have cursors. Now I know I must rewrite them to remove the cursors. Some will be easy, and some are outright complex! I will approach the simple ones as soon as possible and alter them. The tough ones….. will require thought. Thank all of you for your input.
Arthur Lorenzini
Arthur Lorenzini
. If you cursor through the "inserted" table available in the cursor, update calls can be made for each row. If the update affects the same table, be sure to surround it with an IF statement that will not be fired because of the UPDATE called from the trigger or you will have an infinite loop.Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply