Last post Sep 09, 2016 08:55 PM by AZMatt
Sep 09, 2016 01:00 PM|NewKid1nTown|LINK
I'm new to triggers. I have an insert trigger defined on table1. This trigger writes certain data to table2. When the trigger fails, I am noticing that the original insert on table1 also fails. Is there a way to have the original insert still work even if
the trigger fails?
Sep 09, 2016 02:56 PM|AZMatt|LINK
Triggers are not executed until the original insert finishes (or fails). I am guessing there is a problem with the insert to table1. Could you post your error messages?
Sep 09, 2016 03:26 PM|NewKid1nTown|LINK
This is the error message:
Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.
So, you're saying the insert on the table that has the trigger should work, regardless on the success of failure of the trigger(s) on that table?
Sep 09, 2016 04:05 PM|AZMatt|LINK
I couldn't say unless I see a bit more. Could you post the SQL for your insert and trigger?
Also, are you able to duplicate this error by running it manually from SSMS?
Sep 09, 2016 04:39 PM|NewKid1nTown|LINK
This message is from running a specific insert statement in Management Studio.
Let me work out an example that replicates this issue and I will post it here.
But, what is the expectation with triggers? Should the originating insert's failure or success depend on the trigger's failure or success? I would've expected the originating insert to work based on it's merits regardless of if the trigger worked or not.
Sep 09, 2016 05:01 PM|NewKid1nTown|LINK
Here is an example:
Create these tables and trigger.
CREATE TABLE TableA (someColumn CHAR(2))
CREATE TABLE TableB (someColumn CHAR(1))
CREATE TRIGGER trg
INSERT TableB (someColumn) SELECT someColumn FROM INSERTED
And run this insert. This should work. You should find both table inserted.
INSERT TableA(someColumn) VALUES('X')
But try this insert. This will cause an error because TableB's column is only CHAR(1). But what I didn't expect was the insert on TableA to fail also.
INSERT TableA(someColumn) VALUES('XX')
Am I wrong to expect TableA's insert to work regardless of the success or failure of the trigger to TableB?
Sep 09, 2016 05:16 PM|PatriceSc|LINK
It seems expected. A trigger just "adds" to what is done when using the INSERT statement which is still wrapped inside its own transaction so if the trigger fails, the whole transaction fails. Could you provide a repro for the exact error message you have.
Wondering but it would seem something such as maybe committing explicitely the transaction from the trigger?
Also your first move should be likely to fix the error rather than to pretend that all is well and to just commit the transaction.
Edit: gave a quick try and I get the same error message if I'm adding a COMMIT TRANSACTION in the trigger.
Sep 09, 2016 05:31 PM|NewKid1nTown|LINK
The error you saw in this example is the basically the crux of the problem in my original question. And what you said ["Also your first move should be likely to fix the error rather than to pretend that all is well and to just commit the transaction."]
makes sense to me.
So you think no matter what, SQL Server would not allow you to commit the originating statement if the trigger fails. Again, that seems to make logical sense, but wondering if there is a way around it. Or maybe there isn't because it breaks a fundamental
principal in a DB operation.
EDIT: The reason why I thought the originating insert should work regardless of the trigger is because of the "AFTER INSERT". And I thought ON INSERT would have the behavior I am seeing here. But maybe I need to read more about the distinction between AFTER
INSERT and ON INSERT.
Sep 09, 2016 06:32 PM|PatriceSc|LINK
AFTER is the same that is the INSERT happens and then the trigger (inside a single transaction). This is by opposition to INSTEAD OF that allows to replace entirely the insert statement with what you have done in the trigger.
https://msdn.microsoft.com/en-us/library/ms178110.aspx "The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger". My understanding is that you can roll back the insert from the trigger
but you can't commit the INSERT if the trigger code fails.
The point is that the purpose of a trigger is to precisely ENSURE that something is done when an INSERT/UPDATE/DELETE is done. So I doubt you can ingnore an error that happens in a trigger. You are still allowed to test some condition to avoid running a
statement that would fail.
Sep 09, 2016 08:55 PM|AZMatt|LINK
I agree with the above post. If you still need the original insert to happen even if the trigger fails, you could use a Try/Catch in the trigger to prevent the entire transaction from failing...