SQLite - too many levels of trigger recursion

Question

SQLite - too many levels of trigger recursion

Navigation

#1 by (3 votes)

2

I'm getting the error below when I try to insert into SQLite.

Error while executing SQL query on database 'BDTEST': too many levels of trigger recursion

Creating the table

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
);

Creating Trigger

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

Creating the 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', '');

sql sqlite sqlite3 sqlite-studio

asked by anonymous 23.10.2017 / 15:48

1 answer

How to make button to confirm if you want to leave the page? How can I style the select tag arrow in Firefox?

score 3 · Accepted Answer

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

Maybe the following will work (ignores updates that are not 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 updating an empty line any way.

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