September 1, 2005 at 12:34 pm
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?
September 1, 2005 at 1:13 pm
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().
September 1, 2005 at 1:22 pm
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.
September 1, 2005 at 1:24 pm
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
September 1, 2005 at 1:26 pm
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.
September 2, 2005 at 8:36 am
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.
September 2, 2005 at 12:42 pm
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.
September 2, 2005 at 12:45 pm
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.
September 3, 2005 at 12:40 am
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.
September 4, 2005 at 8:17 pm
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
September 4, 2005 at 8:53 pm
your method wont work the way you have it setup...inserted and deleted are multiple row tables, not single row tables. You might have a @@rowount > 0 when updating, but still need to insert the new records.
thats why I wrote my like this:
-- do all updates (records in mirror table already exist)
UPDATE MirrorTable SET
a = inserted.a
b = inserted.b
c = inserted.c
FROM Inserted
WHERE Inserted.id = MirrorTable.id
-- do all inserts..mirror table record's dont exist
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
not that I am sure mine is right either....
- John
September 4, 2005 at 11:41 pm
your right. sorry, I've had my head stuck in a different problem all day. Ignore the whole @@ROWCOUNT thing, that has no place in a trigger.
What you've written above looks good to me, provided theres an id column on the table. Otherwise you'd just have to join on all the primary key columns for the INSERT.
Julian Kuiters
juliankuiters.id.au
September 9, 2005 at 10:11 am
Thank you all for your responses. I have been working on this for a few days now based on the responses I have got. I think I need to clarify on how the mirror tables are being used. The data in the mirror table is being fed to other systems/databases. Because of this requirement, when we pull data from this table (every 15 minutes) we are only interested in the last snapshot of the record.
Also, I need to take into account the possibility that on the insert/update/delete statements, multiple records could get updated by one statement. Hence, there will be multiple records in the inserted/deleted tables at any point in time.
September 9, 2005 at 11:32 am
refer to my post about 2 above this one. I addresed all of this for you.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply