0

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.

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.

0
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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.