MySQL trigger: error when checking if variable exists

Question

I have a trigger which references a global variable that's coming from a delete query which sets the trigger off:

DELIMITER ;;

CREATE TRIGGER test_trigger BEFORE DELETE ON test_table FOR EACH ROW 
BEGIN

    IF (SELECT @userID IS NULL) 
    THEN @userID := 0
    END IF;

END
;;

DELIMITER ;

For some reason I'm getting SQL error:

SQL 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 '@userID := 0

I don't understand this, it's a very basic IF statement, why doesn't it work?

UPDATE: I'm totally confused, I've tried this

IF TRUE
THEN TRUE
END IF;

and it still throws an error... Seriously?

UPDATE 2: SOLVED This actually works, however it is very weird

IF (SELECT @userID IS NULL) 
THEN SET userID = 0;
END IF;

Apparently you need a semicolon inside IF statement?

Madhav · Accepted Answer · 2013-02-07 11:17:23Z

1

CREATE TRIGGER test_trigger BEFORE DELETE ON test_table FOR EACH ROW 
BEGIN
  IF (SELECT @userID IS NULL) 
  THEN 
    SET @userID = 0;
END IF;
END;

edited Feb 7, 2013 at 11:17

answered Feb 7, 2013 at 11:12

Madhav

2,4153 gold badges19 silver badges16 bronze badges

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

1 Comment

Caballero Over a year ago

Actually it still doesn't work, but if I put semicolon after 0 it does work. Very weird.

Collectives™ on Stack Overflow

MySQL trigger: error when checking if variable exists

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related