SQLite conditionals in creation of a trigger

user_12526559 0
Viewed 31

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;
sql-insert conditional-statements where-clause triggers sqlite
1 Answers

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.

user_104988280
Related