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 |
|
majkinetor
Yak Posting Veteran
55 Posts |
 Posted - 2006-03-28 : 03:53:45
|
| Scenerio:0. Start transaction1. insert into table A (sucess) |-- triger fires additional insert in B (sucess)2. insert into table A (failed)3. Rollback transaction (failed)Does anyone knows is this phenomena common, or that might be some error in my database which I am not able to detect correctly.I also heard on several places that trigers can put database in undefined state, when either commit or rollback calls will fail.Thx.www.r-moth.com |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-28 : 05:42:08
|
| A failure in a trigger (e.g. index violation) will abort the transaction (in fact the batch). There is nothing you can do about this and it caonnot be trapped in sql. If someone has put a rollback in the trigger then this will roll back the transaction.It should not cause a problem as it will terminate processing and the transaction will have rolled back.If you execute this as a batch then it will be ok because the rollback in the trigger will terminate processing.If you execute statement by statement in query analyser then the second rollback will fail as the transaction has already been terminated by the trigger.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2006-03-28 : 05:58:39
|
| thx nr for your answer.But what will happen if rollback has not be defined in a trigger ? Afert your words, I count this as bad design isue, but nevertheless, it is good to know symptoms of such scenario.www.r-moth.com |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-28 : 08:01:01
|
| I suspect 2 has caused the transaction to rollback.As there is nothing to stop processing the rollback is also executed and fails as outside a transaction.Triggers don't leave a database in an undefined state. Both a commit and rollback will fail if executed outside a transaction.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Trigers and Transaction