Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to ignore errors inside triggers Expand / Collapse
Author
Message
Posted Thursday, September 01, 2005 12:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 04, 2006 5:07 PM
Points: 26, 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?
Post #216278
Posted Thursday, September 01, 2005 1:13 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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().
Post #216296
Posted Thursday, September 01, 2005 1:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 04, 2006 5:07 PM
Points: 26, 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.

Post #216303
Posted Thursday, September 01, 2005 1:24 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 2,285, Visits: 4,222
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
Post #216305
Posted Thursday, September 01, 2005 1:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #216307
Posted Friday, September 02, 2005 8:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:24 PM
Points: 2,835, Visits: 1,124

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.




Post #216584
Posted Friday, September 02, 2005 12:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 04, 2006 5:07 PM
Points: 26, 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.
Post #216741
Posted Friday, September 02, 2005 12:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #216742
Posted Saturday, September 03, 2005 12:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 06, 2008 9:55 PM
Points: 49, 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.

Post #216808
Posted Sunday, September 04, 2005 8:17 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, June 12, 2007 12:49 AM
Points: 419, 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
Post #216887
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse