This is related with the previous question that I had asked "Database Triggers and Commit". How to commit the triggering action (Insert in tbl1) though the action being done inside the trigger (Insert into tbl2) may fail. Kristen had suggested the commit and savepoint. I tried it but i am getting below error: "The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction." And the trigger failed to write into Table 1 again.
Suggest you try my other suggestion then which is to establish if there is a problem first (e.g. if the INSERT will cause a duplicate) and if so use RAISERROR or somesuch to signal the issue to the Client
The SAVEPOINT will work in a Stored Procedure, so if you call a Stored Procedure (e.g. instead of doing an INSERT directly from Dynamic SQL or similar) to make your INSERT that can have logic to handle the Rollback to Savepoint if incorrect data found.
answered Nov 17 '09 at 07:28 AM
1) I tried a stored procedure but the problem is i am not able to use "inserted" inside it as i guess it is outside the scope of the trigger.
2) Reg the raiseError i notice that adding a duplicate somehow seems to only cause an exception and not raise an error. Please look at the code below: INSERT INTO dbo.DTLOG( id,LogTime, ObjectName, LogReason, Msg) SELECT 1, getdate(), 'dtEvent_', @scopeid , CAST(@@IDENTITY AS NVARCHAR) FROM inserted i -- entering duplicate IF (@@ERROR > 0) BEGIN - do error handling
But the whole code is encapsulated within a try catch and it always goes to the CATCH part of the code. It can signal the issue but the main aim is whatever happens in the trigger it should not prevent the record from being written in the main table.
3) "SET XACT_ABORT OFF" is it a good option to use within a trigger. As it doesnot cause a rollback in case of an error in the main table.
thanks for your help.
answered Nov 19 '09 at 02:06 PM
The code being executed within the trigger is executed within the same transaction as the insert into the base table.
You'll need to put error catching code within the trigger in a TRY CATCH block so that if there is a problem the error isn't thrown. That or put logic in the trigger to ensure that no error would exist, and if an error would exist to ignore the records.
Or something like this