在 SQLite 中创建条件 SQL 触发器

Creating a conditional SQL trigger in SQLite

提问by

I'm trying to write a trigger for sqlite and just running into all kinds of problems. In truth I think my real problem is with my poor background in the sql language. Anyway here goes…

我正在尝试为 sqlite 编写触发器，但遇到了各种问题。事实上，我认为我真正的问题是我在 sql 语言方面的背景很差。无论如何，这里是……

I have two tables Table1 and Table2. Table1 has a column named time (which is a 64bit integer time). I need a trigger that watches for a new row being inserted in Table1. If there are 3 or more rows in Table1 that have time greater than X (a hard coded value in the below example 120 seconds) I need to insert a new row into Table2.

我有两个表 Table1 和 Table2。表 1 有一个名为 time 的列（这是一个 64 位整数时间）。我需要一个触发器来监视插入到表 1 中的新行。如果 Table1 中有 3 行或更多行的时间大于 X（以下示例中的硬编码值 120 秒），我需要在 Table2 中插入一个新行。

Here is what I have so far (note this does not work)

这是我到目前为止所拥有的（注意这不起作用）

CREATE TRIGGER testtrigger AFTER  
INSERT ON Table1 WHEN 
( 
   SELECT COUNT() AS tCount FROM
   ( 
     SELECT * FROM Table1 WHERE  
       time > (NEW.time - 120)  
   ) WHERE tCount > 3
) 
BEGIN 
   INSERT INTO Table2 (time, data) VALUES 
   (NEW.time, 'data1');  
END

Any kind souls out there who are better in SQL than I?

有比我更擅长 SQL 的好心人吗？

回答by dlamblin

This works because the WHENclause needs an expression:

这是有效的，因为该WHEN子句需要一个表达式：

sqlite> .schema Table1
CREATE TABLE Table1 (time int);
CREATE TRIGGER testtrigger AFTER INSERT ON Table1
WHEN 3<(SELECT Count() FROM Table1 WHERE time>(NEW.time-120))
BEGIN
INSERT INTO Table2 (time, data) VALUES (NEW.time,'data1');
END;

Have you looked at this reference page? From what I can tell this is a "misuse of aggregate" which probably stems from statement in the Whensection. You had this:

你看过这个参考页面吗？据我所知，这是“滥用聚合”，这可能源于本When节中的陈述。你有这个：

sqlite> .tables
Table1  Table2
sqlite> .schema Table1
CREATE TABLE Table1 (time int);
CREATE TRIGGER testtrigger AFTER
INSERT ON Table1 WHEN 
( 
   SELECT COUNT() AS tCount FROM
   ( 
     SELECT * FROM Table1 WHERE  
       time > (NEW.time - 120)  
   ) WHERE tCount > 3
) 
BEGIN 
   INSERT INTO Table2 (time, data) VALUES 
   (NEW.time, 'data1');  
END;
sqlite> .schema Table2
CREATE TABLE Table2 (time int,data string);
sqlite> insert into Table1 VALUES (5);
SQL error: misuse of aggregate: 
sqlite> 

I tried deleting "WHERE tCount" to make it into an expression, but then I got a syntax error at the operator.

我尝试删除“ WHERE tCount”以使其成为表达式，但随后在操作符处出现语法错误。

So instead I switched things about for the solution above.

所以相反，我为上面的解决方案切换了一些东西。

回答by J. Polfer

Your WHEN clause in the trigger should be a comparison expression which returns true or false, instead of returning a number. Try dlamblin's idea.

触发器中的 WHEN 子句应该是返回 true 或 false 的比较表达式，而不是返回数字。试试 dlamblin 的想法。

回答by Bob

Maybe a different syntactical approach?

也许不同的句法方法？

CREATE TRIGGER testtrigger ON Table1

FOR INSERT 
AS
BEGIN

  DECLARE @timeNum int

  SET @timeNum = SELECT count(*) FROM Table1 WHERE time > (New.time - 120)

  IF @timeNum > 3

    BEGIN
        INSERT INTO Table2 (time, data) VALUES 
            (NEW.time, 'data1');
    END

END

But also, try some debugging statements. When I was debugging my last trigger for a webservice I put some INSERT statements into a debugging table that I setup. So then you could output the @timeNum every time the trigger gets called, and then put another debug INSERT inside the loop to make see if you actually get into your Table2 INSERT logic.

而且，尝试一些调试语句。当我调试 Web 服务的最后一个触发器时，我将一些 INSERT 语句放入我设置的调试表中。因此，您可以在每次调用触发器时输出 @timeNum，然后在循环中放置另一个调试 INSERT 以查看您是否真的进入了 Table2 INSERT 逻辑。

UPDATE: Sorry! Looks like SqlLite kinda sucks, I did not know that it lacked some of this syntax. Nonetheless, if you are not getting any answers, consider some debugging statements to make sure that your code paths are being called under the right conditions.

更新：对不起！看起来 SqlLite 有点糟糕，我不知道它缺少一些这种语法。尽管如此，如果您没有得到任何答案，请考虑一些调试语句以确保您的代码路径在正确的条件下被调用。