IF Statement firing error in mysql trigger

Question

I'm executing this statement to create a trigger on a table using phpMyAdmin

DELIMITER //
CREATE NEW TRIGGER credit_refs AFTER UPDATE on investments FOR EACH ROW 
BEGIN 
    DECLARE ref INT(20)
    DECLARE parent INT(20)
    DECLARE suparent INT(20)
    SELECT ref,ref_pa,ref_supa INTO ref,parent,suparent FROM users WHERE tid = OLD.tid;
        IF(ref != 0) THEN
        INSERT INTO earnings (`tid`,`amount`,`type`,`ref_tid`) VALUES           (ref,OLD.inv,'ref',OLD.tid);
    IF(parent != 0) THEN 
        INSERT INTO earnings (`tid`,`amount`,`type`,`ref_tid`) VALUES               (parent,OLD.inv,'ref_pa',OLD.tid);
    IF(suparent != 0) THEN 
        INSERT INTO earnings (`tid`,`amount`,`type`,`ref_tid`) VALUES                        (suparent,OLD.inv,'ref_supa',OLD.tid);
END //
DELIMITER ;

The statement looks alright to me, however i am greeted with the following

errors

Static analysis:

4 errors were found during analysis.

Unrecognized statement type. (near "IF" at position 253)

Unrecognized statement type. (near "IF" at position 372)

Unrecognized statement type. (near "IF" at position 505)

Unrecognized statement type. (near "END" at position 643)

SQL query: Documentation

CREATE NEW TRIGGER credit_refs AFTER UPDATE on investments FOR EACH ROW BEGIN DECLARE ref INT(20) DECLARE parent INT(20) DECLARE suparent INT(20) SELECT ref,ref_pa,ref_supa INTO ref,parent,suparent FROM users WHERE tid = OLD.tid; IF(ref != 0) THEN INSERT INTO earnings (`tid`,`amount`,`type`,`ref_tid`) VALUES (ref,OLD.inv,'ref',OLD.tid); IF(parent != 0) THEN INSERT INTO earnings (`tid`,`amount`,`type`,`ref_tid`) VALUES (parent,OLD.inv,'ref_pa',OLD.tid); IF(suparent != 0) THEN INSERT INTO earnings (`tid`,`amount`,`type`,`ref_tid`) VALUES (suparent,OLD.inv,'ref_supa',OLD.tid); END

MySQL said: Documentation

#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 'NEW TRIGGER credit_refs AFTER UPDATE on investments FOR EACH ROW

 BEGIN
        DECL' at line 1

Hopefully someone here will be able to show me what im doing wrong here.

Answer 1

DELIMITER $$
 CREATE TRIGGER credit_refs AFTER UPDATE ON investments
 FOR EACH ROW 
BEGIN
    DECLARE ref INT(20);
    DECLARE parent INT(20);
    DECLARE suparent INT(20);
    SELECT ref,ref_pa,ref_supa INTO ref,parent,suparent FROM users WHERE tid = OLD.tid;
        IF(ref != 0) THEN
        INSERT INTO earnings (`tid`,`amount`,`type`,`ref_tid`) VALUES           (ref,OLD.inv,'ref',OLD.tid);
        END IF;
    IF(parent != 0) THEN 
        INSERT INTO earnings (`tid`,`amount`,`type`,`ref_tid`) VALUES               (parent,OLD.inv,'ref_pa',OLD.tid);
        END IF;
    IF(suparent != 0) THEN 
        INSERT INTO earnings (`tid`,`amount`,`type`,`ref_tid`) VALUES                        (suparent,OLD.inv,'ref_supa',OLD.tid);
        END IF;
END;
 $$
DELIMITER ;

Try above query.

In MySQL every IF statement must have END IF.You missed END IF for multiple if statement.