Sqlite - Too Many levels of Trigger recursion

Question

I’m getting the error below when I try to make an insertion in Sqlite.

Error while executing SQL query on database 'BDTESTE': Too Many levels of Trigger recursion

Table creation

CREATE TABLE INSTANCES (
ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
NAME varchar(255) NOT NULL UNIQUE,
DESCRIPTION text,
CONFIG_TXT text,
EXTRA_TXT text,
CREATED varchar(50) NULL,
MODIFIED varchar(50) NULL
);

Creation of Trigger

CREATE TRIGGER INSTANCE_INSERT AFTER INSERT ON INSTANCES
BEGIN
UPDATE INSTANCES SET CREATED = DATETIME('NOW') WHERE ID = NEW.ID;
END;

Creation of Trigger - Update

CREATE TRIGGER INSTANCE_UPDATE AFTER UPDATE ON INSTANCES
BEGIN
UPDATE INSTANCES SET MODIFIED = DATETIME('NOW') WHERE ID = OLD.ID;
END;

Insertion

INSERT INTO INSTANCES (NAME, DESCRIPTION, CONFIG_TXT, EXTRA_TXT)
VALUES
('app02', 'Intancia de teste Windows', 'blah blah blah blah blah blah blah 
blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah', '');

Answer 1

The problem is in trigger of update. The update will generate the firing of trigger, then you will be doomed to an infinite loop if you do not treat correctly.

Maybe the following works (ignores updates that don’t set new information):

CREATE TRIGGER INSTANCE_UPDATE AFTER UPDATE ON INSTANCES
BEGIN
    UPDATE INSTANCES SET MODIFIED = DATETIME('NOW')
    WHERE ID = OLD.ID AND MODIFIED != DATETIME('NOW');
END;

This will prevent you from vacuously updating any line.

Another alternative would be to operate on views with triggers, then I put it here. It requires quite a bit more code.