Re: sql CLR trigger causing havoc in Access linked table
- From: "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
- Date: Sat, 17 Feb 2007 12:01:02 -0500
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/microsoft.public.sqlserver.programming/browse_frm/thread/31c1dd4693ca321b/93ec304f6d8e5c4a?q=adp+trigger+%40%40identity&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 .
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Lew" <defretted@xxxxxxxxx> wrote in message
news:%23Z0HTNpUHHA.5108@xxxxxxxxxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: sql CLR trigger causing havoc in Access linked table
- From: Lew Burrus
- Re: sql CLR trigger causing havoc in Access linked table
- References:
- Prev by Date: sql CLR trigger causing havoc in Access linked table
- Next by Date: attn: augustine - very fantabulous music - ig - (1/1)
- Previous by thread: sql CLR trigger causing havoc in Access linked table
- Next by thread: Re: sql CLR trigger causing havoc in Access linked table
- Index(es):
Relevant Pages
|
