SQL Syntax error during Trigger creation: when vs. where

Question

Why does the first query below work in Firefox SQLite manager, while the second doesn't? Why do both fail in SQL Fiddle using SQLite, MySQL, PostGreSQL, et al?

create trigger symmetric_insertion
after insert on T
for each row
when not exists (select * from T where T.A = New.B and T.B = New.A)
begin
  insert into T values (New.B, New.A);
end;

.

create trigger symmetric_insertion
after insert on T
for each row
begin
  insert into T values (New.B, New.A)
  where not exists (select * from T where T.A = New.B and T.B = New.A);
end;

EDIT: An infinite loop would not be created, because I have not turned the recursive_triggers setting to be true. Furthermore, MySQL allows only a finite depth of recursion.

You're likely having trouble with SQL Fiddle because you need to set the proper query terminator. See this example: sqlfiddle.com/#!7/6951e — Jake Feasel
– Jake Feasel, Commented Aug 9, 2012 at 6:43
Thanks! That looks right. Do you know why the second snipped doesn't work in the Firefox mysql manager? — Rose Perrone
– Rose Perrone, Commented Aug 9, 2012 at 8:22

Darthtater · Accepted Answer · 2012-08-08 23:25:05Z

1

Don't know why the first trigger work in Firefox SQLite manager. A trigger should fail if it causes the table to insert into itself and it is best when it won't compile. I'd guess it might be because how the Firefox SQLite wrote their DBMS with a when clause that would allow the trigger to compile. The second would be illegal SQL. When it executes its just going to insert every time and then cascade so you can't allow it.

answered Aug 8, 2012 at 23:25

Darthtater

1112 bronze badges

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

5 Comments

Rose Perrone Over a year ago

The two queries are semantically equivalent, so the second query won't actually create an infinite loop. The DBMS doesn't know about the semantic equivalence, however, so it requires the WHEN clause to prevent an infinite loop. Thanks!

Darthtater Over a year ago

insert into T values (New.B, New.A) will always execute the insert since where not exists (select * from T where T.A = New.B and T.B = New.A) inside a trigger will always evaluate to true. Triggers are all executed before the transaction ends (there are ways around this but they are not advisable).

Rose Perrone Over a year ago

An easy way to stop the recursion is to set recursive_triggers to false. In some DBMS implementations, you can set the limit of the recursion depth to be some constant.

Darthtater Over a year ago

Then you've got a trigger that never does anything. If a transaction is not ended with a commit the table never is inserted into (there are ways around this to allow your DBMS does dirty reads but that brings on other issues).

Rose Perrone Over a year ago

I've tried it out, and after the first insert on T, trigger just triggers the inner insert once, and does not recur (that is, the second insertion does not prompt any further insertions).

Collectives™ on Stack Overflow

SQL Syntax error during Trigger creation: when vs. where

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related