How To Handle A Missing Feature Of Sqlite : Disable Triggers?

How to handle a missing feature of SQLite: disable triggers? I don't have it stored the name of triggers for a specific table. For example how can I drop all triggers? What would

Solution 1:

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:

CREATETRIGGER tr_table1_update AFTER UPDATEON TABLE1 WHEN GetAllTriggersOn()
BEGIN-- ...END

Solution 2:

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.:

AND1 = (SELECT val FROM MTSSettings WHEREkey = 'fireTrigger')

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

Solution 3:

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'

Solution 4:

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:

CREATETRIGGER IF NOTEXISTS log_client_data_after_insert
  AFTER INSERTON [clients]
  WHEN (SELECT triggers_on FROM trigger_settings)=1BEGIN
  your_statement
END;

Solution 5:

Maybe you can make a stored procedures for droping and creating them. Is that good for you ?