trigger help needed
-
I need help with error handling within a trigger.
The trigger is on the base table and the indert into this table goes OK. The trigger then inserts the same row into an audit table. In certain cases this insert fails but I do not really care about it - I need the transaction on the based table to succeed though. Is there a way to skip this part of the trigger with error handling, and to make it do something else.
Thanks a lot for the help.
mj
Here is the example to reproduce this behaviour:
create table test_table1 (col1 int, col2 char(4), col3 varchar(4))
create table test_table2 (col1 int, col2 char(1), col3 varchar(1))
insert into test_table values (1,'AAAA','BBBB')
--the insert into test_table2 fails as the fields is only 1 char long.
create trigger t2 on test_table for insert as
BEGIN
declare @errorsave int,
@errno int,
@errmsg varchar(255)
set nocount on
BEGIN TRANSACTION
if @@error=0
insert into test_table2 select * from inserted
else
If @@error=1
BEGIN
insert into test_table2 values (1,'w','w')
COMMIT
END
ELSE
BEGIN
insert into test_table2 values (1,'r','r')
END
COMMIT TRANSACTION
error:
raiserror @errno @errmsg
rollback transaction
end
-
Trigger is a transaction by definition. If any error happens during trigger execution everything will be rolled back including INSERT/UPDATE/DELETE commited on table.
Remove all those toys BEGIN TRANSACTION / COMMIT and make sure your inserts will not fail in any possible case. Make checks before, not after performing insert.
Transactional SQL is not a good place to play with errors. Especially when it comes to triggers.
_____________
Code for TallyGenerator -
I apprecited your replay but it is not up to me to verify if the insert on the auditing table fails and why. I do not have any control over this database structures. (different deparment,etc.). They have a nightly process that synch the audit table from the tansactinal and do not care for the daily stuff. So my hands are tight but I loose transactions because of the auditing that I do not ahve control over...
I still could probably call a procedure from within the trigger to do the insert for me and to roll back whatever is failing(as long as it is on the auditing table). Is this going to work?
Thanks a lot, mj
-
You may call lots of procedures from your trigger. But first error appeared will roll back all executed commands, including INSERT into table, does not matter how deep you'll try to bury it in those SPs.
Just debug your trigger, is it so hard?
_____________
Code for TallyGenerator -
As mentioned by Sergiy, there is nothing possible like a part commit and part rollback possible in triggers, once your insert fails, everything is rolledback. If you are using stored procedures for inserting in the base tables, why don't you include a call in the same stored procedure that would insert data into your audit tables under a separate transaction scoping.
Prasad Bhogadi
www.inforaise.com -
How do you suggest to debug the trigger?
The most common errors are that the audit table has a constraint violation because some other application has already inserted this data by mistake.(actually leftover code of an old application that has never been removed after the new module was built - I do not have access). The example I gave is exactly what the current trigger does - 1 line - insert into aidut_table Select * from inserted
What will be your suggestion for such sutiation?
Thanks a lot,
mj
-
Well, if existing rows are your problem, what about something like
INSERT INTO audit_table
SELECT * FROM inserted
WHERE NOT EXISTS (select * from audit_table where primary_key = inserted.primary_key)
You may be better off with a LEFT JOIN and NULL check than with EXISTS, but the principle is the same. Insert only those rows that don't exist. If there are other unique constraints in addition to PK, you'll have to check them as well.
-
I successfully was able to solve this problem on Oracle by using AUTONOMOUS TRANSACTION. What will be the SQL server equivalent to this?
I cannot believe there's nothing... not possible.
Thanks a lot,
mj
-
Which problem?
There is no any problem except the one you created.
_____________
Code for TallyGenerator -
If you want to be sure that no errors occur, the only way I know about is checking data before insert. You can not command SQL Server to insert some rows and tell it "if insert fails, don't care about it and go on". In my opinion that's good. After all, you should know whether you want to insert a row or not...
-
I just did a quick read on this... it is possible to write to a table from within a trigger and not have it roll back but only if you open a new connection. One way to do that is to use xp_CmdShell to call an OSQL task but we all know the security and privs problems with that. Another way is to use sp_OA* routines but those (reportedly) have some memory leaks and, if you forget to close the connection, will drop your server to it's knees and require a reboot. sp_OA* also tends to be a bit slow in making/breaking the connections so there will be a fairly large performance hit, as well.
Overall, I'd say it's not worth it, but it is possible...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I do not think that I create any problem - not everything in the DB world is trivial and by the books... and you do not need to be rude, If you cannot help me.
I know what I want. I want if the audit insert fails, I need to be able to tell SQL server to ignore it, and to commit the first transaction.
If I was able to do so on Oracle, there should be a way to do it on SQl server as well. I just have to find a way where the info it is not logged like with the table variables.
Thanks a lot,Jeff. I was researching and got some ideas. If I make it work I'll post my solution so It'll be available for other people.
Thanks again, mj
-
mj,
may I ask why you decided not to check the data before insert? I know that life brings many situations not covered in books, but I still think that your attempts to ignore errors are potentially dangerous. IMHO it shouldn't be too complicated to write the trigger so that there is no need for any error dodging, and - taking into account what Jeff mentioned - it should also have better performance.
-
This is not a case of "not wanting to check the data" but more "I could not do it” situation. Most of the time it’s the data, but about 40% the table is not available.
Please, understand, I have no access to the audit table most of the time.
It takes me sometimes 2-3 days to be able to investigate what the reason for failure is.
And after all this time, of course, most of the time there's nothing for me to see. In a lot of cases the table (via linked server) is not available for me at all. Then the only thing I could do is to make a request, wait and wait, to get finally somebody on the phone just to hear, "Oh, OK, I did not know" and then takes usually about 1 hour to get something done and the table will be available again. For ALL THIS TIME I lose transactions which are vital for my application.
I'm tired of this. We have this problem for 6 solid months now and the management is aware to the point that nobody screams at me, but I still need to do heavy exercises to try to recover the data every 2-3 weeks. Sometimes I could do it, sometimes – not… because the application does not log everything equally – depends on the logic and the case.
So, I have no choice… I also thought to create a separate table on my side and to do bulk load to the audit side every 5 min, but I still do not have permission to do it.
So, the life is not easy…
Thanks a lot,
mj
-
Jeff, I would not suggest it.
I did it once and I end up in deadlock situation.
Command shell waits for transaction to be finished to start execute not rollable back command.
And transaction waits for xp_CmdShell to be completed to proceed.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply