Problem with UPDATE trigger code in MySQL5

[mbabcock]

MySQL5 doesn't like my UPDATE trigger:

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `tiremate`.`sp_updatetrans`$$

CREATE TRIGGER `tiremate`.`sp_updatetrans` AFTER UPDATE on
`tiremate`.`inventory_transactions`
FOR EACH ROW BEGIN
update inventory
set iqtyinstock = (select sum(nqty)
from inventory_transactions
where iinventoryid = ROW.iid);
END$$

DELIMITER ;


I had to change ROW reference above to inventory to get it to work, and
while it does work, I don't want the UPDATE SQL to run for EVERY row in
the update, hence my wanting to just update the specific row affected.
Why? For performance reasons...just want to update the specific row in
question. The trigger compiles just fine with no errors, but when I
update a row in the nQty field in inventory_transactions table, I get
the following:

---------------------------
SQLyog Community Edition- MySQL GUI
---------------------------
Error No. 1054
Unknown column 'ROW.iid' in 'where clause'
---------------------------
OK Help
---------------------------


Can anyone see the problem with my syntax or execution plan?

Thanks in advance!
--Michael

 

[mbabcock]

Nevermind...I needed to use the OLD and NEW reference keywords....doh!