ALTER TABLE with complicated trigger with version >3.26
(1) By skiingyac on 2020-10-22 17:24:46 [source]
I am using version 3.33 and we have the following simplified example of an issue we are seeing that seems to be due to the 3.25/3.26 ALTER TABLE improvements. I believe this pattern is following the one recommended at https://sqlite.org/lang_altertable.html. If I uncomment the PRAGMA legacy_alter_table line, it works as expected. However, the documentation seems to suggest that this PRAGMA isn't necessary here.
CREATE TABLE enclave (
CREATE TABLE guests (
CREATE TRIGGER update_eff_perm
AFTER UPDATE OF perm ON guests
FOR EACH ROW WHEN OLD.perm <> NEW.perm
BEGIN UPDATE guests
SET perm = min(NEW.perm, (SELECT max_perm FROM enclave WHERE active=1))
WHERE name = NEW.name;
CREATE TABLE enclave2 (
active INTEGER DEFAULT 1,
INSERT INTO enclave2 (
DROP TABLE enclave;
ALTER TABLE enclave2 RENAME TO enclave;
The error it produces:
$ sqlite3 < test.sql
Error: near line 32: error in trigger update_eff_perm: no such table: main.enclave
I believe this is occuring because of the somewhat complex trigger which uses a second table. If for example the trigger is simplified to only refer to the guests table, and the ALTER TABLE is instead on the guests table, it works just fine without the legacy_alter_table pragma.
I'm hoping someone can point out what we are doing wrong. It does not seem like the intended behavior is that for certain triggers the legacy_alter_table pragma is mandatory if you're editing a table it refers to.