SQLite Forum

Statement that fires trigger is not executed when the trigger causes an error
Login

Statement that fires trigger is not executed when the trigger causes an error

(1) By pythonizer on 2021-05-09 12:34:27 [link] [source]

Hi,

I'm trying to understand what is the default behaviour of SQLite when there is an error in the BEGIN-END section of a trigger.

For example:

  CREATE TABLE my_table (id INTEGER PRIMARY KEY);
  INSERT INTO my_table VALUES (1);

  CREATE TRIGGER R1
  AFTER DELETE ON my_table
  BEGIN
    DELETE FROM Non_existent_table WHERE id=OLD.id;
  END;

So I created a table and then added a trigger that is doomed to fail. If I try to remove something from my_table I get an error, of course:

  DELETE FROM my_table WHERE id=1;
  Error: no such table: main.Non_existent_table

Now, since I'm using AFTER DELETE on the trigger, I would expect that the original DELETE statement on my_table would have been executed, but that is not the case:

  SELECT * FROM my_table;
  id       
---------- 1

Why is the original statement that caused the trigger to fire, not executed when there is an error on the trigger?
Is it rolling back the changes by default?
Or does SQLite parse the trigger's actions before executing anything? If so, why wasn't this parsing done when the trigger was created? Seems like that would make more sense.

Anyway, this may be a stupid question, but it's really bugging me that I can't figure it out.

Thanks

(2) By Richard Damon (RichardDamon) on 2021-05-09 13:18:17 in reply to 1 [link] [source]

I think the issue you are missing is that the statement is executed as part of a session (implied if you don't create one), and a result of the error is to roll back the session so it is as if it never happened.

(3) By Keith Medcalf (kmedcalf) on 2021-05-09 19:15:35 in reply to 1 [source]

Why is the original statement that caused the trigger to fire, not executed when there is an error on the trigger?

The error occurs during the prepare stage of processing. Since no execution plan (VDBE code) is generated, it is not executed. It is like a C program (for example) that has a fatal error during compilation which aborts the compilation. Since no compilation has occurred, there is nothing to execute.

Is it rolling back the changes by default?

No. There is nothing to roll back since there was nothing done.

Or does SQLite parse the trigger's actions before executing anything?

Of course. When you issue, in this case, a DELETE statement, then all applicable DELETE triggers must be parsed (compiled into) the executable code. When that compilation fails (due to an error such as a reference to a non-existent table in the present case) there is nothing to execute and the compiler emits an error message.

If so, why wasn't this parsing done when the trigger was created?

The trigger was parsed as being semantically correct when it was created. However at compilation time (when you sought to prepare to execute it) the trigger was discovered to contain errors thus preventing successful compilation and execution in the context in which it was sought to be executed.

(4) By pythonizer on 2021-05-10 10:20:55 in reply to 3 [link] [source]

Very clear and concise. I get it now. Thank you!