locked
insert trigger failing -> this fails the original insert? RRS feed

  • Question

  • User154499744 posted

    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?

    Friday, September 9, 2016 1:00 PM

Answers

  • User753101303 posted

    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.

    According to 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 9, 2016 6:32 PM

All replies

  • User-2010311731 posted

    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?

    Friday, September 9, 2016 2:56 PM
  • User154499744 posted

    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?

    Friday, September 9, 2016 3:26 PM
  • User-2010311731 posted

    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?

    Friday, September 9, 2016 4:05 PM
  • User154499744 posted

    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.

    Friday, September 9, 2016 4:39 PM
  • User154499744 posted

    Here is an example:

    Create these tables and trigger.

    CREATE TABLE TableA (someColumn CHAR(2))
    CREATE TABLE TableB (someColumn CHAR(1))
    
    CREATE TRIGGER trg
    ON TableA
    AFTER INSERT
    AS
    BEGIN
    	INSERT TableB (someColumn) SELECT someColumn FROM INSERTED
    END
    

    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?

    Friday, September 9, 2016 5:01 PM
  • User753101303 posted

    Hi,

    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.

    Friday, September 9, 2016 5:16 PM
  • User154499744 posted

    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.

    Friday, September 9, 2016 5:31 PM
  • User753101303 posted

    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.

    According to 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 9, 2016 6:32 PM
  • User-2010311731 posted

    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...

    https://msdn.microsoft.com/en-us/library/ms175976.aspx?f=255&MSPPError=-2147217396

    Friday, September 9, 2016 8:55 PM