mysql触发器模拟断言 [英] mysql triggers simulating assertions

问题描述

让我们考虑一下表

Video(
    IDvideo(PK),
    Date,
    Description,
    User
)

使用mysql，我无法编写断言. 是否可以使用一个或多个触发器来模拟以下断言?

with mysql I have no way of writing assertions. Is it possible to simulate the following assertion using one or more triggers ?

create assertion asser1  
check (0 = 
    ( select count(*)  
      from Video  
      where Date >= DATE_SUB(current_date(),INTERVAL  1 YEAR ) 
          && Date<=current_date()  
      group by User   
      having count(*) > 200
    )
)

我应该如何编写触发器?

how should I write that trigger?

推荐答案

好吧，问题在于MySQL没有等效的STOP ACTION命令.因此，基本上，解决方法很脏:

Well, the problem is that MySQL doesn't have an equivalent of a STOP ACTION command. So basically, the work arounds are quite dirty:

一种方法是，您可以违反触发器内的约束以冒泡错误并取消插入:

One way is that you can violate a constraint inside the trigger to bubble an error and cancel the insert:

CREATE TABLE stop_action (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(35),
    UNIQUE KEY (id, name)
);
INSERT INTO stop_action (1, 'Assert Failure');

然后，在触发器中，只需尝试:

Then, in the trigger, just try to:

INSERT INTO stop_action (1, 'Assert Failure');

这样做的好处是，返回的错误将是重复的键错误，并且文本中将包含"Assert Failure".

The benefit of that, is that the error that's returned will be a duplicate key error, and the text will include "Assert Failure".

因此，您的触发器将变为:

So then your trigger would become:

delimiter |

CREATE TRIGGER asser1_before BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    SELECT count(*) INTO test FROM (select count(*)
          from Video  
          where Date >= DATE_SUB(current_date(),INTERVAL  1 YEAR ) 
            && Date<=current_date()  
          group by User   
          having count(*) > 200);
    IF test != 0 THEN
        INSERT INTO stop_action (1, 'Assert Failure');
    END IF;
  END;
|

delimiter ;

现在，您也需要在UPDATE之前执行此操作，否则您可以将日期更新为无效状态.否则，至少应该可以让您开始...

Now, you'd need to do this before UPDATE as well, otherwise you could update the date into an invalid state. But otherwise, that should at least get you started...

这篇关于mysql触发器模拟断言的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！