CODEWITHSUNDEEP
sql-servererror-handlingtriggerscursor
McLawrence
McLawrence

Reputation: 1

SQL Server error handling in cursor inside trigger

I'm new to SQL Server error handling, and my English isn't too clear, so I apologize in advance for any misuderstandings.

The problem is: I insert multiple records into a table. The table has an AFTER INSERT trigger, which is processing the records one by one in the FETCH WHILE cycle with a cursor. If something error happens, everything is rolling back. So if there is just one wrong field in the inserted records, I lost all of them. And the insert rolls back also, so I can't find the wrong record. So I need to handle the errors inside of the cursor, to rollback only the wrong record.

I made a test database with 3 tables:

tA

VarSmallint smallint
VarTinyint tinyint
String varchar(20)

tB

ID int (PK, identity)
Timestamp datetime (default: getdate())
VarSmallint smallint
VarTinyint tinyint
String varchar(20)

tC

ID int PK
Timestamp datetime
VarTinyint1 tinyint
VarTinyint2 tinyint
String varchar(10)

tA contains 3 records with 1 wrong one. I insert this content into tB. tB has the trigger, and inserts the records into tC ony by one.

tC has only tinyint variables, so there can be problem to insert values greater than 255. This is the point where the error occurs for the test!

My trigger is:

ALTER TRIGGER [dbo].[trg_tB] 
ON  [dbo].[tB] 
AFTER INSERT
AS 
BEGIN
    IF @@rowcount = 0
        RETURN;

    SET NOCOUNT ON;

    DECLARE
         @ID                AS int,
         @Timestamp     AS datetime,
         @VarSmallint   AS smallint,
         @VarTinyint        AS tinyint,
         @String            AS varchar(20),

    DECLARE curNyers CURSOR DYNAMIC
    FOR
        SELECT
            [ID], [Timestamp], [VarSmallint], [VarTinyint], [String]
        FROM INSERTED
        ORDER BY [ID]

    OPEN curNyers

    FETCH NEXT FROM curNyers INTO @ID, @Timestamp, @VarSmallint, @VarTinyint, @String

    WHILE @@FETCH_STATUS = 0
    BEGIN
    BEGIN TRY
        BEGIN TRAN

        INSERT INTO [dbo].[tC]([ID], [Timestamp], [VarTinyint1], [VarTinyint2], [String])
        VALUES (@ID, @Timestamp, @VarSmallint, @VarTinyint, @String)

        COMMIT TRAN
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN

        INSERT INTO [dbo].[tErrorLog]([ErrorTime], [UserName], [ErrorNumber],
                                      [ErrorSeverity], [ErrorState],
                                      [ErrorProcedure], [ErrorLine],
                                      [ErrorMessage], [RecordID])
        VALUES (SYSDATETIME(), SUSER_NAME(), ERROR_NUMBER(),
                ERROR_SEVERITY(), ERROR_STATE(),
                ERROR_PROCEDURE(), ERROR_LINE(),
                ERROR_MESSAGE(), @ID)

        END CATCH

        FETCH NEXT FROM curNyers INTO @ID, @Timestamp, @VarSmallint, @VarTinyint, @String
    END

    CLOSE curNyers
    DEALLOCATE curNyers
END

If I insert 2 good records with 1 wrong, everything is rolling back and I got an error:

Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Please help me! How to modify this trigger to work well?

If I insert the wrong record, I need:

  1. All the inserted records in tB
  2. All the good records in tC
  3. Error logged in tErrorLog

Thanks!

Upvotes: 0

Views: 1725

Answers (2)

McLawrence
McLawrence

Reputation: 1

I deleted the TRIGGER, and copy-pasted the code from it into a STORED PROCEDURE.

Then I added a row Status to tB, and set defaults to 0.

1 is "Record processed OK", 2 is "Record processing fault".

I fill the cursor with WHERE Status = 0.

In the TRY section I update the status to 1, in the CATCH section I UPDATE it to 2.

I have no jobs, so I run the SP from Windows scheduler with a batch file with the SQLCMD command.

Now the processing works well, moreover it worked well for the first time. Thanks for the help!

Upvotes: 0

marc_s
marc_s

Reputation: 755187

You have TWO major disasters in your trigger:

  1. do not use a cursor inside a trigger - that's just horrible! Triggers fire whenever a given operation happens - you have little control over when and how many times they fire. Therefore, in order not to compromise your system performance too much, triggers should be very small, fast, nimble - do not do any heavy lifting and extensive processing in a trigger. A cursor is anything but nimble and fast - it's a resource-hog, processor-hog, memory-leaking monster - AVOID those whenever you can, and most definitely inside a trigger! (and you don't need them, 99% of the cases, anyway)

    You can rewrite your whole logic into this one single, fast, set-based statement:

    ALTER TRIGGER [dbo].[trg_tB] 
ON [dbo].[tB] 
AFTER INSERT
AS 
BEGIN
    INSERT INTO [dbo].[tC]([ID], [Timestamp], [VarTinyint1], [VarTinyint2], [String])
        SELECT
            [ID], [Timestamp], [VarSmallint], [VarTinyint], [String]
        FROM 
            INSERTED
END

  2. Never call COMMIT TRAN inside a trigger. The trigger executes inside the context and transaction of the statement that caused it to fire - if everything is OK, just let the trigger finish and then the transaction will be committed just fine. If you need to abort, call ROLLBACK. But never ever call COMMIT TRAN in the middle of a trigger. Just don't.....

Upvotes: 1

Related Questions