May 13, 2009 at 2:26 pm
I have an insert trigger which is coded as For Insert. I believe that this means that it is acutally running as After Insert.
The trigger has a try catch block with a transaction, and I have set up a test to insert threee records as a batch, the first two of which are invalid. The code appears to be executing correctly, but the data is never actually inserted into the table.
The insert is being wrapped into a stored procedure that I am stepping into in Visual Studio. All operations are executing properly, except that the insert that causes the trigger to fire never happens.
If the trigger is operating on as after insert, shouldnt the data be inserted regardless of the outcome of the trigger?
It appears that the error is causing the entire operation to roll back.
This is not the desired effect for me. I want the insert to happen regardless of anything going on in the trigger. Any ideas?
May 13, 2009 at 2:32 pm
Can you post the Trigger code?
I think that you need to cathc the error and suppress it. You can also read this article[/url] by some really smart guy who posts here.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 2:40 pm
I am catching the error, which exists in a transaction in the trigger, but it appears that the rollback command is cancelling the entire operation. I am guessing that the insert is operating as it's own transaction, and the transaction in the trigger is being viewed as a nested transaction. If that is so, then is it possible to name the trigger transaction and only rollback that one? I would post the code, but it is a huge trigger, but here is a synopsis of it:
BEGIN TRY
BEGIN TRANSACTION
--Do some updates, one of which will fail
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
EXEC uspRecordError
END CATCH
This code is in the trigger, and the rollback is happening for two records, and as a result the entire operation, including the insert is rolling back. I only want to rollback the transaction in the trigger
By the way, I looked at the article you mentioned, and it indicates that my assumption is correct. Therefore, is it possible to rollback just the trigger code. I still want the initial insert to happen.
May 13, 2009 at 2:51 pm
Hmm, I don't recall any mention in your original post that you were trying to Rollback actions in the trigger without rolling back the action that invoked the trigger.
Naming the transaction won't help, you'll have to establish a savepoint first and then rollback to that savepoint.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 2:57 pm
awesome, that is the answer I was looking for. Do you have a savepoint example that might work for saving the insert and rolling back the trigger related stuff. It sounds like I need to save the transaction as soon as the trigger starts, then perform my code. How do I then do the partial rollback, and commit the original insert?
May 13, 2009 at 3:11 pm
Well, it turns out that all I need to do is to make the first action of the trigger to be a commit transaction. This is probably a bastardization of the trigger process.
Disregard this post, the above approach will not work, so I guess I need to figure out how to add save points.
May 13, 2009 at 3:27 pm
The tricky thing about savepoints is that after rolling back to a savepoint you still have to do a COMMIT.
-- Get the initial transaction nesting level (will be at least 1)
DECLARE @InitialTranCount INT;
SET @InitialTranCount = @@TRANCOUNT;
BEGIN TRY
BEGIN TRANSACTION-- @@TRANCOUNT is now at least 2
SAVE TRAN TriggerTran-- Create a savepoint before any changes are made
--Do some updates, one of which will fail
COMMIT
END TRY
BEGIN CATCH
-- Is the trigger transaction still active and valid?
IF @@TRANCOUNT > @InitialTranCount AND XACT_STATE() = 1
BEGIN
ROLLBACK TRAN TriggerTran-- Rollback to savepoint
-- @@TRANCOUNT is not decremented
COMMIT-- Commit the now-empty transaction
-- @@TRANCOUNT is decremented
-- @@TRANCOUNT should now equal the value it had when the trigger started
END
EXEC uspRecordError
END CATCH
May 13, 2009 at 3:43 pm
Scott Coleman (5/13/2009)
The tricky thing about savepoints is that after rolling back to a savepoint you still have to do a COMMIT.
-- Get the initial transaction nesting level (will be at least 1)
DECLARE @InitialTranCount INT;
SET @InitialTranCount = @@TRANCOUNT;
BEGIN TRY
BEGIN TRANSACTION-- @@TRANCOUNT is now at least 2
SAVE TRAN TriggerTran-- Create a savepoint before any changes are made
--Do some updates, one of which will fail
COMMIT
END TRY
BEGIN CATCH
-- Is the trigger transaction still active and valid?
IF @@TRANCOUNT > @InitialTranCount AND XACT_STATE() = 1
BEGIN
ROLLBACK TRAN TriggerTran-- Rollback to savepoint
-- @@TRANCOUNT is not decremented
COMMIT-- Commit the now-empty transaction
-- @@TRANCOUNT is decremented
-- @@TRANCOUNT should now equal the value it had when the trigger started
END
EXEC uspRecordError
END CATCH
I haven't used save points, but wouldn't you want that outside the transaction you are starting in the TRY CATCH block?
May 13, 2009 at 4:50 pm
Here is a gutted version of my trigger.
CREATE TRIGGER [dbo].[tr_Stage]
ON [dbo].[Stage]
FOR INSERT
AS
DECLARE @ROW_COUNT int
SET @ROW_COUNT = @@ROWCOUNT
IF (@ROW_COUNT >= 1)
BEGIN
--Loop through the inserted records to perform the update and send the email.
DECLARE cur CURSOR FOR
SELECT FROM Inserted
OPEN cur
FETCH NEXT FROM cur
INTO
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--UPDATE A TABLE
--INSERT DATA INTO A TASK TABLE
--Send the Email
EXEC MSDB.DBO.SP_SEND_DBMAIL
END
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
EXEC uspRecordError
END CATCH
FETCH NEXT FROM cur
INTO
END
CLOSE cur
DEALLOCATE cur
END
There are three actions that could cause a rollback in the trigger, an update, and email, and an insert, if any of them fail, I want to abort the trigger code, but still insert.
I have tried several different ways to add a save point and manage the transaction, but I keep getting errors.
May 13, 2009 at 4:52 pm
here is some interesting info from Microsoft:
A trigger operates as if there were an outstanding transaction in effect when the trigger is executed. This is true whether the statement firing the trigger is in an implicit or explicit transaction.
When a statement begins executing in autocommit mode, there is an implied BEGIN TRANSACTION to allow the recovery of all modifications generated by the statement if it encounters an error. This implied transaction has no effect on the other statements in the batch because it is either committed or rolled back when the statement completes. This implied transaction is still in effect, however, when a trigger is called.
When a trigger executes, an implicit transaction is started. If the trigger completes execution and @@TRANCOUNT = 0, error 3609 occurs and the batch is terminated. If a BEGIN TRANSACTION statement is issued in a trigger, it creates a nested transaction. In this situation, when a COMMIT TRANSACTION statement is executed, the statement will apply only to the nested transaction.
When using ROLLBACK TRANSACTION in a trigger, be aware of the following behavior:
All data modifications made to that point in the current transaction are rolled back, including any that were made by the trigger.
The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back.
A ROLLBACK in a trigger closes and deallocates all cursors that were declared and opened in the batch containing the statement that fired the trigger. This includes cursors declared and opened in stored procedures called by the batch that fired the trigger. Cursors declared in a batch prior to the batch that fired the trigger are only closed. However, STATIC or INSENSITIVE cursors are left open if:
CURSOR_CLOSE_ON_COMMIT is set OFF.
The static cursor is either synchronous or a fully populated asynchronous cursor.
Instead of using ROLLBACK TRANSACTION, the SAVE TRANSACTION statement can be used to execute a partial rollback in a trigger.
May 13, 2009 at 5:02 pm
Something else I'd look at since you want the insert to complete regardless of the processing in the trigger, look at Service Broker. Move the processing done inside the trigger to a Service Broker application so that all the trigger has to do is send a message detailing what records need to be processed.
May 13, 2009 at 5:36 pm
Lynn Pettis (5/13/2009)I haven't used save points, but wouldn't you want that outside the transaction you are starting in the TRY CATCH block?
If you mean the SAVE TRAN statement should come before the BEGIN TRAN statement, I don't think so. BOL says that ROLLBACK TRAN savepoint doesn't decrement @@TRANCOUNT, so you still need a COMMIT to undo the effect of BEGIN TRAN. I like to keep the transaction nesting level at the ROLLBACK statement the same as it was at the SAVE statement, putting an uncommitted BEGIN TRAN in between might work but it seems like asking for trouble to me.
However, it occurs to me that the trigger is already running in a transaction so there is no need to start another one. The code I posted should work if the BEGIN TRAN and two COMMIT statements are removed, and the "@@TRANCOUNT >" test changed to "@@TRANCOUNT =". The ROLLBACK TRAN savepoint statement will still undo the work done in the trigger without affecting the original insert.
May 13, 2009 at 6:00 pm
Now that I've seen the longer version of your trigger, I have to agree that the email should be handled outside of the trigger. And if you get rid of the email, you can probably get rid of the cursor.
The partial rollback with savepoints should work if you change the BEGIN TRAN statements to SAVE TRAN savepoint_name, change ROLLBACK to ROLLBACK savepoint_name, and eliminate all the COMMITs. You can even use the same savepoint_name throughout.
May 13, 2009 at 6:05 pm
Lynn Pettis (5/13/2009)
Something else I'd look at since you want the insert to complete regardless of the processing in the trigger, look at Service Broker. Move the processing done inside the trigger to a Service Broker application so that all the trigger has to do is send a message detailing what records need to be processed.
Like I said, I wasn't sure. I have never used SAVE points so it made sense to at least question it to be sure it was accurate.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply