CODEWITHSUNDEEP
mysql
osieman
osieman

Reputation: 62

MySql trigger with where clause

This is my first trigger in MySql and I am having a few problems. I tried both of these pieces of code but both would not compile. I got it to work without the where clause.

CREATE TRIGGER ins_meal_details
AFTER INSERT ON meal_details
FOR EACH ROW 
 INSERT INTO sql_changes
 SET
 sc_table='book_room',
 sc_reason='DINNER1',
 sc_key='bh_no=NEW.bh_no,date=NEW.md_date',
 sc_value='1', 
 sc_done =0
WHERE not exists (select 1 from booking where bh_no = NEW.bh_no and bo_date = NEW.md_date and bo_meals < 1)


CREATE TRIGGER ins_meal_details AFTER INSERT meal_details FOR EACH ROW
BEGIN
IF NOT EXISTS (select 1 from booking where bh_no = NEW.bh_no and bo_date = NEW.md_date and bo_meals < 1) THEN
    INSERT INTO sql_changes (sc_table, sc_reason, sc_key, sc_value, sc_done )
    VALUES ('book_room','DINNER1', 'bh_no=NEW.bh_no,date=NEW.md_date','1', 0);
END IF
END

Upvotes: 0

Views: 463

Answers (2)

osieman
osieman

Reputation: 62

MySql did not like the select/where exists in my code when there is no table specified. This was due to using version 5.6 of MySql server. This will not work: select 'works' where exists (select 1 from my-table) The fix would be thanks to @akina to add from DUAL. The best solution.

I got round it by using a count(*) instead :-

DROP TRIGGER IF EXISTS ins_meal_details;
DELIMITER //
CREATE TRIGGER ins_meal_details
    AFTER INSERT ON meal_details FOR EACH ROW
    BEGIN
        IF (select count(*) from booking where bh_no = NEW.bh_no and bo_date = NEW.md_date and bo_meals < 1) > 0 THEN
INSERT INTO sql_changes (sc_table, 
                         sc_reason,
                         sc_key,
                         sc_value, 
                         sc_done)
VALUES ('book_room','DINNER1', CONCAT('bh_no=',NEW.bh_no,',date=',NEW.md_date),'New Value', 0);

        END IF;
    END//
DELIMITER ;

Upvotes: 0

Akina
Akina

Reputation: 42632

CREATE TRIGGER ins_meal_details
AFTER INSERT 
ON meal_details
FOR EACH ROW 
INSERT INTO sql_changes (sc_table, 
                         sc_reason,
                         sc_key,
                         sc_value, 
                         sc_done)
SELECT 'book_room',
       'DINNER1',
       CONCAT('bh_no=',NEW.bh_no,',date=',NEW.md_date),
       1, 
       0
WHERE NOT EXISTS (SELECT 1 
                  FROM booking 
                  WHERE bh_no = NEW.bh_no 
                    AND bo_date = NEW.md_date 
                    AND bo_meals < 1);

Upvotes: 1

Related Questions