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?
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
