SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to ignore errors inside triggers


How to ignore errors inside triggers

Author
Message
vn
vn
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 1
Hi - I have a requirement to create an audit of every insert/update/delete on a table (say, TableA(col1, col2, col3). So, I created a mirror table with two additional columns. One column with Change_type to hold ('U;, 'I', 'D') the type of change to the record and another column with the timestamp. Hence, the mirror table will be TableA_mirror(col1, col2, col3, change_type, change_timestamp). I also created a after trigger that will load all data from the inserted or deleted tables into the mirror table. This setup works fine except when there are errors during the execution of the trigger. When there is an error in the trigger, I want to write the error message to a different error_log table and gracefully exit the trigger without propogating the error message back to the client application. How do I ensure that exceptions during trigger execution do not stop the application from functioning as if the triggers do not exist?
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28763 Visits: 9671
Why would there have an error in the trigger?? This is a simple insert into from inserted/deleted with change type hardcoded in the triger and the time_stamp set with a default constraint of getdate().
vn
vn
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 1

The Mirror table has a primary key constraint. When we try to insert two records with the same primary key ID in the mirror table, it generates an error. I want to track only the last change made to a record. Any previous inserts/updates that have a corresponding mirror table record need to be deleted.

Thank you for your response.


Carl Federl
Carl Federl
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3234 Visits: 4350
The bad news is this can be done with SQL only for some error but cannot be done for all types of errors.

When an error is encountered, a termination occurs that could include the statement, the procedure/trigger, the batch or the connection. One can only code for those errors that terminate the statement and not for any of the other terminate scopes.

See Erland Sommarskog's papers on this subject at
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

SQL = Scarcely Qualifies as a Language
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28763 Visits: 9671
This is something that you need to code correctly to avoid errors. Just run the delete before the update and you'll be fine.... and make sure you update the same line only once in a batch.
Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3786 Visits: 1463

My first work with triggers involved working with some very robust-looking code (written, of course, by someone long gone) that had all kinds of error handling built into every trigger. I used these as a template to develope my own triggers, but my error handling never worked. I went nuts trying to figure out why before I realized it is a waste of time.

A trigger is considered an extension of the original statement. The original statement (and the trigger) are aborted when an error occurs. You can detect the error in the code containing the DML statement, but not in the trigger.

If you need to put extra code in the trigger or elsewhere to avoid errors, go right ahead. But it is pointless to put any "IF @@ERROR > 0" type of code in a trigger. If there is an error, it won't get that far.





vn
vn
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 1
Thank you all for your responses. From the links sent by Carl Federl (part II), I believe it is possible to make the trigger not abort the transaction by issuing a RaiseError statement. Is there a way to somehow ensure that a RaiseError statement is executed and that the transaction of the calling statement is not aborted. It seems like the RaiseError can only be called when we do are doing some condition checking (like, Business rules) and we manually code to raise the error. But, when an error happens for reasons beyond our control, there is no way to ensure that the trigger does not abort the transaction. I am beginning that a trigger should never be used anywhere since there is no way to capture the error and handle it gracefully within the trigger.
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28763 Visits: 9671
That's the way the transaction should work, if something doesn't work, ALL FAILS. That way you can correct the problem and resend the whole transaction without having to find the rows that didn't work and resend only those, which may be a lot more complicated case to handle.
John F
John F
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 9

Woiuld this work?

UPDATE MirrorTable SET
a = inserted.a
b = inserted.b
c = inserted.c
FROM Inserted
WHERE Inserted.id = MirrorTable.id

INSERT INTO MirrorTable (id,a,b,c,d)
SELECT id,a,b,c,d
FROM Inserted
LEFT JOIN MirrorTable ON MirrorTable.id = Inserted.id
WHERE MirrorTable.id IS NULL

Its late, im tired, so I didnt check if I did the sql right. I'm sure its close though.


Julian Kuiters
Julian Kuiters
Mr or Mrs. 500
Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)

Group: General Forum Members
Points: 555 Visits: 1

I wouldn't put any constraints on the Mirror / Audit Log tables.

Think about it. All the constraints that were against the original table have already been applied before the trigger has been called. So all constraints have already been met.

If your Mirror table is only to track the LAST change, then your trigger should attempt updating rows in the Mirror table before inserting new ones. Again in this case I still wouldn't apply and constraints to the Mirror table.

Logic:

UPDATE MirrorTable
SET MirrorTable values equal to Inserted/Deleted
IF @@ROWCOUNT equals 0 -- There are no previous changes in MirrorTable
INSERT into MirrorTable from Inserted/Deleted

If you want your MirrorTable to be tracking all changes and someone updates a row, then updates the row again, your going to end up with rows in your mirror table that wont adhere to a unique primary key / unique constraint. If you are auditing the changes to records, not just insert/deletes then duplicate primary key records should be expected, and OK.






Julian Kuiters
juliankuiters.id.au
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search