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.
<code>
PRAGMA foreign_keys=OFF;
--PRAGMA legacy_alter_table=ON;
BEGIN TRANSACTION;
CREATE TABLE enclave (
active INTEGER,
max_perm INTEGER
);
CREATE TABLE guests (
name TEXT,
perm INTEGER
);
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;
END;
CREATE TABLE enclave2 (
active INTEGER DEFAULT 1,
max_perm INTEGER
);
INSERT INTO enclave2 (
active, max_perm
)
SELECT
active, max_perm
FROM enclave;
DROP TABLE enclave;
ALTER TABLE enclave2 RENAME TO enclave;
PRAGMA foreign_key_check;
COMMIT;
</code>
The error it produces:
<code>
$ sqlite3 < test.sql
Error: near line 32: error in trigger update_eff_perm: no such table: main.enclave
</code>
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.