mysql insert trigger error

Question

I just moved to phymysql and i'm having problems in creating of a trigger. Below is my script

CREATE TRIGGER trgAutoInsert AFTER INSERT ON profile
FOR EACH ROW
BEGIN

    DECLARE var1 INT;
    DECLARE var2 INT;
    DECLARE var3 CHAR(100);
    DECLARE var4 CHAR(100);
    DECLARE var5 CHAR(3);

    SELECT      profile_id,
                profile_id,
                fname,
                fname,
                fpage
        FROM    profile
        INTO    var1,
                var2,
                var3,
                var4,
                var5;

    IF  var5 = 'yes'
    THEN

        INSERT INTO fiends
                (req_id, resp_id, req_name, resp_name, fpage)
            VALUES
                (var1, var2, var3, var4, 'yes');

    END IF;

END

When i execute i get this error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5 

Any help will be my appreciated.

Answer 1

The typical way to do this in MySQL uses :=:

SELECT var1 := profile_id,
       var2 := profile_id,
       var3 := fname,
       var4 := fname,
       var5 := fpage
FROM profile;

In my opinion, the into clause is there to be similar to Oracle.

That said, two things to note:

1. This seems to assume that profile has only one row. Normally there would be a where clause.
2. You should name your variables with a prefix (say v_) so they are not confused with columns.

However, I would write this without the variables:

DELIMITER $$
CREATE TRIGGER trgAutoInsert AFTER INSERT ON profile
FOR EACH ROW
BEGIN
    INSERT INTO fiends(req_id, resp_id, req_name, resp_name, fpage)
        SELECT profile_id, profile_id, fname, fname
        FROM profile p
        WHERE fpage = 'yes';
END$$
DELIMITER ;

This seems much simpler. I am guessing that yes should be a string.

EDIT:

If you want the rows just inserted, use new:

DELIMITER $$
CREATE TRIGGER trgAutoInsert AFTER INSERT ON profile
FOR EACH ROW
BEGIN
    INSERT INTO fiends(req_id, resp_id, req_name, resp_name, fpage)
        SELECT new.profile_id, new.profile_id, new.fname, new.fname
        FROM dual
        WHERE new.fpage = 'yes';
END$$
DELIMITER ;