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

user-454 (yahoo) gravatar 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

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

kristen can u please look into the answer for this topic
Nov 19, 2009 at 02:07 PM user-454 (yahoo)
(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

user-454 (yahoo) gravatar image

user-454 (yahoo)
23 4 5 5

1) Sorry, I meant to call a Stored Procedure from your application and have it attempt to catch all the possible issues before attempting to save the data (rather than calling an SProc from your Trigger). Its all "a bit too late" by the time the trigger discovers that there are duplicates.
Nov 19, 2009 at 02:44 PM Kristen ♦
2) You need to prevent adding duplciates. Just do an IF EXISTS test for the duplicates before making the INSERT - and if you find pre-existing matching values then take suitable action - e.g. use RAISERROR to signal the situation to the application. But better would be if you use a stored procedure instead that can detect such things and pass a meantingful code back to the application - a TRIGGER can only really abort the transaction, it can't return a "Failure Code" to the application - other than by using RAISERROR which is not a good means of signalling to the application.
Nov 19, 2009 at 02:46 PM Kristen ♦

Inserting duplicates is just a example that i showed. The main purpose of what i want to do it is. When a record is inserted into the main table (tbl1) and the after insert trigger is called to do other stuff. In case something goes wrong with the trigger i do not want it to affect the main table (tb1). Anything that can go wrong with the trigger nothing should happen to the main tables insert.

How can i call a stored procedure? bcoz the insert will be taking place every few mseconds and i also donot want a delay and that is why i am using a trigger rather than a scheduled job.
Nov 19, 2009 at 05:12 PM user-454 (yahoo)
(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

mrdenny gravatar image

mrdenny
928 3

Did you mean "if NOT exists" in your first example?
Nov 30, 2009 at 04:14 AM Kristen ♦
Yep, sure did. Thanks.
Nov 30, 2009 at 04:20 AM mrdenny
I solved this problem by using SET XACT_ABORT OFF
as the first line in the trigger. So no matter what kind of error occurs whether duplicate, syncatical in dynamic tsql -> the statements within the trigger is rolled back but not the actual insert which caused the trigger.
Dec 11, 2009 at 05:57 PM user-454 (yahoo)
The reason that I didn't mention this is that your main table and your child table will now be out of sync.
Dec 12, 2009 at 07:09 PM mrdenny
(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:

x1951
x121

asked: Nov 16, 2009 at 07:39 PM

Seen: 1615 times

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