在SQLite中定义触发器的语法错误

Question

如下表所示：

CREATE TABLE "active_mtrs" 
(
     "mtr_ID" INTEGER PRIMARY KEY NOT NULL,
     "status" INTEGER, 
     "NIrSTime" DATETIME, 
     "NIrETime" DATETIME
)

我将触发器定义为：

CREATE TRIGGER "main"."replace1" 
AFTER INSERT ON "active_mtrs" FOR EACH ROW  
BEGIN 
    DECLARE @NIrSTime DATETIME;
    DECLARE @NIrETime DATETIME;
    DECLARE @mtr_ID INTEGER DEFAULT 0;

    SELECT @mtr_ID = mtr_ID FROM INSERTED;

    SELECT @NIrSTime = NIrSTime,@NIrETime = NIrETime 
    FROM subscriber 
    WHERE mtr_ID = @mtr_ID;

    UPDATE active_mtrs 
    SET NIrSTime = @NIrSTime,
        NIrETime = @NIrETime 
    WHERE mtr_ID = @mtr_ID; 
END

其中subscriber是此数据库中的另一个表。

但我发现了一个错误：

接近“声明”：语法错误 异常名称: NS_ERROR_FAILURE 异常消息:组件返回故障代码: 0x80004005 (NS_ERROR_FAILURE)

我的问题是什么？

Answer 1

您的问题是，您试图在SQLite中使用不同的SQL方言。

不能使用声明；必须直接查找值：

CREATE TRIGGER main.replace1
AFTER INSERT ON active_mtrs
FOR EACH ROW  
BEGIN 
    UPDATE active_mtrs
    SET (NIrSTime, NIrETime) =
            (SELECT NIrSTime, NIrETime
             FROM subscriber
             WHERE mtr_ID = NEW.mtr_ID)
    WHERE mtr_ID = NEW.mtr_ID;
END;