CODEWITHSUNDEEP
mysqlsqltriggerssyntax-error
Юрий Златев
Юрий Златев

Reputation: 1

How to declare variable in triggers

Upon creating a trigger i get the following error:

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 '' at line 3

This is my query:

CREATE TRIGGER reserved BEFORE INSERT ON Reserv
FOR EACH ROW BEGIN 
    DECLARE trip_id integer;
    DECLARE free integer;
    DECLARE count integer;

    set @free := (select place_free from Trips where id = trip_id);
    set @count := (select count from inserted);

    if @count <= @free
        begin

            update Trips set place_free = @free - @count where id = @trip_id;

        end
    ELSE
        BEGIN
            ROLLBACK;
            return;
        END
 END

Upvotes: 0

Views: 678

Answers (1)

Lars Gendner
Lars Gendner

Reputation: 1982

What I see is that you are using the RETURN statement. As https://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html#stored-routines-trigger-restrictions states:

The RETURN statement is not permitted in triggers, which cannot return a value. To exit a trigger immediately, use the LEAVE statement.

But from my point of view this is a different topic, as the error message speaks of a syntax error in line 3. Maybe you forgot to set the delimiter to something different than ;:

DELIMITER //
CREATE TRIGGER ...

Upvotes: 2

Related Questions