x

Database And Trigger

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.

more ▼

asked Nov 16, 2009 at 07:39 PM in Default

avatar image

user-454 (yahoo)
23 4 5 5

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Nov 17, 2009 at 07:28 AM

avatar image

Kristen ♦
2.2k 7 11 14

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 19, 2009 at 02:06 PM

avatar image

user-454 (yahoo)
23 4 5 5

(comments are locked)
10|1200 characters needed characters left

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.

Something like

IF NOT EXISTS (SELECT * FROM Table2 WHERE ChildId IN (SELECT Id FROM inserted)            
BEGIN            
    INSERT INTO Table2            
    ...            
END            

Or something like this

INSERT INTO Table2            
SELECT ...            
FROM inserted            
WHERE NOT EXISTS (SELECT * FROM Table2 WHERE Table2.ChildId = inserted.Id)            
more ▼

answered Nov 30, 2009 at 03:52 AM

avatar image

mrdenny
928 2 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017
x144

asked: Nov 16, 2009 at 07:39 PM

Seen: 1768 times

Last Updated: Nov 17, 2009 at 07:29 AM

Copyright 2016 Redgate Software. Privacy Policy