I am trying to write a trigger on a table that inserts data into another table. Now just to be programatically correct, I would like to catch any errors that occur during this procedure. I have not been able to do this. Currently this is what happens. Lets call these tables T1 and T2. Now when data is inserted in t1 the trigger fires. The trigger then runs the insert statement onto T2. Now if there are any errors while inserting on T2 the entire transaction including the insert for T1 gets rolled back! I would like it so that only the insert on T2 is rolled back and then I write the error message/code to another table. Now I tested this in oracle and it works like I want it to work. I tried to use @error variable to see if there were errors, and try to write errors to a error table but it does not work because it just kicks back everything and doesn't care for my exception routines ..:) Please help!

Mike

-- Anonymous, March 15, 2002

Answers

Mike,

First, although I assume you just made a typo, I should point out that the error variable is @@error rather than @error.

Next, although I have some doubts that the @@error cannot be used, let's just accept that it can't. I would approach the problem by having the trigger code check for the error condition in such a way that an error is not actually generated. For instance, let's say that the error is an attempt to insert a duplicate key into T2. Have the trigger check for the existence of the key and if it already exists, write an error message to another table and skip the insertion into T2.

Hope this helps,

Eric

-- Anonymous, March 15, 2002

Eric, Thanks for the reply. I understand that I can try to predict the error and check for it before the error occurs. However, it would be nice to have the ability to catch errors into an exception block somehow where you handle it appropriately. This feature is available in Oracle. Th ekey word used in PL/SQL is "exception".

This way the error never causes a complete rollback on the initial transaction and you can rollback only the current transaction.

What I need to do, is to let the insert on T1 go through and catch the exceptions for inserts on T2. Read the original problem statement for the referecnes fo T1, T2.

Thanks..

Mike

-- Anonymous, March 15, 2002

Mike,

You might try making use of the SET XACT_ABORT command as documented in the SQL Server Books Online. The SET XACT_ABORT OFF command is supposed to inhibit an error from aborting a transaction. I could not get it to work for me though.

Good Luck,

Eric

-- Anonymous, March 16, 2002