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 |
|
tal_olier
Starting Member
13 Posts |
 Posted - 2002-08-27 : 09:33:42
|
| Hello,I would like to create a trigger (instead of insert) on MS-SQL Server table in a way that if something happens (say x=1) I would like to rollback the insert.In the trigger code I have ...IF (x=1)BEGIN RAISERROR ('error, please contact support', 2/*2=EXUSER*/, 1/*default state*/) ROLLBACK TRANSACTION <---------END...My question is:I can use ROLLBACK or ROLLBACK TRANSACTIONIn the docs I have read that the ROLLBACK rollbacks to the far most old begin transaction but when I test it it rollbacks the same using ROLLBACK TRANSACTION.I have tested it using:begin transaction t1 update tx set c1 = c1 + 1 INSERT INTO test_table values(.....)commit transaction t1And both the update and the insert are rolled back on both cases.- Any idea why or what am I missing ?Regards, Tal Olier (otal@mercury.co.il) |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-27 : 09:51:28
|
| It rolls back to the outermost transaction.Triggers always effectively run inside a transaction.rollback, rollback tran and rollback transaction are the same thing.If you want to rollback part of a transaction (not recommended without a lot of thought) then decare a save point and rollback to thatNote that a rollback does not terminate processing.==========================================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. |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-08-27 : 14:02:41
|
| If I'm reading correctly, it sounds like you always want the update to happen, and it's the insert that you would want to roll back. So why make the update part of the transaction? Do the update, and let the trigger rollback the insert if there condition is met.Am I missing something?Cheers-b |
|
|
|
tal_olier
Starting Member
13 Posts |
Posted - 2002-08-27 : 14:32:31
|
quote:
If I'm reading correctly, it sounds like you always want the update to happen, and it's the insert that you would want to roll back. So why make the update part of the transaction? Do the update, and let the trigger rollback the insert if there condition is met.Am I missing something?Cheers-byes you are missing, see the e-mail of the one posted before you (the update was just a test
|
|
|
|
tal_olier
Starting Member
13 Posts |
Posted - 2002-08-27 : 14:33:32
|
quote:
It rolls back to the outermost transaction.Triggers always effectively run inside a transaction.rollback, rollback tran and rollback transaction are the same thing.If you want to rollback part of a transaction (not recommended without a lot of thought) then decare a save point and rollback to thatNote that a rollback does not terminate processing.==========================================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.
could not get more exact answer, 10x |
|
|
|
BigRetina
Posting Yak Master
144 Posts |
Posted - 2002-08-28 : 03:51:43
|
| I think I read in the BOL that ROLLBACK TRANSACTION rolls back an EXPLICT transaction where ROLLBACK rolls back an implicit transaction. A trigger can be classified as an implicit transaction. |
|
|
|
|
|
|
|
|
Rollback in a trigger