sql CLR trigger causing havoc in Access linked table

Excuse if this is not the correct group -- I am not using an adp, but an upsized mdb file with linked tables. There seems to be no group made for Access-SQL Server discussions other than this one.

Have been running an Access front end (mdb) on a SQL Server back end for several years. Will soon be migrating to SQL2005. Have detected a showstopper of a problem.

Some background:
One reason to move to SQL05 is the use of CLR for a generic audit trigger (based on code made available by others). Trigger works well. For an example, see http://www.sqlservercentral.com/columnists/dziffer/creatingagenericaudittriggerwithsql2005clr.asp

Here’s the problem: when Access inserts a new row in a linked, audited table, SQL should return the ID (identity) of the new record, and then Access can refresh that row – the ID field’s value and field value with defaults should be available to the Access table. However, when the CLR trigger fires, SQL returns the ID of the row from the audit table, not the table where the original insert occurred. So, if I am inserted my 200th record into table1, and this creates a 49th record in my audit table, AND table1 contains a record where ID=49, then the Access table will change its current record to row where ID=49. The new record still gets inserted correctly; the trigger fires correctly; the problem is solely how Access represents the current/new record. I probably don’t need to explain the danger of having the current record flip on a user without notice.

Anyway, am assuming this might be related to difference between @@identity and scope_identity(), but am not able to tell. What’s curiouser, if the id returned by the audit table is not found in table1, then Access behaves as it should.

Any advice on how to proceed? Or do I need to backtrack, and implement a purely SQL solution? Is anyone from Microsoft out there who understands why is occurring?

Lew

.

Relevant Pages

  • sql CLR trigger causing havoc in Access linked table
    ... Have been running an Access front end on a SQL Server back end for several years. ... However, when the CLR trigger fires, SQL returns the ID of the row from the audit table, not the table where the original insert occurred. ... So, if I am inserted my 200th record into table1, and this creates a 49th record in my audit table, AND table1 contains a record where ID=49, then the Access table will change its current record to row where ID=49. ... The new record still gets inserted correctly; the trigger fires correctly; the problem is solely how Access represents the current/new record. ...
    (microsoft.public.data.odbc)
  • Re: Problems launching DTS package from Stored Procedure
    ... exec sp_bcp_dbcmptlevel "UsersDatabase" SET FMTONLY ON Select * from Users1 ... Audit Logout ... Here is an example of an UNSUCCESSFUL sql profiler log: ... > I cannot run enterprise manager on our server because it is not installed. ...
    (microsoft.public.sqlserver.dts)
  • Re: Sql Profiler trace file converted to a table
    ... 'Login Failed') ... 'SQL Transaction') ... 'CursorPrepare') ... 'Audit Statement GDR') ...
    (microsoft.public.sqlserver.security)
  • Re: file backup component
    ... I've gotten my problem solved.The reason why my SQL service sometimes can ... Otherwise you can try to use audit techniques to find out who's trying to ... file backup app or any other app that need LDF file a while. ... Delay the start of the SQL server service until manual start. ...
    (microsoft.public.windowsxp.embedded)
  • Re: Best way to clear stale data from very active audit tables
    ... has a bunch of audit style tables. ... We're experiencing significant ... our application can also run against sql server, which while fast, ... About partitions I can't offer any tips sorry... ...
    (comp.databases.oracle.server)