我正在使用 SQLite 創建一個觸發器，我想比較 OLD 值是否與 NEW 值不同，以便執行INSERT
這是我創建的觸發器，但似乎 MySQL 語法在 SQLite 中不起作用。什么是等價的？
CREATE TRIGGER document_handler AFTER UPDATE ON document
FOR EACH ROW
BEGIN
IF NEW.type <> OLD.type THEN
INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
VALUES (id, "type", OLD.type, NEW.type, NOW());
END IF;
IF NEW.title <> OLD.title THEN
INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
VALUES (id, "title", OLD.title, NEW.title, NOW());
END IF;
IF NEW.path <> OLD.path THEN
INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
VALUES (id, "path", OLD.path, NEW.path, NOW());
END IF;
END;
uj5u.com熱心網友回復：
在 SQLite 中沒有IF條件陳述句。
在這種情況下，您可以使用簡單的WHERE子句：
CREATE TRIGGER document_handler AFTER UPDATE ON document
FOR EACH ROW
BEGIN
INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
SELECT NEW.id, 'type', OLD.type, NEW.type, CURRENT_TIMESTAMP
WHERE NEW.type IS NOT OLD.type;
INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
SELECT NEW.id, 'title', OLD.title, NEW.title, CURRENT_TIMESTAMP
WHERE NEW.title IS NOT OLD.title;
INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
SELECT NEW.id, 'path', OLD.path, NEW.path, CURRENT_TIMESTAMP
WHERE NEW.path IS NOT OLD.path;
END;
我改變了所有<>可以IS NOT比較的NULL值。
此外，SQLite 中的等價物NOW()是CURRENT_TIMESTAMP.
查看簡化的演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/463247.html
標籤：sqlite 触发器 条件语句 where子句 sql-插入