I have a mysql trigger that fires BEFORE INSERT. My php application is inserting multiple rows at once like so
INSERT INTO table1(id,name,price) VALUES(1,'LH32400', 1990),(2,LH32401,2000);
The idea is that before the data is inserted i want to sum all of the prices, and insert them into another table. I know after insert would work, but the rest of my logic requires this to be a before insert trigger.
The main problem is that within the trigger body New.price returns null (or i think it does, because the trigger throws an error: total cannot be null in my final insert)
If i try
CREATE TRIGGER triggerName BEFORE INSERT ON table1 FOR EACH ROW BEGIN DECLARE vSum DOUBLE(10,2); SET vSum = vSum + NEW.price; // because of FOR EACH ROW i thought that this would happen multiple times INSERT INTO table2 ( id, date, total, status ) VALUES ( 1, SYSDATE(), vSum, 'closed' ); END
If on the other hand i try to use a cursor i get stuck
DECLARE cur1 CURSOR FOR SELECT id,name, price FROM ? (i obviously cant use table1 since this is a before insert trigger and no data has been actually inserted into table1)
There is probably a very simple answer to this but my brain is pretty down with concentration and i desperately need help. Any suggestion will be appreciated, thanks.