November 17, 2002 at 8:34 pm
I'm writing a procedure that builds auditing triggers for arbitrary tables and ran into a seemingly easy problem and can't figure it out.
For transactional replication there's an automatic update trigger that updates the uniqueifier column.
When I create an update trigger, these interact. So if I do one udpate, my own update trigger fires twice -- once for the change I made, once for the change that the update trigger makes to the msrepl_tran_version as part of replication.
The simple solution would appear to be
if update(msrepl_tran_version) then return
in my trigger. However, I'm trying to do this in a more generic fashion -- I do not know if the table is going to be replicated when the trigger is created. I COULD check if the column exists, but I ran into a problem with that -- it didn't exist at first, then we enabled replication for that table (which created the column) and then the trigger (which didn't have the IF) double-updated.
And if you put the IF statment in and the column doesn't exist at the time the trigger is created it won't syntax.
And I really don't want to use dynamic sql inside an update trigger (and frankly not sure if I can do IF UPDATED inside dynamic sql).
How do people handle update triggers in replicated tables, to keep from firing them twice? Is it always the above IF? Is there another alternative that might work whether or not the table is replicated with the same code?
November 18, 2002 at 3:09 am
When you create your trigger use NOT FOR REPLICATION. (see details in bol , search for create trigger)
November 18, 2002 at 5:17 am
Sorry, I must be phrasing my question badly. Let me try again.
Server A is the publisher. Server B is the subscriber. Table X has an update trigger aimed at auditing to table X_AUDIT.
Transactional replication. This means A's table X has a microsoft update trigger also which modifies msrepl_tran_version whenever any other statement modifies a row in X on A. So...
1) User updates row 1 in X on A
2) MSync_upd_trig_X_n on X on A fires, and changes msrepl_tran_version
3) My trigger fires on A as a result of step (2), still on server A
4) My trigger fires on A as a result of step (1), still on server A
5) Data is replicated to server B
6) My trigger does NOT fire on B as it is "NOT FOR REPLICATION"
My problem is on server A, not B, where my trigger fires twice for one update, and leaves two audit entries in the auditing table.
If the table is already set up for replication, this is easy. I put in
if update(msrepl_tran_version) then return
and this second update (which is always only for msrepl_tran_version) is skipped. But what I was hoping for is to write a trigger that would survive having the table go from not-replicated to replicated and back. So what I want is an equivilent IF statement that will still syntax (and not return) if the field msrepl_tran_version does not exist.
Either that, or a whole different approach. How do you keep transactional replication's MSync_upd_trig_x_n from causing your own update triggers from firing (or from doing anything).
What I have kludged around now is a generated test that joins INSERTED and DELETED and them checks each field to see if any field has changed (or is null which implies an insert or delete (or primary key change)). But that's really, really ugly and a lot of code.
Edited by - ferguson on 11/18/2002 05:19:00 AM
November 18, 2002 at 7:17 am
You need to use the trigger_nestlevel() function to either test if your trigger is already running, or if the replication trigger is running, or has run. There's several ways to use trigger_nestlevel(), depending on your requirements. See BOL.
Jay
Jay Madren
November 18, 2002 at 7:22 am
quote:
You need to use the trigger_nestlevel() function to either test if your trigger is already running, or if the replication trigger is running, or has run. There's several ways to use trigger_nestlevel(), depending on your requirements. See BOL.
But that just tells me if it is nested. If I know for sure that the change to my underlying table can ONLY be made directly and not through some other trigger that works fine. But what if there are other triggers on other tables which might update my audited table. Then I may be nested but not nested due to the replication triggers MS adds.
I realize there are any number of ways to do this for specific cases where no changes occur in the table. I'm trying to write a general auditing code generator which I can run on a table, then have the table get replicated and still work. It's that transition from not-replicated to replicated that appears to require additional code in the trigger, and the maintainers of the database might forget to rebuild the auditing triggers.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply