这是我在MySql中的第一个触发器，我遇到了一些问题。 我尝试了这两段代码，但都无法编译。 我没有where子句也能用。
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
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);
当没有指定表时，MySql不喜欢在我的代码中存在SELECT/WHERE。
这将不起作用:在存在的地方选择“works”（从my-table中选择1）
我用一个计数(*)来解决这个问题:-
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 ;
