Declaring variables while creating a trigger in MySql
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:
- You have to terminate every statement with a semicolon
;
includingIF ... END IF;
,WHILE ... END WHILE;
etc. - To refer to columns of a row being updated you have to use
NEW
keyword instead ofUPDATED
. - You don't need to wrap every
SET
statement inBEGIN...END
block. It just adds clutter. - You can't just arbitrarily access a column value in your while loop with
(medical_tests.status=1)
. If you meant to refer tostatus
column of a row being updated then again you need to use a special keywordNEW
orOLD
. If you meant to traverse some other resultset then you should use a cursor or do it in aSELECT
statement.
Related videos on Youtube
Comments
-
arrigonfr about 2 years
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.