Mysql Trigger error

Question

1.I m using mysql 5.2.2 version

2.I create table in my test database

CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

3.I also Create trigger

CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;

4.I insert data into account table

INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);

5.After then when i use following command for show data

SELECT @sum AS 'Total amount inserted';

6.I can't find data what is problem for that or any mistake in this code?

Total amount inserted
 NULL

Can anyone help?

Answer 1

You need to initialize @sum prior to inserting values. For example:

SET @sum := 0;
INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+