Declaring variables while creating a trigger in MySql

Question

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.

peterm · Accepted Answer · 2013-11-10 05:19:37Z

2

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 ; including IF ... END IF;, WHILE ... END WHILE; etc.
To refer to columns of a row being updated you have to use NEW keyword instead of UPDATED.
You don't need to wrap every SET statement in BEGIN...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 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.

answered Nov 10, 2013 at 5:19

peterm

93k16 gold badges157 silver badges162 bronze badges

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

3 Comments

arrigonfr Over a year ago

wow... i guess there are many more differences between MySQL and SQL Server than i thought. Thank you for your response.

peterm Over a year ago

@user2975318 You're quite welcome :) If you feel like the answer was helpful please, consider to accept it.

peterm Over a year ago

@user2975318 If you need help in translating in to MySQL consider to explain in plain words the logic of what that trigger should do.

Collectives™ on Stack Overflow

Declaring variables while creating a trigger in MySql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related