Key column information is insufficient ....II

[Leonardo Almeida]

This is the case.... I would like to learn the statement that make the
relation between these tables.
Why? Cos these are separated in two different databases and if a user
make an update in a table from database X these changes must to be
applied in the other table in the another database:

The tables are :

Principal Database Name : Server Information 2004
Table Name : Clients
Fields : ID_Client, Client

Secondary Database Name : Index2003
Table Name : Contratos
Fields : ID_Con, ID_Client, Client

I need to write a Trigger for Update the table Contratos everytime a
user change the values in Clients.

I´m using the follow Trigger :

CREATE TRIGGER UPDate_Clients ON dbo.Clients
FOR UPDATE
AS
update Contratos
set Client = inserted.Client
from Clients
inner join inserted on Clients.Client = inserted.Client

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were
affected by update."

If somebody can help me THANKS A LOT OF....

Leonardo Almeida



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Re: Key column information is insufficient ....II


"Leonardo Almeida" <leonardoalmeid a2004@yahoo.com .br> wrote in message
news:3f674b6d$0 $62079$75868355 @news.frii.net. ..[color=blue]
>
>
> This is the case.... I would like to learn the statement that make the
> relation between these tables.
> Why? Cos these are separated in two different databases and if a user
> make an update in a table from database X these changes must to be
> applied in the other table in the another database:
>
> The tables are :
>
> Principal Database Name : Server Information 2004
> Table Name : Clients
> Fields : ID_Client, Client
>
> Secondary Database Name : Index2003
> Table Name : Contratos
> Fields : ID_Con, ID_Client, Client
>
> I need to write a Trigger for Update the table Contratos everytime a
> user change the values in Clients.
>
> I´m using the follow Trigger :
>
> CREATE TRIGGER UPDate_Clients ON dbo.Clients
> FOR UPDATE
> AS
> update Contratos
> set Client = inserted.Client
> from Clients
> inner join inserted on Clients.Client = inserted.Client
>
> When I update the register the follow message in the application raise :
>
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."
>
> If somebody can help me THANKS A LOT OF....
>
> Leonardo Almeida
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

See my reply to your previous post.

Simon

[Erland Sommarskog]

Re: Key column information is insufficient ....II

Leonardo Almeida (leonardoalmeid a2004@yahoo.com .br) writes:[color=blue]
> I need to write a Trigger for Update the table Contratos everytime a
> user change the values in Clients.
>
> I´m using the follow Trigger :
>
> CREATE TRIGGER UPDate_Clients ON dbo.Clients
> FOR UPDATE
> AS
> update Contratos
> set Client = inserted.Client
> from Clients
> inner join inserted on Clients.Client = inserted.Client
>
> When I update the register the follow message in the application raise :
>
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."[/color]

Include a SET NOCOUNT ON first in the trigger. If that does not help,
remove the trigger and run the update again. I would expect in such
case that you get the error anyway. Which would indicate that the error
is in the client code which you did not show us.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Leonardo Almeida]

Re: Key column information is insufficient ....II

What code are you talking about?

In the client code I use Delphi + ADO

ADOTable1.Open;
ADOTable1.Edit;

Now edit the Client registrer

Post the register with the command:

ADOTable1.Post;

The message arise again....
and each table has a primary key...



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: Key column information is insufficient ....II

Leonardo Almeida (leonardoalmeid a2004@yahoo.com .br) writes:[color=blue]
> What code are you talking about?[/color]

The SET NOCOUNT ON command should be added to your trigger.
[color=blue]
> In the client code I use Delphi + ADO
>
> ADOTable1.Open;
> ADOTable1.Edit;
>
> Now edit the Client registrer
>
> Post the register with the command:
>
> ADOTable1.Post;
>
> The message arise again....
> and each table has a primary key...[/color]

There is no .Post method in ADO, so I conclude that this is something
Delphi-specific, and I don't know Delphi.

If SET NOCOUNT ON did not help, I can only suggest to use the Profiler
to see what is going on behind the covers.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Shervin Shapourian]

Re: Key column information is insufficient ....II

Hi Leonardo,

Your trigger doesn't look correct to me! Why are you using Clients
table in your trigger? In order to update Contratos table using new
values in Clients, you need to use inserted table not Clients.
Something like this:


create trigger Update_Clients on Clients
for update
as
update Contratos
set Client = inserted.Client
from Contratos join inserted
on Contratos.ID_Cl ient = inserted.ID_Cli ent


In your current trigger, you are dealing with 3 tables (Contratos,
Clients & inserted) without joining them correctly. So when you try to
update Client field of Contratos table, it finds more than one value
in inserted table which causes that problem.
I hope this one works fine. I didn't test it...

Good Luck,
Shervin



Leonardo Almeida <leonardoalmeid a2004@yahoo.com .br> wrote in message news:<3f674b6d$ 0$62079$7586835 5@news.frii.net >...[color=blue]
> This is the case.... I would like to learn the statement that make the
> relation between these tables.
> Why? Cos these are separated in two different databases and if a user
> make an update in a table from database X these changes must to be
> applied in the other table in the another database:
>
> The tables are :
>
> Principal Database Name : Server Information 2004
> Table Name : Clients
> Fields : ID_Client, Client
>
> Secondary Database Name : Index2003
> Table Name : Contratos
> Fields : ID_Con, ID_Client, Client
>
> I need to write a Trigger for Update the table Contratos everytime a
> user change the values in Clients.
>
> I´m using the follow Trigger :
>
> CREATE TRIGGER UPDate_Clients ON dbo.Clients
> FOR UPDATE
> AS
> update Contratos
> set Client = inserted.Client
> from Clients
> inner join inserted on Clients.Client = inserted.Client
>
> When I update the register the follow message in the application raise :
>
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."
>
> If somebody can help me THANKS A LOT OF....
>
> Leonardo Almeida
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]