Linked tables to SQL and how to handle triggers on SQL ???

[Giri Palakodeti]

We have an Access Front end with linked tables to SQL 2000. There are
some critical tables that are SOX compliant and needs monitoring.
We have Insert/Update and Delete triggers on the criticle tables that
archives the changed records to a table _Archive_tableName

The proble is with Access fron end. Eeven though you are not physically
modifying a record but you open the record from front end and SAVE the record
the trigger fires as if a REAL change happened and wrting a record to archive
table. How can i avoid that?

 

[Norman Yuan]

That suggests that the logic in the trigers might be wrong. That is, when
triger fires, it should check if the "updated" record (when Access's bound
control saves) have been archived or not. If already archived before, you
could choose either ignore this "update", or archive it again (but delete
the previous archived record first)....

 

[Mary Chipman [MSFT]]

To troubleshoot the problem, open a Profiler trace and take a look the
statements being sent to the server when you save the record from the
Access UI. That's the only way to see what's going on under the covers
when Access "saves" a record.

--Mary

 

[Paul Shapiro]

Since Access is saving the row, the trigger will be initiated. You can't
avoid that. So you have 2 choices:
a) Modify the trigger code to only insert to the archive table if data has
actually changed.
b) Prevent Saving the row in Access until data has changed.

 

[Mary Chipman [MSFT]]

As far as b) goes, Access will send an UPDATE statement automatically
when the form is bound directly to a table or updateable view when the
cursor moves off of the record. You cannot "prevent" saving the row in
this situation -- you would need to create an unbound form and post
the changes via ADO code/pass-through querie . HTH,

--Mary