I'm creating a trigger using SQLite and I want to compare if the OLD value is different than the NEW value so I can execute an INSERT
This is the trigger I created but seems like the MySQL syntax does not work in SQLite. What would be the equivalent?
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;
CodePudding user response:
In SQLite there is no IF
conditional statement.
In this case you can use simple WHERE
clauses:
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;
I changed all <>
with IS NOT
which can compare also NULL
values.
Also, the equivalent of NOW()
in SQLite is CURRENT_TIMESTAMP
.
See a simplified demo.