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

Question

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.

Answer 1

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.

Answer 2

Error Code: 1048 - Column 'total' cannot be null

If the above error is the one that shows up to you, alter the column and set the column total from table2 to nullable.

Answer 3

FOR EACH ROW enables you to handle multiple row inserts, so you're good with your trigger logic.

The problem is that vSum is not initialized to a start value. You're trying: vSum = NULL + New.price, hence the error. You want to initialize vSum after declaration:

Set vSum = 0;

This however will not accumulate the prices, it will reset sum to 0 for every row that's inserted, so you have to work on code logic that enables that. Cheers