SQL INSERT trigger side effects not always displayed in ADP front end without refresh

  • Thread starter Thread starter Malcolm Cook
  • Start date Start date
M

Malcolm Cook

Hi,

I have a table which has an INSERT trigger that sets a field as a side effect.

For a while, my ADP client would refresh the current records with fields from the entire row after inserting a new row, which was good, because the screen would should the side-effected column.

Now this has stopped happening and I can't figure out why. (If I refresh the record set I see the side effected update).

I just created another (albeit simpler) table with a similar trigger to set a field as a side effect, and, LO, indeed it works.

So, what's the difference?

I ran the SQL Profiler (1st time) to get a trace.

I can see with the table ItWorks the final three lines doing the insert, getting the @@IDENTITY, and then refreshing the entire row, like this:
exec sp_executesql N'INSERT INTO "SILO"."dbo"."ItWorks" ("c") VALUES (@P1)', N'@P1 nvarchar(2)', N'dd'
SELECT @@IDENTITY
exec sp_executesql N'SELECT * FROM ( SELECT * FROM "dbo"."ItWorks" ) AS DRVD_TBL WHERE "a" = @P1', N'@P1 bigint', 34
However, it the table 'ItFails' the final select is missing:
exec sp_executesql N'INSERT INTO "SILO"."dbo"."ItFails" ("req_ID") VALUES (@P1)', N'@P1 bigint', 994
SELECT @@IDENTITY


I expect that there is something different between them but I can't figure out what could cause the change.

1.. Neither of the tables has a resync command.
2.. They both have IDENTIY columns as (indexed) primary keys
3.. They both have timestamps (I've tried removing them on a lark, but, no dice).
4.. Neither have ROWGUID columns (could this make a difference anyway?)

Behind my sig are the two traces of opening each table from the database window and inserting a single row. If you can make any sense of them or otherwise suggest something else I might check or do I would be grateful. I inserted some blank lines to break the events into logical groups. The 2nd trace has an extra group of trace events for handling foreign key lookups.

Thanks!


--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA


----------------This is the trace from 'ItWorks'

SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'ItWorks',NULL,NULL)
SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'ItWorks',N'column',NULL) ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC
SET ROWCOUNT 10000
SELECT * FROM "dbo"."ItWorks"
SET ROWCOUNT 0


EXEC sp_MShelpcolumns N'ItWorks', NULL, N'id', 1
SET NO_BROWSETABLE OFF
EXEC sp_MShelpcolumns N'dbo.ItWorks' , NULL, 'id', 1
SELECT IDENT_SEED('ItWorks')
SELECT IDENT_INCR('ItWorks')
SET NO_BROWSETABLE ON
exec sp_executesql N'INSERT INTO "SILO"."dbo"."ItWorks" ("c") VALUES (@P1)', N'@P1 nvarchar(2)', N'dd'
SELECT @@IDENTITY
exec sp_executesql N'SELECT * FROM ( SELECT * FROM "dbo"."ItWorks" ) AS DRVD_TBL WHERE "a" = @P1', N'@P1 bigint', 34





----------------This is the trace from 'ItFails'


SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'ItFails',NULL,NULL)
SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'ItFails',N'column',NULL) ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC
SET ROWCOUNT 10000
SELECT * FROM "dbo"."ItFails"
SET ROWCOUNT 0


SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'SomeLookup',N'column',NULL) ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC
SELECT * FROM "dbo"."SomeLookup"
SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'SomeLookup',N'column',NULL) ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC
EXEC sp_MShelpcolumns N'ItFails', NULL, N'id', 1
SELECT * FROM "req"
SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'req',N'column',NULL) ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC


SET NO_BROWSETABLE OFF
EXEC sp_MShelpcolumns N'dbo.ItFails' , NULL, 'id', 1
SELECT IDENT_SEED('ItFails')
SELECT IDENT_INCR('ItFails')
SET NO_BROWSETABLE ON
exec sp_executesql N'INSERT INTO "SILO"."dbo"."ItFails" ("req_ID") VALUES (@P1)', N'@P1 bigint', 994
SELECT @@IDENTITY
 
Hey!

I figured it out for myself pretty much....

It seems that the difference between the two tables is that the 'ItFails' had an alternate key on it THAT WAS CLUSTERED, and the column being updated participated in the key.

Access/ADC was using the clustered key instead of the primary surrogate IDENTITY key to manage inserts and deletes

If I make the alternate key not be clustered (though still UNIQUE), Access goes back to using the primary key. And it all works again. Very nice.

I'm not sure at what level this decision is happening. Access core? THe client manager?

If I were writing Access/ADP, I would probably not use the alternate key, but always use any key declared as 'primary' key (especially if it is an IDENTITY). But, I'm interested in Microsoft's reason for prefering to use the clustered index. Anyone know?

Gotta remember this.

I wonder - same deal in ACC2003 (I'm in ACC2002).

Cheers,

-Malcolm

Hi,

I have a table which has an INSERT trigger that sets a field as a side effect.

For a while, my ADP client would refresh the current records with fields from the entire row after inserting a new row, which was good, because the screen would should the side-effected column.

Now this has stopped happening and I can't figure out why. (If I refresh the record set I see the side effected update).

I just created another (albeit simpler) table with a similar trigger to set a field as a side effect, and, LO, indeed it works.

So, what's the difference?

I ran the SQL Profiler (1st time) to get a trace.

I can see with the table ItWorks the final three lines doing the insert, getting the @@IDENTITY, and then refreshing the entire row, like this:
exec sp_executesql N'INSERT INTO "SILO"."dbo"."ItWorks" ("c") VALUES (@P1)', N'@P1 nvarchar(2)', N'dd'
SELECT @@IDENTITY
exec sp_executesql N'SELECT * FROM ( SELECT * FROM "dbo"."ItWorks" ) AS DRVD_TBL WHERE "a" = @P1', N'@P1 bigint', 34
However, it the table 'ItFails' the final select is missing:
exec sp_executesql N'INSERT INTO "SILO"."dbo"."ItFails" ("req_ID") VALUES (@P1)', N'@P1 bigint', 994
SELECT @@IDENTITY


I expect that there is something different between them but I can't figure out what could cause the change.

1.. Neither of the tables has a resync command.
2.. They both have IDENTIY columns as (indexed) primary keys
3.. They both have timestamps (I've tried removing them on a lark, but, no dice).
4.. Neither have ROWGUID columns (could this make a difference anyway?)

Behind my sig are the two traces of opening each table from the database window and inserting a single row. If you can make any sense of them or otherwise suggest something else I might check or do I would be grateful. I inserted some blank lines to break the events into logical groups. The 2nd trace has an extra group of trace events for handling foreign key lookups.

Thanks!


--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA


----------------This is the trace from 'ItWorks'

SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'ItWorks',NULL,NULL)
SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'ItWorks',N'column',NULL) ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC
SET ROWCOUNT 10000
SELECT * FROM "dbo"."ItWorks"
SET ROWCOUNT 0


EXEC sp_MShelpcolumns N'ItWorks', NULL, N'id', 1
SET NO_BROWSETABLE OFF
EXEC sp_MShelpcolumns N'dbo.ItWorks' , NULL, 'id', 1
SELECT IDENT_SEED('ItWorks')
SELECT IDENT_INCR('ItWorks')
SET NO_BROWSETABLE ON
exec sp_executesql N'INSERT INTO "SILO"."dbo"."ItWorks" ("c") VALUES (@P1)', N'@P1 nvarchar(2)', N'dd'
SELECT @@IDENTITY
exec sp_executesql N'SELECT * FROM ( SELECT * FROM "dbo"."ItWorks" ) AS DRVD_TBL WHERE "a" = @P1', N'@P1 bigint', 34





----------------This is the trace from 'ItFails'


SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'ItFails',NULL,NULL)
SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'ItFails',N'column',NULL) ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC
SET ROWCOUNT 10000
SELECT * FROM "dbo"."ItFails"
SET ROWCOUNT 0


SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'SomeLookup',N'column',NULL) ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC
SELECT * FROM "dbo"."SomeLookup"
SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'SomeLookup',N'column',NULL) ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC
EXEC sp_MShelpcolumns N'ItFails', NULL, N'id', 1
SELECT * FROM "req"
SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'req',N'column',NULL) ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC


SET NO_BROWSETABLE OFF
EXEC sp_MShelpcolumns N'dbo.ItFails' , NULL, 'id', 1
SELECT IDENT_SEED('ItFails')
SELECT IDENT_INCR('ItFails')
SET NO_BROWSETABLE ON
exec sp_executesql N'INSERT INTO "SILO"."dbo"."ItFails" ("req_ID") VALUES (@P1)', N'@P1 bigint', 994
SELECT @@IDENTITY
 
Hey!

I figured it out for myself pretty much....

It seems that the difference between the two tables is that the 'ItFails' had an alternate key on it THAT WAS CLUSTERED, and the column being updated participated in the key.

Access/ADC was using the clustered key instead of the primary surrogate IDENTITY key to manage inserts and deletes
Click to expand...

Unfortunately, this has always been the behavior of both ADO and DAO. If you
have a clustered index, that's seen as the primary key. That's true even if
the clustered index is not unique!

With DAO (MDBs), the answer is simple - link to a view, and set the virtual
primary key to be the true primary key. In ADO (ADPs), if it's wrong, it will
stay wrong, and you pretty much can't work around it because ADO/ADPs want to
think for you and go around behind your views to determine the primary keys of
the tables. I don't think you can solve it using WITH VIEW METADATA either.

So, my first reaction to this is to stop using non-primary clustered indexes
with ADPs. My second reaction is that this is one more thing on the
ever-growing stack of reasons I don't like ADPs and probably won't ever use
them again for production work. They're great for prototyping.
 
Steve,

Some time ago I read your considerations in
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&[email protected]

Do you have any changes of heart or knowledge on any of those points, esp
w.r.t. ACC2002?

#2 views w/ multiple Timestamps issue?

#4 - re: the extra round trips. I don't think I could have debugged my
problem if it were not for these extra round trips showing up in the trace.
If access cached metadata in a local disconnected record set I would have to
break and remake the connection. Unless of course Access exposed the local
meta data and gave me explicit control over refreshing this cache... and a
good programmers description of their front-end/back-end integration rules
(like what meta-data is used to make what decisions)... Theres an idea!

re: #8 Do you know if the ADP programmer get ahold of any connections that
access opens in addition to application.currentproject.accessconnection?
For instance, I'd like to "SET NOCOUNT ON" on each connection that gets
opened (you know why). Or maybe that is a bad idea....??

Needless to say, I AM engages in using ACC2002 in a non-prototype in-house
project. It embodies some of the meta-data driven client/server
interactions I've been musing about for some time, and I am able to layer on
more (i.e. auto-locking data-bound controls for which the current user has
been revoked update privs on underlying controlsource (per
INFORMATION_SCHEMA, which I agree, I would like to see MS programmers take
their own advice on this point). Unfortunately, It does not expose their
rules, and allowing declarative tweaks/hints such as "Unique Table", "Resync
Command", "Update using view rules", etc without a thorough documentation of
how they effect round-trip processing is courting mythologizing - but I
digress. Or am I missing the definitive tome on the subject....???

re:
Unfortunately, this has always been the behavior of both ADO and DAO. If you
have a clustered index, that's seen as the primary key. That's true even if
the clustered index is not unique!
Click to expand...

Can you point me to any MS branded documentation to this effect? Or other
good source for factoids like this.
So, my first reaction to this is to stop using non-primary clustered indexes
with ADPs.
Click to expand...


For now, I'm going with your first reaction.
My second reaction is that this is one more thing on the
ever-growing stack of reasons I don't like ADPs and probably won't ever use
them again for production work. They're great for prototyping.
Click to expand...

I would be much obliged for any updates you're willing to share to this
stack...

Here are some rules I'm learning to abide by that ADP development work for
me:

1) don't put unique indices on non-primary clustered indexes.
2) make sure you SET NOCOUNT ON in your triggers
3) if your triggers do inserts into other tables that update @@IDENTITY,
remember it at beginning of trigger and reset it back last thing before the
trigger terminates

You probably know all these already. Have you any more that would help a
neophyte ADPer?

Thanks so much for your reply, and I look forward to your further
considerations of these issues....

Sincerely,
 
Hi Malcolm,

Thanks for using MSDN Newsgroup!

From your descriptions, I understood that your Access ADP will not be able
to update the insert while it has done on server-side. You find when a
table had a clustered index, it would happen. Have I understood you? If
there is anything I misunderstood, please feel free to let me know:)

Based on my understanding, if a clustered index was used, you should set
the "unique table" property of the form, and after that it will be
updatable.

Would you please have a try on it and show me the result? If about could
not resolved your issue, would you please show me a sample? (With all DLL
in SQL Server and sample adp)

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
 
Back
Top