July 25, 2013 at 8:52 am
Thanks, Eugene, I see. I had commented out the ROLLBACK for my INSTEAD OF trigger so didn't understand the behavior you just posted with the test code. Perhaps Scott was talking about FOR DELETE triggers and didn't realize I would assume he meant all triggers.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 25, 2013 at 8:53 am
ScottPletcher (7/25/2013)
Eugene Elutin (7/25/2013)
Alexander Suprun (7/24/2013)
Eugene Elutin (7/24/2013)
RAISERROR cause transaction to rollback, so your log insert is rollbacked as well as delete...It's so untrue...
RAISERROR has nothing to do with the transaction.
It's just the way how SSMS works. It creates transaction and then rollbacks it if there are any errors.
And if you delete multiple rows then SSMS deletes them one by one, and as soon as it get's the first error it stop processing.
What do you mean by "RAISERROR has nothing to do with the transaction."
Read the original OP post. There is nothing there about SSMS.
RAISERROR in the trigger has everything to do with the transaction as it causes its rollback!
If the trigger wouldn't have one, then OP would see Log records created, no delete happened but also, no error returned to client.
And it doesn't matter what you are using to delete records.
Even if you would do it from any client using DELETE query and explicitly open a transaction, RAISERROR in this trigger would ROLLBACK any modifications happened before rollback.
SSMS (in Edit Top N Rows mode) performs all row modifications in a separate transactions eg. deletes.
Actually, if delete from this table is not permitted, I would explicitly rollback in the trigger, to ensure that the whole transaction is rollbacked.
Now, the most interesting thing for me here is the effect of using INSTEAD OF trigger.
If the OP would create ordinary trigger for delete and would raise an error and rollback before inserting into log, he would not see records in the log anyway, as rollback mark the whole transaction uncommittable. But, looks like in the INSTEAD OF trigger, whatever happens after rollback is committed.
This is just false:
"RAISERROR causes ... rollback ... RAISERROR in this trigger would ROLLBACK ..."
A RAISERROR (with a level below 20) does not cause or force a ROLLBACK.
Ok, I did make inaccurate statement.
What I did mean, was: if there is an explcitly openned transaction and an error validation (@@ERROR!=0) or trapping (TRY...CATCH) for rollback or commit, then RAISERROR whould cause the rollback of such transaction.
Actually, it's a good point. If you run DELETE query from SSMS without an explicit transaction, than such error (with low severity) will be returned but DELETE committed. However in the "Table Edit" mode, SSMS opens explicit transaction and rolls it back if any trappable error (severity higher than 10) does happen.
July 25, 2013 at 9:07 am
webrunner (7/25/2013)
Thanks, Eugene, I see. I had commented out the ROLLBACK for my INSTEAD OF trigger so didn't understand the behavior you just posted with the test code. Perhaps Scott was talking about FOR DELETE triggers and didn't realize I would assume he meant all triggers.Thanks again,
webrunner
After looking it more carefully, I've understood what does really happen.
Actually ROLLBACK in a trigger couses all modification happened before rollback statemnt to be rolledback, but it doesn't stop trigger to exit, so all statements after the ROLLBACK do execute and are committable.
Now, the real difference between INSTEAD OF and ordinary trigger is in a way SQL server operates with "deleted" entity. I found it by adding SELECT COUNT(*) FROM deleted straight after ROLLBACK.
In case of an ordinary FOR DELETE trigger, rollback removes records from "deleted", so no records inserted into log. However in INSTEAD OF trigger, records are still in "deleted" after ROLLBACK issued.
May be SQL desides not to bother with removing them as they wouldn't be deleted from base table anyway until delete is specificly coded in a trigger?
For me, it's very interesting feature of INSTEAD OF trigger.
July 25, 2013 at 9:40 am
Eugene Elutin (7/25/2013)
webrunner (7/25/2013)
Thanks, Eugene, I see. I had commented out the ROLLBACK for my INSTEAD OF trigger so didn't understand the behavior you just posted with the test code. Perhaps Scott was talking about FOR DELETE triggers and didn't realize I would assume he meant all triggers.Thanks again,
webrunner
After looking it more carefully, I've understood what does really happen.
Actually ROLLBACK in a trigger couses all modification happened before rollback statemnt to be rolledback, but it doesn't stop trigger to exit, so all statements after the ROLLBACK do execute and are committable.
Now, the real difference between INSTEAD OF and ordinary trigger is in a way SQL server operates with "deleted" entity. I found it by adding SELECT COUNT(*) FROM deleted straight after ROLLBACK.
In case of an ordinary FOR DELETE trigger, rollback removes records from "deleted", so no records inserted into log. However in INSTEAD OF trigger, records are still in "deleted" after ROLLBACK issued.
May be SQL desides not to bother with removing them as they wouldn't be deleted from base table anyway until delete is specificly coded in a trigger?
For me, it's very interesting feature of INSTEAD OF trigger.
I did mean all triggers.
RAISERROR alone (with less than a "fatal" severity level) does not cause or force a ROLLBACK.
But, yes, a ROLLBACK statement does cause a ROLLBACK :-).
What SQL is doing with regards to an INSTEAD OF (IO) trigger is quite logical.
An IO trigger prevents the actual statement from running, meaning that your code is responsible for any data modifications. Given that, it only makes sense to leave all data in the inserted/deleted tables even if a ROLLBACK occurs, so that your code can make use of it as required. In an AFTER trigger, SQL is responsible for the modifications, and since after a ROLLBACK occurs, SQL no longer needs the data in those tables, SQL releases it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 25, 2013 at 10:06 am
Thanks, Scott,
By "prevents the actual statement from running" do you mean the original DELETE statement? And then any code in the trigger after the ROLLBACK does get run? That would explain why my changes to the code sequence in my trigger worked.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 25, 2013 at 10:58 am
webrunner (7/25/2013)
Thanks, Scott,By "prevents the actual statement from running" do you mean the original DELETE statement? And then any code in the trigger after the ROLLBACK does get run? That would explain why my changes to the code sequence in my trigger worked.
webrunner
Yes, the original DELETE. The trigger runs instead of the original DELETE statement (hence it's designation as an "INSTEAD OF" trigger :-)).
So you don't need a ROLLBACK within an INSTEAD OF DELETE trigger to "undo" the delete(s) -- it(they) were never done anyway! But, per normal SQL Server rules, the ROLLBACK will rollback all of any active transaction, as you acknowledged earlier.
A specific example ::= given this code:
BEGIN TRAN
INSERT INTO dbo.table1 VALUES(...)
INSERT INTO dbo.table2 VALUES(...)
DELETE FROM dbo.table3 WHERE id = ...
COMMIT
And an INSTEAD OF DELETE trigger on table3: if you do a ROLLBACK in that trigger, you've undone the INSERTs on table1 and table2! If you use RAISERROR and exit the trigger, the DELETE does not occur but the INSERTs will.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 25, 2013 at 2:06 pm
Thanks once more, Scott.
I did realize the INSTEAD OF trigger would cancel the original delete that was issued 🙂 but didn't realize the care needed in using ROLLBACK or in trying to log data as part of the trigger.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply