sql CLR trigger causing havoc in Access linked table

[Lew]

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

 

[Sylvain Lafontaine]

Your problem has nothing to do with the fact that you are using CLR or not
for your trigger and you will get the same effect if you are using classical
T-SQL for your insert trigger. A possible solution is to reset the
@@identity value at the end of the insert trigger, see:

http://groups.google.com/group/micr...dp+trigger+@@identity&rnum=1#93ec304f6d8e5c4a

For a strict point of vue of performance, it might also be a better idea to
use a pure T-SQL solution without any CLR because the latter is probably
setting an heavy load on the sql server.

For complicated databases, you will have to leave ODBC linked tables behind
and use passthrough queries and make your own updating or to use .NET
technologies instead.

Finally, the correct newsgroup for this would be m.p.a.odbclientsvr or
m.p.a.externaldata .

 

[Lew Burrus]

Thank you. I suspected I'd get the same
effect from straight SQL, but would be able
to compensate using scope_identity(). I
suppose it is obvious, as @@identity is a
global variable, but was the first time I've
seen this behavior from Access.

Am surprised to learn the CLR puts a heavy
strain on the server. Will investigate this,
as the opposite is my goal.

Thank you for the reply,
Lew