MySql trigger with where clause

Question

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

"both would not compile" with what errors? perhaps those indicate the cause being that typo. — underscore_d
– underscore_d, Commented Jun 28, 2020 at 9:38
I edited the above to read 'CREATE TRIGGER ins_meal_details' which works, but without the where clause. — osieman
– osieman, Commented Jun 28, 2020 at 9:44
this works: 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 — osieman
– osieman, Commented Jun 28, 2020 at 9:46
Your second trigger uses begin..end so you need to set delimiters. and a terminator after the end if — P.Salmon
– P.Salmon, Commented Jun 28, 2020 at 9:47

Akina · Accepted Answer · 2020-06-28 10:25:29Z

1

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);

answered Jun 28, 2020 at 10:25

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

osieman · Accepted Answer · 2020-06-28 20:54:48Z

0

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 ;

edited Jun 28, 2020 at 20:54

answered Jun 28, 2020 at 12:39

osieman

621 silver badge8 bronze badges

5 Comments

Akina Over a year ago

This will not work: select 'works' where exists (select 1 from my-table) Justify your statement (which is more than controversial). Maybe you cannot execute the query because you have used minus sign in the tablename without proper quoting?

osieman Over a year ago

I can only tell you what is happening with my version of mysql but this is what I get : 'Error SQL query: Documentation select 'works' where exists (select 1 from booking) MySQL said: Documentation #1064 - 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 'where exists (select 1 from booking)' at line 1'

Akina Over a year ago

This must work. fiddle. You have some problem elsewhere. Show exact query text and exact error message, specify DBMS version precisely.

osieman Over a year ago

I see what the problem is now.. I am using Server version: 5.6.47 . using your fiddle it does not work if set to a lower version.

Akina Over a year ago

On the version 5.6 you must add oracle-style FROM dual. fiddle.

Collectives™ on Stack Overflow

MySql trigger with where clause

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related