Trigger help please

[chobo321321]

I'm trying to create a trigger that be able to update the UnitsOnHand (field) in the tblproduct table when an order is inserted, modified, or deleted in the tblOrder table. If an order is added it should take away from unitsonhand. If an order is modified it should add or take away depending on the adjustment to Qty in the order table. If the order is deleted then the quantity should be returned to UnitsOnHand.

I blow at triggers, and can't figure out to compare the qty in tblOrder table to the quantity in the insert or delete table, so I can figure out whether to add or subtract from the field. Any help is appreciated.

CREATE TRIGGER UnitStock
ON tblOrder
AFTER INSERT, UPDATE, DELETE  AS

UPDATE tblProducts
SET UnitsOnHand = UnitsOnHand - qty
FROM inserted
WHERE tblOrder.ProductID = inserted.ProductID

 

[RoyVidar]

Access, to my knowledge, doesn't support triggers. The closest Access gets, I believe, is form events. If you're using something else as backend, I think your enquiry might be better answered if posting in a forum related to that technology.

The "quantity on hand" challenge, is also a bit troublesome. Storing calculated values and such, one would often be better off calculating the quantity on hand on the fly each time it is needed. See for instance this article by Allen Browne
http://allenbrowne.com/AppInventory.html