How to make all stmnts. in a TRIGGER execute regardless of an error

[bobyjose]

Hello ,<br>I'm using Microsoft sql 7.0.<br>I have a trigger on a table which contains code to insert data into a second table. If this insert fails (for eg. due to a referential integrity violation), it raises an error and the trigger terminates without executing the remaining statements (where i check for the status of this insert using @@error and @@rowcount and do corrective measures).<br><br>How can all the statements in a trigger be executed regardless of the failure of one update statement so that i can have cleanup/error handling actions performed ?<br><br>I've put all statements in the trigger inside a transaction.<br>Can anyone please help?<br>Thanks in advance,<br>Boby.

 

[ColinM]

Don't put them inside a single transaction?

 

[bobyjose]

Thanx Collin, for the reply. But, unfortunately, the trigger executes only upto the insert statement where the error occurs, whether or not you put the statements in a transaction!!<br>Any other suggestion?

 

[ColinM]

Are you implementing the Referential Integrity using foreign keys?<br>If so you might want to disable these and write code to do the RI inside the triggers, where you could check for a related record.<br><br>

 

[bobyjose]

Collin..Thats exactly whatz going worng! When i insert data that violates Ref. Integrity into another table&nbsp;&nbsp;from the trigger, it cancels the execution of the remaining statements. <br>You mean to say that i've to disable all foreign keys in the table to which i'm inserting data and have to do the check using a tigger on that table?<br>Ooops.. that table is having 62 fields, with 10 foreign keys!!<br>Is there any other workaround?<br>By the by, i wonder why its like this?

 

[ColinM]

Don't know of any workarounds.<br>Other than making sure you data doesn't violate RI <br><br>

 

[clawton]

Bobyjose, Colin<br><br>As I understand it (and please correct me if I'm wrong), the statements within a trigger are always embedded in the transaction that causes it to fire, whether implicit or explicit.&nbsp;&nbsp;Therefore, if a statement within the trigger fails, it will roll back the entire transaction, including the one that called it. The fact that you've defined a transaction within the trigger has no effect. There's no way around this I'm afraid.<br><br>The only possible way I can think of is to use stored procedures.&nbsp;&nbsp;These always execute as separate transaction.&nbsp;&nbsp;So, if you create SP's for each of the updates within your trigger, then use the trigger to execute them one by one, the remainder of the trigger sould survive the failure of the component parts.&nbsp;&nbsp;Take care with rollback and commit within called SP's - these will affect every statement from the start of the transaction - including the statement that fired the trigger.<br><br>I havn't tested this, but the theory seems sound enough.<br><br>Let me know the results.<br><br>Cheers<br><br>Chris.

 

[gummadi]

Hi bobyjose,<br><br>I think your problem will be solved if you you use join statement to eliminate the rows that voilating referential<br>integrity.<br><br>If insert is into same table which is having trigger,then <br>join statement will not useful.but if if you are inserting into another we can always write join staement to eliminate unwanted rows.<br><br>If you want clear details,please furnish the two table ,with one or more records you want insert.<br>I can explain how we can eliminate the rows<br><br><br>bye<br>gummadi<br><br>&nbsp;

 

[ColinM]

Yeah you can write your trigger to check that the required records exists. i.e On an insert trigger<br><br>CREATE TRIGGER INS_titleauthor<br>ON titleauthor<br>FOR INSERT<br><br>-- Do any rows exists in the inserted table that do not have<br>-- a matching ID in titles?<br><br>IF EXISTS<br>&nbsp;&nbsp;&nbsp;(SELECT * FROM inserted WHERE inserted.title_id NOT IN<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(SELECT titles.title_id FROM titles))<br>&nbsp;&nbsp;&nbsp;&nbsp;BEGIN<br>&nbsp;&nbsp;&nbsp;&nbsp;RAISERROR('No matching title found.Aborted',16,1)<br>&nbsp;&nbsp;&nbsp;&nbsp;ROLLBACK TRAN<br>&nbsp;&nbsp;&nbsp;&nbsp;END<br><br>This is similar to an example from Inside SQL Server.<br>