This answer says that "you should never call stored procedures from within triggers", however, it seems to be assuming some circumstances and needs that might not be applicable to every database. What if I don't need to roll data back? What if my data set is fairly small?
Specifically, I'm building a database and application, part of which will deal with inventory management, and I've made a stored procedure as follows:
drop procedure if exists quantityupdate;
DELIMITER //
create PROCEDURE quantityupdate()
begin
DECLARE idcounter INT;
DECLARE newq FLOAT;
DECLARE partcount FLOAT;
DECLARE used FLOAT;
DECLARE added FLOAT;
set idcounter = 1;
set partcount = (SELECT COUNT(*) FROM parts);
while idcounter < partcount DO
SET used = (SELECT sum(Quantity) FROM `partusage` WHERE partID = idcounter AND SoftDelete = 0);
SET added = (SELECT sum(Quantity) FROM `purchases` WHERE partID = idcounter AND SoftDelete = 0);
SET newq = added - used;
UPDATE `parts` SET `QuantityOnHand` = if (newq IS NOT NULL, (SELECT newq),QuantityOnHand) WHERE partID = idcounter;
set idcounter = idcounter + 1;
end while;
end //
delimiter ;
I'm considering calling this stored procedure whenever a record is INSERTed into the purchases and partusage tables, which will happen only 50 times per day or so. Is there a reason that I shouldn't call that from a trigger like that?
