Mysql – How to get currently inserted rows and sum them within Mysql trigger before insert

bulk-insertcursorsMySQLtrigger

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.

Best Answer

BEFORE inserting, use OLD.

Is this building something like a "Summary table" for a Data Warehousing application? If so, consider doing:

INSERT INTO tmp VALUES ...;
INSERT INTO summary_table  SELECT SUM(...),... FROM tmp GROUP BY ...;
INSERT INTO real SELECT * FROM tmp;

There are many variants on that flow. But note that you get a chance to transform data, do multiple inserts, etc. And, quite probably the GROUP BY will be faster than doing one the rows one at a time, as you are doing with the TRIGGER.

Related Question