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?

Mike · Accepted Answer · 2010-08-20 11:10:34Z

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 |
+-----------------------+

answered Aug 20, 2010 at 11:10

Mike

21.7k2 gold badges48 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql Trigger error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related