SQLite Forum

Removing not null constraint doesn't behave correctly
Login
I don't refer to the thread per se.

My remark is simply pointing out that in the 12-step procedure proposed to make profond changes to a table X (beyond what alter table currently does), the step #3 is not sufficient in all cases and must also include triggers of other tables which issue SQL statement(s) against the changed table X.

For instance, in the "4. Some example triggers" paragraph, the first trigger updates table orders "after UPDATE OF address ON customers".

So if one only looks at what is in sqlite_master with name 'customers', the trigger update_customer_address won't be selected/examined/modified and might cause havoc after an alter table changing sqlite_master directly.
This won't be noticed until the unchanged trigger is invoked, as AFAIK triggers are only parsed when launched.