CODEWITHSUNDEEP
mysqlsql
dark knight
dark knight

Reputation: 85

Trigger problems in mysql

i have a table named reservation which consists of column reservationStatus and place and another table spot which consists of spot_id and spot_status. i have created a trigger in reservation table which should activate after the reservationStatus has been set to 0. but it is not working

DELIMITER $$
CREATE TRIGGER spotCancel
AFTER UPDATE ON reservation
FOR EACH ROW
BEGIN

IF NEW.reservationStatus = 0 AND OLD.place <=>  'AbcMall' THEN
UPDATE spot SET spot_status = 0 WHERE spot_id <=> OLD.spot_id;
END IF;

IF NEW.reservationStatus = 0 AND OLD.place <=> 'XyzGym' THEN
UPDATE spot1 SET spot_status =0 WHERE spot_id <=> OLD.spot_id;

END IF;
END$$ 
DELIMITER ;

This query fails silently everytime and i couldnot find the error. I am using xampp and mysql innoDB storage engine.

Upvotes: 0

Views: 44

Answers (1)

Barmar
Barmar

Reputation: 780974

You're missing an END IF.

DELIMITER $$
CREATE TRIGGER spotCancel
AFTER UPDATE ON reservation
FOR EACH ROW
BEGIN

    IF NEW.reservationStatus = 0 AND OLD.place <=>  'Abc' THEN
        UPDATE spot SET spot_status = 0 WHERE spot_id <=> OLD.spot_id;
    END IF;

    IF NEW.reservationStatus = 0 AND OLD.place <=> 'Def' THEN
        UPDATE spot1 SET spot_status =0 WHERE spot_id <=> OLD.spot_id;
    END IF;

END$$ 
DELIMITER ;

Upvotes: 1

Related Questions