Update trigger works in SQL Server but NOT in Access adp datasheet

  • Thread starter Thread starter Bonno Hylkema
  • Start date Start date
B

Bonno Hylkema

Dear friends,

I have a SQL Server database and an Access adp frontend. I use DRI
(declarative referential integrity) throughout, with one exception. I have a
table Projects in which I save the names of several people working on the
project, like analist, projectleader etc, a total of four names. These names
are taken from one table Employees. Since I want to have referential
integrity on four fields, DRI wil not help (only one allowed), so I am using
triggers instead.

I wrote the following trigger as an example, changing a FunctionCode in the
table Functions, and hoping that the table Employees will be updated
accordingly:

CREATE TRIGGER updFunctionCode
ON Functions
FOR UPDATE
AS
IF EXISTS (SELECT * from Employees, deleted WHERE Employees.FunctionCode
= deleted.FunctionCode)
UPDATE Employees
SET FunctionCode = inserted.FunctionCode
FROM inserted, deleted
WHERE Employees.FunctionCode = deleted.FunctionCode

When I am in the SQL Server environment (Query Analyzer) the trigger works
like a charm.

However when I am in the Access adp environment and I update a FunctionCode
in the table Functions I get the following Microsoft Office Access error:

"There are insufficient key columns data, or they are not correct. Too many
rows are affected by the update"

This error message is not the litteral english text, I had to translate it
from dutch. So if you don't understand it, I don't either.

The row is not updated in the Access datasheet of the table Functions, the
update mark stays on, after I hit the Escape key, the old value is still
shown. However after a requery of the datasheet the new value is shown and
the update was also succesful in the related Employees table!

It looks like the trigger works OK, but the update of the Access adp
frontend is fooled.

Has anybody any clue?

Thanking you in advance, Bonno Hylkema
 
Bonno,

Put a SET NOCOUNT ON as the first thing you do in the trigger and see if that helps... I bet it will.

If not, then tell us, is there an update trigger on employees table, and if so, what does it do?

BTW - I think the logic in your trigger will not work correctly if more than one row in your Functions table is being updated at a
time. You need a self-join between inserted and deleted to handle multi-row updates to 'Functions'... otherwise your Employees
will no be updated correctly. Is there a surrogate key you can join on?

Cheers,
 
Remember me of many problems similar to this that I have in the past with
ADP 2000 & 2002. I never tested but I won't be surprised if they are the
same with ADP 2003: instead of using a third table to store your N-N
relationship, you have took advantage of the fact that you have a maximum of
four names to store directly the ID of the table Employees into the table
Projects. By doing so, each record of the table Project references more
then one time the table Employees.

A sweet design on paper but a killer with ADP. Three years ago, I attempted
many things to circumvent this problem under ADP but the only solution that
I have found was to change my design to remove this kind of multiple
references to the same table inside one record.

By taking a look with the SQL-Profiler, probably that you will see some
invalid update commands sent by ADP to the SQL-Server and these invalid
commands will explains the error messages that you are getting.

My tests was with ADP 2000 and 2002 some years ago, however I'm pretty sure
that these problems have never been corrected and are still around even with
ADP 2003 and the latest service pack.
 
Dear Malcolm,

Your bet was absolutely right. SET NOCOUNT ON did the job. The Access error
was on the row count row returned from SQL Server. It was that easy!

FunctionCode is the primary key on table Functions. So I can't see how users
could update more than one row at a time. If so, how should the code look
like when more than one row could be updated at a time?

Thank you many times for your tip. It made my day!

Regards, Bonno Hylkema
 
Bonno,
Your bet was absolutely right. SET NOCOUNT ON did the job. The Access error was on the row count row returned from SQL Server. It
was that easy!
Click to expand...

glad to help with the trigger....
FunctionCode is the primary key on table Functions. So I can't see how users could update more than one row at a time. If so, how
should the code look like when more than one row could be updated at a time?
Click to expand...

suppose you have only two function codes: FC1, FC2

and suppose someone issued

UPDATE Functions set FunctionCode = FunctionCode & "_xxx" (expecting the change all the function codes to now have an extension of
"_xxx", i.e. "FC1_xxx" and "FC2_xxx"

your lack of a join between inserted and deleted will result in your trigger running for all combinations, including where
insert.FunctionCode = FC1_xxx and deleted.FunctionCode = "FC2", which is not what you want

You have a problem. You are allowing updates to the primary key to Function... and there is no way to join the inserted and deleted
(pseudo) tables (unless there is some other field which is unique).

So, I suggest you either

1) put logic in your trigger: if 1 < select count(*) from inserted then RAISERROR "only one FunctionCode can be updated at a time"
(even though your app doens't create such update statements - it is better to be safe)
or
2) don't allow changing functioncodes
3) make FunctionCode be an alternate unique key after adding a surrogate IDENTITY primary key and add to your query 'AND
inserted.FunctionID = delete.FunctionID'
4) explain what you mean by "DRI wil not help (only one allowed)," - maybe there is another way to refactor your model to use DRI
after all...

--Malcolm Cook
 
Malcolm,

Thank you for explaining a possible problem in the trigger I presented. I
will study your solution and see whether it applies to my application.

Your question regarding DRI will not help (only one allowed) is about the
following:

I have a table Projects in which I save the names of several people working
on the project, like analist, projectleader etc, a total of four names.
These names are taken from one table Employees.

When I try to apply one relationship with cascaded update between these
tables, it's okay. But when I try to apply a second relationship with
cascaded update I get the following error: "Introducing FOREIGN KEY
constraint 'FK_Projects_Employees1' on table 'Projects' may cause cycles or
multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION,
or modify other FOREIGN KEY constraints."

This relationship will not cause a cycle, but it does cause a multiple
cascade path that seems NOT to be allowed in SQL Server 2000.

When I google the error I come across the following link:
http://www.mcse.ms/archive93-2004-12-1315623.html
It explains that you cannot have multiple cascade paths and that you have to
use triggers. So that is what I am trying to do.

If you know a more simple solution than using triggers, I would be grateful.
Although an On update trigger is not that difficult after all ( as long as
you use SET NOCOUNT ON)!

Hope to hear from you,

Bonno Hylkema
 
Back
Top