MySQL触发开始日期早于结束日期 [英] MySQL trigger start date is before end date

问题描述

我的服务器运行MySQL 5.5.40&我目前有一个触发器，该触发器首先检查两个日期列(discount_start& Discount_end)是否均为NULL或都具有一个日期，然后检查Discount_start是否大于discount_end，其目的是确保开始日期不是在结束日期之后.

My server runs MySQL 5.5.40 & I currently have a trigger which first checks that the two date columns (discount_start & discount_end) are either both NULL or both have a date then checks if discount_start is greater than discount_end, the idea being to make sure that the start date isn't after the end date.

第一个位有效，但是第二个位似乎没有检查开始日期是否在结束日期之后.我已经做了一些Googleing&似乎在这里允许>运算符?

The first bit works but the second, checking that the start date isn't after the end date doesn't seem to. I've done a bit of Googleing & it appears that the > operator is allowed here?

我使用的日期时间是2015年12月10日04:16:34的discount_start&2015-12-08 04:16:38 for disocunt_end，我写的触发器如下.

The datetime I'm using is 2015-12-10 04:16:34 for discount_start & 2015-12-08 04:16:38 for disocunt_end, the trigger I've written is below.

# Both discount_start & discount_end must either be entered or NULL, one cannot be NULL while the other is entered
IF NEW.discount_start IS NULL AND NEW.discount_end IS NOT NULL THEN 
  signal sqlstate '45000' set message_text = 'discount_end has value but discount_start NULL, both need to have a value or be NULL';
ELSEIF NEW.discount_start IS NOT NULL AND NEW.discount_end IS NULL THEN 
  signal sqlstate '45000' set message_text = 'discount_start has value but discount_end NULL, both need to have a value or be NULL';
# discount_start date must be before discount_end
ELSEIF NEW.discount_start > NEW.discount_end THEN
  signal sqlstate '45000' set message_text = 'discount_start must be before discount_end';
ELSE
  SET new.created = NOW();
END IF

推荐答案

尝试

... ELSEIF DATEDIFF(NEW.discount_end, NEW.discount_start) < 0 THEN ...

触发代码中允许使用

关系运算符(< ;,>& c.)，因此我假设您用谷歌搜索的答案专门指的是比较 DATE 类型.

Relational operators (<, > &c.) are allowed in trigger code, so I assume the answer you googled is referring specifically to comparing DATE types.

这篇关于MySQL触发开始日期早于结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！