比较MySQL触发器中的日期 [英] Compare dates in MySQL trigger
问题描述
我有两个表,回合和事件。一轮有很多事件。
I have two tables, round and event. One round has many events.
create table round (
round_id INTEGER AUTO_INCREMENT NOT NULL,
round_start_date DATETIME NOT NULL,
round_end_date DATETIME NOT NULL,
CONSTRAINT round_pk PRIMARY KEY (round_id)
);
create table event (
event_id INTEGER AUTO_INCREMENT NOT NULL,
round_id INTEGER NOT NULL,
event_date DATETIME NOT NULL,
CONSTRAINT event_pk PRIMARY KEY (event_id),
CONSTRAINT round_fk FOREIGN KEY (round_id) REFERENCES round (round_id),
);
当在事件表中插入一行时,我想使用触发器来比较event_date字段新插入的行到圆表中其相应条目中的round_start_date和round_end_date字段中。如果event_date早于round_start_date,则应该使用新的event_date更新round_start_date。如果event_date在round_end_date之后,则round_end_date应该用新的event_date更新。
When a row is inserted into the event table, I want to use a trigger to compare the event_date field of the newly inserted row to the round_start_date and round_end_date fields in its corresponding entry in the round table. If event_date is earlier than round_start_date, round_start_date should be updated with the new event_date. If event_date is after round_end_date, round_end_date should be updated with the new event_date.
这是我的触发器。它不起作用,我也不明白为什么。我在网络上找不到任何其他人试图在触发器中使用datetime类型的地方,所以对于我要去哪里,我没有任何参考。
This is my trigger. It does not work, and I do not understand why. I cannot find anywhere on the web where anyone else has tried to use a datetime type in a trigger, so I have no frame of reference for where I am going wrong.
create trigger update_round_date
after insert on event for each row
begin
declare curSdate datetime;
declare curEdate datetime;
set curSdate = (select round_start_date from round where round_id = NEW.round_id);
set curEdate = (select round_end_date from round where round_id = NEW.round_id);
if (NEW.event_date < curSdate) then
update round set round_start_date = NEW.event_date where round_id = NEW.round_id;
else if (NEW.event_date > curEdate) then
update round set round_end_date = NEW.event_date where round_id = NEW.round_id;
end if;
end;
编辑:我只是无法创建触发器。 phpMyAdmin给我这个错误:
#1064-您的SQL语法有错误;请查看与您的MySQL服务器版本相对应的手册,以获取正确的语法以在''第4行附近使用
I simply can't create the trigger. phpMyAdmin gives me this error: "#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 '' at line 4"
编辑2:使用定界符集更新
EDIT 2: Updated with a delimiter set
DELIMITER $$
create trigger update_round_date
after insert on event for each row
begin
declare curSdate datetime;
declare curEdate datetime;
set curSdate = (select round_start_date from round where round_id = NEW.round_id);
set curEdate = (select round_end_date from round where round_id = NEW.round_id);
if (NEW.event_date < curSdate) then
update round set round_start_date = NEW.event_date where round_id = NEW.round_id;
else if (NEW.event_date > curEdate) then
update round set round_end_date = NEW.event_date where round_id = NEW.round_id;
end if;
end$$
这将返回错误:#1064-您在您的SQL语法;请查看与您的MySQL服务器版本相对应的手册,以找到在第13行附近的''处使用的正确语法
This returns the error: "#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 '' at line 13"
推荐答案
MySQL可能会在第一个';'处停止,将您的命令解释为:
MySQL is probably stopping at the first ';', interpreting your command as:
create trigger update_round_date
after insert on event for each row
begin
declare curSdate datetime;
您必须先将分隔符设置为其他值,然后使用该分隔符终止create trigger命令(并将定界符放回末尾):
You have to set your delimiter to something else first, then terminate the create trigger command with that delimiter instead (and put the delimiter back at the end):
delimiter ^
create trigger update_round_date
after insert on event for each row
begin
...
end;
^
delimiter ;
我相信结束
之后的最后一个分号是必须的。
I believe the last semicolon after end
may be necessary.
这篇关于比较MySQL触发器中的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!