CODEWITHSUNDEEP
mysqlsqlsyntaxtriggersdeclare
Haider Ali
Haider Ali

Reputation: 964

IF Statement firing error in mysql trigger

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.

Upvotes: 0

Views: 2964

Answers (2)

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

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.

Upvotes: 1

Shadow
Shadow

Reputation: 34232

The warnings around the IFs are just phpmyadmin's own check. The syntax error you get is caused by the NEW keyword, which is not needed. As per MySQL manual on creating triggers:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

However, your IFs are not closed by END IF, and you are also missing semicolons at the end of the declare statements.

Upvotes: 1

Related Questions