sqlite触发器中的条件插入语句 [英] conditional insert statement in sqlite triggers

问题描述

sqlite触发器是否支持条件/案例/何时语句？

Are conditional if/case/when statements supported in sqlite triggers?

让我们说我具有以下设置：

Let`s say I have the following setup:

CREATE TABLE someTable (id INTEGER PRIMARY KEY, someValue INTEGER);
CREATE TRIGGER update_another_table AFTER  INSERT ON someTable 
BEGIN
    IF(new.someValue==0)
        DELETE FROM another_table WHERE (...some condition);
    ELSE
        IF NOT EXISTS(SELECT anotherValue  FROM another_table  WHERE anotherValue =new.someValue)
            INSERT INTO another_table  VALUES(new.someValue, ...);
        ELSE
            UPDATE another_table SET anotherValue = new.someValue;
END;

但是它会引起语法错误'IF'附近的SQLite错误：语法错误

But it rises a syntax error Sqlite error near 'IF': syntax error"

推荐答案

这是语法错误，因为SQLite触发器的语法图不允许任何IF

That is an syntax error as the Syntax Diagram for SQLite Triggers does not allow any IF clauses nor CASE WHEN constructions.

但是您可以通过定义两个或三个使用WHEN条件的触发器来实现相同的效果，请参见 http://sqlite.org/lang_createtrigger.html

But you can achieve the same effect by defining two or three triggers that use the WHEN condition, see http://sqlite.org/lang_createtrigger.html

因此您将在触发器上创建对于这样的DELETE情况：

So you would create on trigger for your DELETE case like this:

CREATE TRIGGER delete_from_other_table AFTER INSERT ON someTable
WHEN new.someValue = 0
BEGIN
   DELETE FROM anotherTable WHERE (... some condition );
END;

然后在适当的条件下为INSERT和UPDATE Case添加另一个触发器...

And than add another trigger for the INSERT and UPDATE Case with the appropriate conditions...

CREATE TRIGGER update_another_table AFTER INSERT ON someTable
WHEN new.someValue <> 0
BEGIN
   ...
END;

这篇关于sqlite触发器中的条件插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！