在第二种情况下使用mysql触发错误

Question

Hey guys i am new to mysql.I have did some code with mysql trigger but it throws me error on the second condition. The code

This one works fine

create table foo (a INT, b INT, ts TIMESTAMP);
create table bar (a INT, b INT);

INSERT INTO foo (a,b) VALUES(5,1);
INSERT INTO foo (a,b) VALUES(4,2);


INSERT INTO foo (a,b) VALUES(3,3);


CREATE TRIGGER ins_sum AFTER UPDATE ON foo

FOR EACH ROW
insert into bar values(OLD.a,NEW.b);



UPDATE foo SET a = 3 WHERE b = 1;

It succesfully outputs 5.

The error code

create table foo (a INT, b INT, ts TIMESTAMP);
create table bar (a INT, b INT);

INSERT INTO foo (a,b) VALUES(5,1);
INSERT INTO foo (a,b) VALUES(4,2);
INSERT INTO foo (a,b) VALUES(3,3);



CREATE TRIGGER ins_sum AFTER UPDATE ON foo
FOR EACH ROW
insert into bar values(OLD.a,NEW.b);
insert into bar values(OLD.a,NEW.b);

UPDATE foo SET a = 3 WHERE b = 1;

When i called like select a from bar where b = 1.The expected result is 5 and 4.

But instead of it .it outputs like Schema Creation Failed: Unknown column 'OLD.a' in 'field list':

So my question is why doesnt the second option work here ..Whay cant i add NEW.a and NEW.b in the insert statement second time.

Thanx for the help..

Answer 1

You need the DELIMITER command so that the semicolon doesn't end the trigger definition. You also need a BEGIN ... END block to put multiple commands into the FOR EACH ROW loop

DELIMITER //
CREATE TRIGGER ins_sum AFTER UPDATE ON foo
    FOR EACH ROW
    BEGIN
        insert into bar values(OLD.a,NEW.b);
        insert into bar values(OLD.a,NEW.b);
    END
//
DELIMITER ;

DEMO