See "Doesn't satisfy criteria" error on Access update of updateable view with INSTEAD OF trigger Options.
I recnetly got one step closer to making an Access 2003 ADP work well with an INSTEAD_OF trigger.
I have a supertype/subtype relationship This is a 1 to 0-or-1 relationship for the supertype to subtype relationship. (Effectively, there is a 1-1 relationship among all subtypes along the lines of supertype
subtype1 union all subtype 2 union all subtype3 ...). I want inserts to each subtype table to automatically perform an insert to the supertype table, and be given the identity values from the supertype. I got everything working just fine, as long as one provides dummy values for the primary key in the subtype table, as said the above link.
Here's what I did to allow this to work with Access, along with using the WITH VIEW_METADATA keywords:
I made the pk column of the subtype table an identity and in the instead of trigger used SET IDENTITY_INSERT ON/OFF for inserts to the table with the values discovered after insert to the main table. Now that SQL server sees that the column is an identity column, it allows it to be left out of the insert statement, and Access knows it must look up @@Identity to get the value after the insert. There is no conflict between @@identity and scope_identity() as the identity value is the same for both the subtype and supertype tables (and even if it were not we could spoof it in our trigger by inserting to a temp table as the final step in our trigger).
The only final yucky part is that I can no longer add ON DELETE CASCADE to the subtype table, to allow the full range of inter-behaviors I want between the supertype and subtype table, as "Cascading foreign key cannot be created where the referencing column is an identity column." This can be addressed with a delete trigger, with some care taken between the subtype and supertype column to avoid unnecessary bounces back and forth.
Erik
[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.
[sub]The best part about anything that has cheese is the cheese.[/sub][/color]
I recnetly got one step closer to making an Access 2003 ADP work well with an INSTEAD_OF trigger.
I have a supertype/subtype relationship This is a 1 to 0-or-1 relationship for the supertype to subtype relationship. (Effectively, there is a 1-1 relationship among all subtypes along the lines of supertype
subtype1 union all subtype 2 union all subtype3 ...). I want inserts to each subtype table to automatically perform an insert to the supertype table, and be given the identity values from the supertype. I got everything working just fine, as long as one provides dummy values for the primary key in the subtype table, as said the above link.Here's what I did to allow this to work with Access, along with using the WITH VIEW_METADATA keywords:
I made the pk column of the subtype table an identity and in the instead of trigger used SET IDENTITY_INSERT ON/OFF for inserts to the table with the values discovered after insert to the main table. Now that SQL server sees that the column is an identity column, it allows it to be left out of the insert statement, and Access knows it must look up @@Identity to get the value after the insert. There is no conflict between @@identity and scope_identity() as the identity value is the same for both the subtype and supertype tables (and even if it were not we could spoof it in our trigger by inserting to a temp table as the final step in our trigger).
The only final yucky part is that I can no longer add ON DELETE CASCADE to the subtype table, to allow the full range of inter-behaviors I want between the supertype and subtype table, as "Cascading foreign key cannot be created where the referencing column is an identity column." This can be addressed with a delete trigger, with some care taken between the subtype and supertype column to avoid unnecessary bounces back and forth.
Erik
[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.
[sub]The best part about anything that has cheese is the cheese.[/sub][/color]