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.
- There is no
OR REPLACE
option with triggers in MySQL. The trigger must be dropped and then recreated. - There is no
OR
ing 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 anAFTER UPDATE
action? ... becauseAFTER INSERT
does not have anOLD
reference, andAFTER DELETE
does not have aNEW
reference. - Getting past those two items, there is also a
;
missing after the query withSELECT 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:
Then after executing the SQL:
Then checking the triggers tab:
...and clicking Edit
at the trigger shows the definition: