Home > Software design >  Mysql trigger statement keeps giving syntax error
Mysql trigger statement keeps giving syntax error

Time:11-07

I keep getting error messages when I try to execute this statement in phpmyadmin. I'm running mysql 5.7

DELIMITER //
CREATE OR REPLACE TRIGGER update_counts_trigger AFTER INSERT OR UPDATE OR DELETE ON tickets
FOR EACH ROW
BEGIN
    DECLARE V_uitvoeringId, V_ReserveringId varchar(50);
    DECLARE V_tekoop, V_gereserveerd, V_wachtlijst int;
    
    SET V_ReserveringId = NEW.reserveringId OR OLD.reserveringId;
    SET V_uitvoeringId = ( SELECT uitvoeringId FROM reservering WHERE id=V_ReserveringId )
    SET V_tekoop = (
        SELECT count(*) 
        FROM tickets t 
        WHERE t.tekoop AND NOT t.geannuleerd AND NOT t.verkocht AND t.uitvoeringId = V_uitvoeringId);

    SET V_gereserveerd =  (
        SELECT count(*) 
        FROM tickets t 
        WHERE NOT t.wachtlijst AND NOT t.geannuleerd AND NOT t.verkocht AND t.uitvoeringId = V_uitvoeringId);

    SET V_wachtlijst = (
        SELECT count(*) FROM tickets t WHERE t.wachtlijst AND NOT t.geannuleerd AND NOT t.verkocht AND t.uitvoeringId = V_uitvoeringId);

    update uitvoering 
        set tekoop = V_tekoop,       
            gereserveerd =  V_gereserveerd, 
            wachtlijst =  V_wachtlijst,
            vrije_plaatsen = aantal_plaatsen - V_gereserveerd   V_tekoop  
        WHERE id=V_uitvoeringId; 
END //
DELIMITER ;

Can anyone see what's wrong?

  • Unrecognized statement type. (near "DECLARE" at position 176)
  • This type of clause was previously parsed. (near "SET" at position 398)
  • Unrecognized statement type. (near "END" at position 1192)

CodePudding user response:

There are other syntax errors causing those complaints.

  1. There is no OR REPLACE option with triggers in MySQL. The trigger must be dropped and then recreated.
  2. There is no ORing with actions, only one, which means that you'll have to create 3 triggers even though the definition may be the same. However, it seems like you only need an AFTER UPDATE action? ... because AFTER INSERT does not have an OLD reference, and AFTER DELETE does not have a NEW reference.
  3. Getting past those two items, there is also a ; missing after the query with SELECT uitvoeringId.

Putting all that together, becomes:

DELIMITER //
CREATE TRIGGER update_counts_trigger AFTER UPDATE ON tickets
FOR EACH ROW
BEGIN
    DECLARE V_uitvoeringId, V_ReserveringId varchar(50);
    DECLARE V_tekoop, V_gereserveerd, V_wachtlijst int;
    
    SET V_ReserveringId = NEW.reserveringId OR OLD.reserveringId;
    SET V_uitvoeringId = ( SELECT uitvoeringId FROM reservering WHERE id=V_ReserveringId );
    SET V_tekoop = (
        SELECT count(*) 
        FROM tickets t 
        WHERE t.tekoop AND NOT t.geannuleerd AND NOT t.verkocht AND t.uitvoeringId = V_uitvoeringId);

    SET V_gereserveerd =  (
        SELECT count(*) 
        FROM tickets t 
        WHERE NOT t.wachtlijst AND NOT t.geannuleerd AND NOT t.verkocht AND t.uitvoeringId = V_uitvoeringId);

    SET V_wachtlijst = (
        SELECT count(*) FROM tickets t WHERE t.wachtlijst AND NOT t.geannuleerd AND NOT t.verkocht AND t.uitvoeringId = V_uitvoeringId);

    update uitvoering 
        set tekoop = V_tekoop,       
            gereserveerd =  V_gereserveerd, 
            wachtlijst =  V_wachtlijst,
            vrije_plaatsen = aantal_plaatsen - V_gereserveerd   V_tekoop  
        WHERE id=V_uitvoeringId; 
END //
DELIMITER ;

Then with that in phpMyAdmin:

enter image description here

Then after executing the SQL:

enter image description here

Then checking the triggers tab:

enter image description here

...and clicking Edit at the trigger shows the definition:

enter image description here

  • Related