Search code examples
mysqlvariablestriggersdeclare

Declaring variables while creating a trigger in MySql

I'm having a little problem creating a trigger, because I have to declare a few variables, and then set some of them to 0, so they will start increasing their value in a loop, depending on conditions. Anyway, I'm gonna paste the code, and the error that it throws, and hopefully someone will tell me what I'm doing wrong. Thanks.

CREATE TRIGGER update_request AFTER UPDATE ON medical_tests
FOR EACH ROW
BEGIN
DECLARE y INT, x0 INT, x3 INT, counting INT
SET y=0, x0=0, x3=0,
counting = count(SELECT * FROM medical_tests WHERE medical_tests.request_id = UPDATED.request_id)

WHILE (y<counting)
BEGIN
SET y=y+1
IF (medical_tests.status=1)
BEGIN
SET x0=x0+1
END
ELSEIF (medical_tests.status=3)
BEGIN
SET x3=x3+1
END
END IF
END WHILE

IF (x0>0 AND x0<counting)
BEGIN
UPDATE requests SET status=2 WHERE requests.id=UPDATED.request_id
END
ELSEIF (x3=counting)
BEGIN
UPDATE requests SET status=3 WHERE requests.id=UPDATED.request_id
END
ENDIF
END

It throws the following 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 ' x0 INT, x3 INT, counting INT SET y=0, x0=0, x3=0, counting= count(SELECT * FROM m' at line 3.

Last time i wrote a trigger I did it in MySQL Server and the syntax is a little different. Maybe I'm just going to need to create a procedure and call it instead. SO if anyone can help, i'd appreciate it.

Solution

  • First things first. To properly declare variables of the same type and the same initial value in MySQL change

    DECLARE y INT, x0 INT, x3 INT, counting INT

    to

    DECLARE y, x0, x3, counting INT DEFAULT 0;

    This declares four variables of type INT with default value of 0.

    But that is just the beginning. Your code have several other problems:

    1. You have to terminate every statement with a semicolon ; including IF ... END IF;, WHILE ... END WHILE; etc.
    2. To refer to columns of a row being updated you have to use NEW keyword instead of UPDATED.
    3. You don't need to wrap every SET statement in BEGIN...END block. It just adds clutter.
    4. You can't just arbitrarily access a column value in your while loop with (medical_tests.status=1). If you meant to refer to status column of a row being updated then again you need to use a special keyword NEW or OLD. If you meant to traverse some other resultset then you should use a cursor or do it in a SELECT statement.