mysql 检查日期约束(触发器)

2022-01-15 00:00:00 mariadb mysql

很抱歉再次打扰，我在 MySql Maria DB 上是全新的(可能也在触发器上)并希望添加检查功能以确保此表中没有插入过去的日期.我相信Mysql的唯一方法是创建一个触发器.我试图创建一个，但我一直遇到问题.谁能告诉我我在这里做错了什么(也许我需要一杯浓咖啡):

Sorry for disturbing again, I completely new on MySql Maria DB (maybe on trigger as well) and looking to add check function to ensure no past date is inserted in this table. I believe that the only way to do it Mysql is to create a trigger. I have tried to create one and I keep on having problems. Can someone tell me what I am doing wrong here (maybe I need a strong coffee): 

CREATE TRIGGER check_date BEFORE INSERT on Event
  FOR EACH ROW 
  BEGIN 
  if new.date <= now() then
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Event cannot start in the past     event cannot start now. Choose an ulterior date'
end
end if;

请我在终端上.每次系统看到分号时，它都会结束我的查询.因此，我不能有 2 个分号.

Please that i'm on a terminal. Every time the system sees a semicolon it ends my query. I cannot therefore have 2 semicolons.

感谢您的帮助.我希望我足够清楚......

thanks for your help . I hope im clear enough...

推荐答案

查询前需要更改分隔符:

You need to change delimiter before the query:

delimiter //

CREATE TRIGGER check_date BEFORE INSERT on Event
FOR EACH ROW
BEGIN
  IF new.date <= now() THEN
    SIGNAL SQLSTATE '45000' 
    SET MESSAGE_TEXT = 'Event cannot start in the past     event cannot start now. Choose an ulterior date';
  END IF;
END;//

delimiter ;

另外，在你 END 为事件(触发器)执行整个代码块之前结束你的 IF 语句

Also, end your IF statement before you END the entire block of code to execute for an event(trigger)

相关文章