How to handle a missing feature of SQLite : disable triggers?

SQLite stores schema (meta) information in the built-in sqlite_master table.

To get a list of available triggers use the below query:

SELECT name FROM sqlite_master
WHERE type = 'trigger' -- AND tbl_name = 'a_table_name'

Set a flag in your database and use it in the triggers WHEN condition.

Say you want to create a trigger on the "clients" table after an insert. You have created a table "trigger_settings" with a TINYINT "triggers_on" field - this is your flag. Then you can set the field to 0 if you want to turn off the filters and to 1 when you want to turn them back on.

Then you create your filter with a WHEN condition that checks the "triggers_on" field.

For example:

CREATE TRIGGER IF NOT EXISTS log_client_data_after_insert
  AFTER INSERT
  ON [clients]
  WHEN (SELECT triggers_on FROM trigger_settings)=1
BEGIN
  your_statement
END;

I wrote a very simple extension function to set a boolean value to true or false.

And a function to retrieve this value (GetAllTriggersOn()).

With this function I can define all my triggers like:

CREATE TRIGGER tr_table1_update AFTER UPDATE ON TABLE1 WHEN GetAllTriggersOn()
BEGIN
    -- ...
END

So here it is 2015 and there still is no 'disable triggers' in SQLite. For a mobile Application this can be problematic--especially if it's a corporate App requiring offline functionality and local data.

An initial data load can be slowed to crawl by trigger execution even when you don't wrap each insert in an individual transaction.

I solved this issue using SQLite SQL fairly simply. I have a settings table that doesn't participate in the init load. It holds 'list' of key/value pairs. I have one key called 'fireTrigger' with a bit value of 0 or 1. Every trigger I have has an expression that selects value and if it equals 1 it fires the trigger, otherwise it doesn't.

This expression is in addition to any expressions evaluated on the data relating to the trigger. e.g.:

AND 1 = (SELECT val FROM MTSSettings WHERE key = 'fireTrigger')

In simple clean effect this allows me to disable/enable the trigger with a simple UPDATE to the settings table