Savepoints in triggers

Question

I have 3 tables, let's say Table A, Table B and Table C.

I have a trigger on Table A which inserts data into Table B, and I also have a trigger on Table b which inputs data into Table C. On Table C, there's a trigger that executes a procedure.

The task is that even if something happens during the operations on the Table C i.e (any error occurs), the operations on Table A, B and C should continue and complete. The problem is that if something goes wrong on procedures, the operations on table A, B and C are aborted as well.

My table C trigger is :

CREATE TRIGGER [dbo].[Trg_Mst_TableC] 
ON [dbo].[TableC]
FOR UPDATE 
AS 
BEGIN
    DECLARE @flg_Error INT
    SET @flg_Error  = 0

    DECLARE @SLQString NVARCHAR(MAX) 

    SELECT @SLQString  = 'Text Message' 

    SAVE TRANSACTION MySavePoint                                    

    IF ISNULL(@SLQString, '') <> '' 
    BEGIN 
        EXEC spMsc_SendMsg 'Test', 'POST',  @SLQString

        IF @@error <> 0 BEGIN 
            ROLLBACK TRAN MySavePoint
        END 
    END
END

Here is my procedure :

CREATE PROCEDURE [dbo].[spMsc_SendMsg]  
     @msg_type NVARCHAR(50),  
     @getpost NVARCHAR(20),  
     @msg_txt NVARCHAR(MAX)  
AS  
BEGIN  
    SET NOCOUNT ON 

    SAVE TRANSACTION MySavePoint

    CREATE TABLE Test (XXX NVARCHAR(MAX))

    SELECT @msg_type, @getpost, @msg_txt
END

Now this procedure returns error msg for "Test" table. But i need to save the remaining transaction done till Trigger C. Please give me solution for this.

You can't `COMMIT` transaction in a trigger. The handling of the transaction should be done outside of it, not inside. What is the problem you're actually trying to solve here? — Thom A, Jul 03 '19 at 11:14
I recommend `XACT_ABORT ON`, `TRY .. CATCH` and having read all of [this](http://sommarskog.se/error_handling/Part1.html) before you even attempt any non-trivial error handling like this. If at all possible, do not do anything that could ever go wrong in a trigger if you do not also want the original transaction to abort -- if necessary, defer processing by inserting to a table or service broker queue. — Jeroen Mostert, Jul 03 '19 at 11:21
Oh, and triggers that trigger other triggers are essentially the tools of Satan. A design like this should probably be rethought in favor of a stored procedure call operating on table A, B and C, as the whole operation obviously represents something much more involved than just a single insert. Even where a trigger must be used because the client code can't be changed, it pays to not nest triggers but put all the logic on one level instead. `TRIGGER_NESTLEVEL()` can be used to check if a trigger is invoked from a nested context, and return without doing anything if that's the case. — Jeroen Mostert, Jul 03 '19 at 11:45

score 0 · Answer 1 · answered Jul 03 '19 at 13:24

0

Instead of using Transactions and Rollbacks, you can use TRY CATCH blocks to control what you want to happen if an error occurs in a specific trigger.

answered Jul 03 '19 at 13:24

Tab Alleman

31,483
7
36
52

Savepoints in triggers

1 Answers1