Can some one help with this situation. I am using the sample northwind Database with and inventory control data tables, purchase order and inventory transaction. I need to update the unitsOnOrder in the products table when insert occurs in the inventory transaction table.
I created this trigger:

CREATE TRIGGER "bcc.Trigger_UpdateProducts" ON InventoryTransactions
FOR INSERT
Not for replication
AS
UPDATE
dbo.products
SET
UnitsOnOrder=dbo.InventoryTransactions.UnitsOrdere d
FROM
inserted,dbo.InventoryTransactions
where dbo.products.productID=dbo.InventoryTransactions.p roductID

but I get this error "key column information is insufficient or incorrect. Too many rows were affect by the update"

Can anyone help me correct the trigger to avoid the error.
Pleas make suggestion or recommendation on any part of this request.

__________________
Jaime E. Maccou

keep looking on how you are using the inserted table
you must precede every column with the table have if two tables have the same column name


Ahmed Ali
Software Developer

I may not be clear on what you are saying, Please correct if I amd wrong.

CREATE TRIGGER "bcc.Trigger_UpdateProducts" ON InventoryTransactions
FOR INSERT
Not for replication
AS
UPDATE
dbo.products
SET
dbo.products.UnitsOnOrder=dbo.InventoryTransaction s.UnitsOrdered
FROM
dbo.InventoryTransactions, inserted
where dbo.products.productID=dbo.InventoryTransactions.p roductID

Please look your code again, as I see it there, You have used the inserted table only in the FROM clause. But you dont use the same in the WHERE clause, without which the query is meaningless. Going by the code you posted, it would update all the records in PRODUCT table, every time an INSERT event occurs on InventoryTransactions Table. Thats why you get too many rows updated as you said.

If I am not wrong, it should be like this. Every time a record is inserted into InventoryTransactions, the same records ProductId is used as a look up value in Products table and its UnitsOnOrder should get updated. If that is so, then your Update statement should be as follows.

CREATE TRIGGER "bcc.Trigger_UpdateProducts" ON InventoryTransactions
FOR INSERT
Not for replication
AS
UPDATE dbo.products SET
dbo.products.UnitsOnOrder=Inserted.UnitsOrdered
FROM dbo.InventoryTransactions, inserted
where dbo.products.productID=Inserted.productID

Coz, Inserted table is a replica of the record that you are trying to insert.

Also, I noticed another logical error. Everytime the Record is inserted, should the PRODUCTS.UnitsOrdered be incremented? If so, it should look like

UPDATE dbo.products SET
dbo.products.UnitsOnOrder=dbo.products.UnitsOnOrde r + Inserted.UnitsOrdered
FROM dbo.InventoryTransactions, inserted
where dbo.products.productID=Inserted.productID

Hope that helps.

Cheers,

-Vijay G

I forgot to remove the "dbo.InventoryTransactions" from the FROM clause in both the occasion in my previous post, as it is no more required there, since we dont use that table for any kidn of reference. Please do so at you end and let me know if that works.

Cheers,

-Vijay G

Thank you so much for opening my eyes. IT worked.