Cannot CREATE TRIGGER in SQLite3

Question

I have issues creating this trigger second_loop in sqlite3: It is part of more code but it's this exact part that's giving errors. When I execute the code on the database I get the following: near "CREATE": syntax error.

I am sure the part inside the trigger works well as I've tested it separately, but there must be some other kind of problem with this.

Note that I have another trigger in the same SQL and it is working just fine!

Please advise, Thanks

CREATE TRIGGER second_loop AFTER UPDATE OF stack_count ON global WHEN NEW.stack_count<>0

BEGIN
 CREATE TEMP TABLE last_row(id INTEGER PRIMARY KEY, unvisited INTEGER DEFAULT 1, predecessor INTEGER DEFAULT 0, discovery INTEGER DEFAULT 0, finish INTEGER DEFAULT 0);

 INSERT INTO last_row
        SELECT *
        FROM nodes
        WHERE id = (SELECT node_id
                   FROM stack
                   ORDER BY oid DESC LIMIT 1);

 UPDATE last_row SET unvisited =0, predecessor = (SELECT parent_id FROM global), discovery = (SELECT time FROM global);

 UPDATE global SET current_id = (SELECT node_id FROM stack WHERE ROWID = (SELECT MAX(ROWID) FROM stack));
 UPDATE global SET time = time + 1;

 DELETE FROM stack WHERE ROWID = (SELECT MAX(ROWID));

 INSERT INTO stack(node_id)
   SELECT DISTINCT edges.target_id
   FROM nodes, edges, global
   WHERE edges.source_id=global.current_id AND nodes.unvisited=1;

  -- update stack count to go to second_loop trigger
  UPDATE global SET stack_count = stack_count + 1;
 END;

Apparently, SQLite doesn't like CREATE TEMP TABLE in triggers; could you use a "normal" table instead and just DELETE all rows from it at the start of your trigger? — Frank Schmitt
– Frank Schmitt, Commented Aug 28, 2012 at 15:53
I got rid of the TEMP tables and used "normal" ones and still have the same error! — lyes
– lyes, Commented Aug 28, 2012 at 18:23

Doug Currie · Accepted Answer · 2012-08-28 15:54:35Z

3

SQLite3's CREATE TRIGGER statement does not support embedded CREATE TEMP TABLE substatements according to the syntax diagram.

answered Aug 28, 2012 at 15:54

Doug Currie

41.4k1 gold badge103 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lyes Over a year ago

I got rid of the TEMP tables and used "normal" ones and still have the same error!

Tony Gibbs Over a year ago

As per Doug's link to the documenation - It looks as though sqlite triggers can only contain update, insert, delete and select statements.

Collectives™ on Stack Overflow

Cannot CREATE TRIGGER in SQLite3

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related