You may some day run into this problem.
If you attach a trigger to a table in SQL server that updates columns on the affected rows, Access blows up because it thinks the update or insert failed. I don't know how to get around that one.
However, I thought it would theoretically be safe in a trigger to make inserts or updates to *other* tables that Access won't care about. I found out otherwise.
When making updates to a table that has a trigger which inserts to another table, Access will display the user edit conflict box asking if you want to drop changes, recommit them, or something else I don't remember. Trying to recommit them fails because you just run into the exact same problem as the first time. And what is the problem?
The problem is that after performing the insert or update, Access uses the @@IDENTITY function to determine the row affected, then queries the table updated to get the new values. But, @@IDENTITY will return the ID from the other unrelated table, and Access tries to apply this to the edited table.
So for example, let's say you are editing OrderID 321 (an identity column) of your Orders table. You have a trigger which inserts some history data into OrderHistory. Since you just created this trigger, the OrderHistory identity value of 1 gets returned to Access when it uses @@IDENTITY. Then it queries for OrderID 1, sees that the data doesn't match what it expected (for OrderID 321) and has a conniption fit.
Aside from some method to make an Access ADP use SCOPE_IDENTITY() like it should for SQL Server 2000 (it's stuck with @@IDENTITY for older versions), there's a little trick that can help work around this problem!
At the very end of your trigger, add the following code. This example assumes the trigger is used only for update operations.
Because the SELECT INTO syntax automatically makes columns in the new table have the properties of the source table, the new short-lived #Trash temp table column (randomly named 'A') is identity. Thus it seeds future evaluations of @@IDENTITY appropriately, making it return the same value SCOPE_IDENTITY() will.
NOCOUNT is set on because this operation should be transparent and spurious rowcount messages can confuse software (or people) sometimes.
Because @@IDENTITY can only return one value, I use Max() on the theory that this might be the right one, experiments are called for to be sure. Of course, for single-row operations it's irrelevant.
You may (completely theoretically) figure out the range of IDs that actually got inserted by: setting the transaction isolation level to lock the table exclusively and doing something to actually acquire the lock for the session, DBCCing the table properties to learn the next identity value, performing the update, unlocking the table, resetting the transaction isolation level to something more friendly, and then using SCOPE_IDENTITY() or @@IDENTITY(). Something simpler might involve @@ROWCOUNT instead of DBCC.
However, care should be taken with @@ROWCOUNT to ensure that you actually get the right number seeing as you're performing an extra operation. I'd have to experiment to learn if the Max() syntax could be counterproductive in certain situations.
Last, if your trigger only inserts into another table under particular conditions, or covers update or delete operations as well as insert, you may want to add an IF <CONDITION> or IF EXISTS (SELECT ...) clause to prevent doing unnecessary work all the time.
Here's an example of how, if the trigger was for all three data operations, if you wished you could restrict the spoofing to just the inserts:
You might be able also to just put the spoof code immediately after the earlier INSERT of your trigger and group it with the same conditions that statement falls under. But again, experimentation is needed as later SELECTs or UPDATEs could affect @@IDENTITY.
And now that I think about it, perhaps the spoofing might be necessary for updates and deletes as well...
I hope this helps someone.
Erik Eckhardt
Applications Designer and SQL Developer
If you attach a trigger to a table in SQL server that updates columns on the affected rows, Access blows up because it thinks the update or insert failed. I don't know how to get around that one.
However, I thought it would theoretically be safe in a trigger to make inserts or updates to *other* tables that Access won't care about. I found out otherwise.
When making updates to a table that has a trigger which inserts to another table, Access will display the user edit conflict box asking if you want to drop changes, recommit them, or something else I don't remember. Trying to recommit them fails because you just run into the exact same problem as the first time. And what is the problem?
The problem is that after performing the insert or update, Access uses the @@IDENTITY function to determine the row affected, then queries the table updated to get the new values. But, @@IDENTITY will return the ID from the other unrelated table, and Access tries to apply this to the edited table.
So for example, let's say you are editing OrderID 321 (an identity column) of your Orders table. You have a trigger which inserts some history data into OrderHistory. Since you just created this trigger, the OrderHistory identity value of 1 gets returned to Access when it uses @@IDENTITY. Then it queries for OrderID 1, sees that the data doesn't match what it expected (for OrderID 321) and has a conniption fit.
Aside from some method to make an Access ADP use SCOPE_IDENTITY() like it should for SQL Server 2000 (it's stuck with @@IDENTITY for older versions), there's a little trick that can help work around this problem!
At the very end of your trigger, add the following code. This example assumes the trigger is used only for update operations.
Code:
SET NOCOUNT ON
SELECT A = Max(IDCol) INTO #Trash FROM InsertedNOCOUNT is set on because this operation should be transparent and spurious rowcount messages can confuse software (or people) sometimes.
Because @@IDENTITY can only return one value, I use Max() on the theory that this might be the right one, experiments are called for to be sure. Of course, for single-row operations it's irrelevant.
You may (completely theoretically) figure out the range of IDs that actually got inserted by: setting the transaction isolation level to lock the table exclusively and doing something to actually acquire the lock for the session, DBCCing the table properties to learn the next identity value, performing the update, unlocking the table, resetting the transaction isolation level to something more friendly, and then using SCOPE_IDENTITY() or @@IDENTITY(). Something simpler might involve @@ROWCOUNT instead of DBCC.
However, care should be taken with @@ROWCOUNT to ensure that you actually get the right number seeing as you're performing an extra operation. I'd have to experiment to learn if the Max() syntax could be counterproductive in certain situations.
Last, if your trigger only inserts into another table under particular conditions, or covers update or delete operations as well as insert, you may want to add an IF <CONDITION> or IF EXISTS (SELECT ...) clause to prevent doing unnecessary work all the time.
Here's an example of how, if the trigger was for all three data operations, if you wished you could restrict the spoofing to just the inserts:
Code:
IF EXISTS (SELECT 1 FROM Inserted I LEFT JOIN Deleted D ON I.IDCol = D.IDCol WHERE D.IDCol IS NULL) SELECT ...And now that I think about it, perhaps the spoofing might be necessary for updates and deletes as well...
I hope this helps someone.
Erik Eckhardt
Applications Designer and SQL Developer