Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sg
Starting Member
2 Posts |
 Posted - 2008-07-16 : 08:34:56
|
| So say I have trigger_1 on table_1 and the pseudocode for trigger_1 is:begin trigger_1 UPDATE table_2... Procedure_that_takes_a_long_time UPDATE table_2...end trigger_1My question is: After I update table_2 for the first time will the updates to table_2 commit at that time (before the long procedure runs)? Or will everything commit together at the end of trigger_1?If everything commits at the end, whats the best way to make it commit after the first update? My understanding is that you cant commit within a trigger so would I make another procedure to do the updates or something?Your advice is appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-16 : 09:36:23
|
quote:
Originally posted by sg
So say I have trigger_1 on table_1 and the pseudocode for trigger_1 is:begin trigger_1 UPDATE table_2... Procedure_that_takes_a_long_time UPDATE table_2...end trigger_1My question is: After I update table_2 for the first time will the updates to table_2 commit at that time (before the long procedure runs)? Or will everything commit together at the end of trigger_1?If everything commits at the end, whats the best way to make it commit after the first update? My understanding is that you cant commit within a trigger so would I make another procedure to do the updates or something?Your advice is appreciated.
the initial update to table2 commits before procedure starts executing unless you wrap them all in a transaction. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-07-16 : 12:41:22
|
| In short, my advice is don't. Triggers should (if at all possible) be short and simple (meaning fast). When I see triggers doing this sort of thing it tends to show a flaw in the system design. About the only ting I use triggers for are Auditing and making sure that there are no temporal gaps or overlaps in temporal tables (warehouse). And then the only reason I do that is because SQL doesn't have a time span or durration datatype (which they left out of 2008 again but added a three point coordinate? grr don't get me started ;) ).The point being, doing complex operations in triggers is probably a bad idea. But, if you must, then Visakh is correct about the transaction. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-07-16 : 13:22:13
|
| I disagree about the when the transaction is committed. Assuming no explicit transaction control within the trigger...The DML within the trigger is part of the implicit user transaction. Those statements (within the trigger) won't be committed until the user tranaction on the table with the trigger is committed. That necessarily means tables in any of the statements will fall under the same locking/blocking scenario of any transaction.However, I strongly agree with all the other opinions stated by Lamprey.Be One with the OptimizerTG |
|
|
|
sg
Starting Member
2 Posts |
Posted - 2008-07-17 : 05:20:28
|
| Well, I thnk you all for your advice. I believe in my particular circumstance, running the long procedure from the trigger is ok. Inserts to that table are performed asyncronously from my application.I am still confused about my original question however, specifically if the first update will commit prior to the completion of the trigger. TG's explanation sounds plausable.What if I called a procedure to perform the update? Would that commit prior to the completion of the trigger for certain? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-07-17 : 09:51:48
|
quote:
What if I called a procedure to perform the update? Would that commit prior to the completion of the trigger for certain?
No. The only way to do that would be to issue a COMMIT within the trigger. Books Online recommends against that. From Books Online under COMMIT:quote:
In SQL Server 2000 and later, if a COMMIT TRANSACTION or COMMIT WORK statement is executed in a trigger and there is no corresponding explicit or implicit BEGIN TRANSACTION statement at the start of the trigger, users may see different behavior than in SQL Server version 7.0. Placing COMMIT TRANSACTION or COMMIT WORK statements in a trigger is not recommended.
If you want to post your big picture business objective we may be able to offer some alternatives.EDIT:one way to isolate a data dependent process from a user transaction is to use the trigger to populate a simple process queue table. A job (or service) can monitor the the queue and perform actions outside the context of the user transaction on any schedule.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Triggers - Your advice on the best approach