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 |
|
KingReza
Starting Member
4 Posts |
 Posted - 2012-03-02 : 12:41:55
|
| Hi all,I'v looked through the forums and I couldn't find anything regarding this questionAssume there is a table, called Table A. Table A has multiple triggers that update Table B and Table C when Table A is updated.When an application sends in a query like "Update Table A". Does the database give the go ahead to the application once Table A is updated or waits till Table A and all the tables affected by the triggers are updated.I'm asking this because we're facing a sporadic behavior in our system where the code follows as such:Update Table A.Send Email with data from Table B and Table C (expecting them to be updated following the update to Table A)Most of the time the E-mails have the updated data, but sometimes (5% of the time) the data is not in the email, even though it is present in the database. So it made wonder if the code is beating the trigger's to the punch, and sending the email before the subsequent tables are updated. Thank you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-02 : 12:43:59
|
| are you doing it in a transaction?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
KingReza
Starting Member
4 Posts |
Posted - 2012-03-02 : 12:46:43
|
| The code is in classic ASP so I'm not sure:Set MM_editCmd = Server.CreateObject("ADODB.Command") MM_editCmd.ActiveConnection = MM_LMSdb_STRING MM_editCmd.CommandText = "UPDATE Table A...." MM_editCmd.Execute MM_editCmd.ActiveConnection.Close |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-03-02 : 13:19:24
|
| All the triggers should adher the ACID properties. Meaning, they should all fire and complete before SQL will signal that it is "done." That having been said, if you have multiple triggers on a table the order they fire is not guaranteed (as far as I know) unless you use the sp_settriggerorder procedure. |
|
|
|
KingReza
Starting Member
4 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-02 : 15:12:08
|
| As far as I know, the triggers - even nested triggers, multiple triggers, recursive triggers - are all ACID. For example, see this page: http://msdn.microsoft.com/en-us/library/ms190739.aspxSince you said that the data in the tables have been correctly updated when you look at it, I don't think either of the following would apply; but might be worth a second look.1. As Lamprey already mentioned, you can set the order the triggers are fired, but if you have not, SQL can choose to fire them in any order. Even if you do set the order, you can set only the first and the last, so if you have more than three triggers, the order of the second and third cannot be predicted.2. If you have any rollback statements in the triggers, the behavior is sort of funny (at least it looks funny to me). Take a look at the remarks section in this page: http://msdn.microsoft.com/en-us/library/ms181299.aspxApart from these caveats, the update to the table and all the triggers that are fired as a result of that update should be ACID. |
|
|
|
KingReza
Starting Member
4 Posts |
Posted - 2012-03-02 : 15:17:13
|
| Thank you all for your informative and detailed response. |
|
|
|
|
|
|
|
|
Triggers and their behavior.