You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Have a couple of tables, a VIEW, a TRIGGER on a VIEW and an UPDATE statement:
CREATETABLEfoo (
id INTEGERNOT NULLPRIMARY KEY,
name TEXTNOT NULL,
selected INTEGERASBooleanNOT NULL
);
CREATETABLEbar (
id INTEGERNOT NULLPRIMARY KEY,
short_name TEXTNOT NULL,
full_name TEXTNOT NULL,
selected INTEGERASBooleanNOT NULL
);
CREATEVIEWfoobarASSELECT id, name, selected FROM foo
UNIONSELECT id, short_name AS name, selected FROM bar;
CREATETRIGGERfoobar_update_added
INSTEAD OF UPDATE OF selected ON foobar
BEGINUPDATE foo SET selected =new.selectedWHERE id =new.id;
UPDATE bar SET selected =new.selectedWHERE id =new.id;
END;
updateFoobarSelected:
UPDATEOR IGNORE foobar SET selected = ? WHERE id = ?;
ah right. thats gonna be an interesting one. Do you know what happens to an update on a view that doesn't have a trigger? Does it crash at runtime when you execute the view or does it just do nothing? Would be nice if we can also fail at compile time if you have an update statement on a view with on corresponding trigger (if SQLite also disallows it)
SQLite docs seem to imply that it's an error to perform INSERT, UPDATE or DELETE on a view without an INSTEAD OF trigger (emphasis mine):
If one or more ON INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is not an error to execute an INSERT, DELETE or UPDATE statement on the view, respectively.
A little test confirms it:
$ sqlite3
SQLite version 3.24.02018-06-0414:10:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE foo (
...> id INTEGERNOT NULLPRIMARY KEY,
...> name TEXTNOT NULL
...> );
sqlite> CREATE VIEW bar ASSELECT*FROM foo;
sqlite>UPDATE bar SET name ='Baz'WHERE id =1;
Error: cannot modify bar because it is a view
sqlite> CREATE TRIGGER bar_update
...> INSTEAD OF UPDATE OF name ON bar
...>BEGIN
...>UPDATE foo SET name =new.nameWHERE id =new.id;
...> END;
sqlite>UPDATE bar SET name ='Baz'WHERE id =1;
sqlite>
Would be nice if we can also fail at compile time if you have an update statement on a view with on corresponding trigger (if SQLite also disallows it)
Versions: 1.0.0-alpha5, 1.0.0-rc2
Have a couple of tables, a
VIEW
, aTRIGGER
on aVIEW
and anUPDATE
statement:Compilation fails with the following error:
Removing
UNION
and the secondSELECT
from theVIEW
lets the compiler to run just fine.The text was updated successfully, but these errors were encountered: