August 7, 2002 at 3:38 pm
I am still having problems with my subscriber table trigger.
I have a Transactional publication replication that pushes data to a table. On that table (on subscriber) I have a two triggers that should fire on insert and/or update.
I have set up auditing on the triggers (I am putting before and after info into a separate table). It looks like all the transactions are inserts only, regardless of which field is updated. I need to take action only if one field (FileDate) is updated or has new value inserted into it (it is NULL when the record is created which usualy happens days before FileDate is inserted).
Here are two records from the Audit table created by the insert trigger:
ID Action Auditdate InsertedFile FileDate Zipfile
------------------------------------------------------------------------------------------
41insert8/7/2002 2:29:45 PM2207085F.pdf8/7/2002 1:58:26 PM NULL
42insert8/7/2002 2:29:45 PM2207085F.pdf8/7/2002 1:58:26 PM 2207085.zip
------------------------------------------------------------------------------------------
as you can see both of them happened at the same time (AuditDate is DateTime), but since the record already existed after the first instert, shouldn't the second event be an UPDATE????
how can I tell if FileDate was inserted or updated. There is no Deleted table on inserts and it fires even if the 2207085.zip gets inserted.
Any ideas are greatly appreciated.
Jakub
August 7, 2002 at 3:47 pm
I bet that what's actually coming to the subscriber in the event of a record update is a delete-insert pair of transactions instead of a true update. The MS Support site has info ion this, and a work-around, at this link:
<http://support.microsoft.com/default.aspx?scid=kb;en-us;q302341>
HTH,
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
August 7, 2002 at 4:05 pm
Good answer. Profiling would reveal for sure. On the subject of auditing, you're logging before and after images? Why not just log the before version, the current version would always be in the table? Lots of different ways to audit, thats just the method I normally use. Also, if you're doing the same action in both triggers you can just combine into one. Finally, wouldnt it make more sense to do the auditing on the publisher?
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply