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

Question

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 you do?

Danonorwegian

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

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

Luge

How did you create a function in SQLite? – Chive

I'm glad you wrote an extension. Care to share with the class? – Schedule

@Schedule It's just a trivial user-defined function. However, you must use the C API or whatever your SQLite wrapper (or SQLite interfacing library) has provided to allow you to do that. It sucks if that isn't provided and you'll have to resort to a pure SQLite solution. – Orissa

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

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

Longeron

Answer 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'

Avigation

Answer 4

Expanding on Nick Dandoulakis's answer, you could drop all relevant triggers and then reinstate them before the transaction's completion:

BEGIN;
SELECT name, sql FROM sqlite_master WHERE type = 'trigger' AND tbl_name = 'mytable';
-- store all results
-- for each name: DROP TRIGGER $name;
-- do normal work
-- for each sql: execute the SQL verbatim
COMMIT;

Slacken

Answer 5

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;

Vitrescent

Answer 6

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

Towhee

I don't have stored the name of the triggers. So I need to get all triggers specific for a table. – Danonorwegian

Answer 7

Expanding other answers this is how i'm doing it. Take into account that this is disabling all triggers for all tables in the database except some of then used by spatialite

SQLITE_FILE=/tmp/my.sqlite

# Define output sql files as variables
CREATE_TRIGGER_SQL=/tmp/create_triggers.sql
DROP_TRIGGER_SQL=/tmp/drop_triggers.sql

## Dump CREATE TRIGGER statements to a file ##

# To wrap statements in a transaction
echo -e "BEGIN;\n\n" > "${CREATE_TRIGGER_SQL}"
# `SELECT sql` does not output semicolons, so we must concatenate them
sqlite3 -bail "${SQLITE_FILE}" "SELECT sql || ';' FROM sqlite_master WHERE type = 'trigger' AND (name NOT LIKE 'gid_%' AND name NOT LIKE 'ggi_%' AND name NOT LIKE 'ggu_%' AND name NOT LIKE 'gii_%' AND name NOT LIKE 'giu_%' AND name NOT LIKE 'vwgcau_%' AND name NOT LIKE 'vtgcau_%' AND name NOT LIKE 'gcau_%' AND name NOT LIKE 'geometry_columns_%' AND name NOT LIKE 'gcfi_%' AND name NOT LIKE 'gctm_%' AND name NOT LIKE 'vtgcfi_%' AND name NOT LIKE 'vwgcfi_%' AND name NOT LIKE 'vtgcs_%' AND name NOT LIKE 'vwgc_%' AND name NOT LIKE 'vtgc_%' AND name NOT LIKE 'gcs_%');" >> "${CREATE_TRIGGER_SQL}"
echo -e "\n\nCOMMIT;" >> "${CREATE_TRIGGER_SQL}"

## Dump DROP TRIGGER statements to a file ##
echo -e "BEGIN;\n\n" > "${DROP_TRIGGER_SQL}"
sqlite3 -bail "${SQLITE_FILE}" "SELECT 'DROP TRIGGER ' || name || ';' FROM sqlite_master WHERE type = 'trigger' AND (name NOT LIKE 'gid_%' AND name NOT LIKE 'ggi_%' AND name NOT LIKE 'ggu_%' AND name NOT LIKE 'gii_%' AND name NOT LIKE 'giu_%' AND name NOT LIKE 'vwgcau_%' AND name NOT LIKE 'vtgcau_%' AND name NOT LIKE 'gcau_%' AND name NOT LIKE 'geometry_columns_%' AND name NOT LIKE 'gcfi_%' AND name NOT LIKE 'gctm_%' AND name NOT LIKE 'vtgcfi_%' AND name NOT LIKE 'vwgcfi_%' AND name NOT LIKE 'vtgcs_%' AND name NOT LIKE 'vwgc_%' AND name NOT LIKE 'vtgc_%' AND name NOT LIKE 'gcs_%');" >> "${DROP_TRIGGER_SQL}"
echo -e "\n\nCOMMIT;" >> "${DROP_TRIGGER_SQL}"

## Execute like ##
sqlite3 -bail /"${SQLITE_FILE}" < "${DROP_TRIGGER_SQL}"
# do things
sqlite3 -bail /"${SQLITE_FILE}" < "${CREATE_TRIGGER_SQL}"

Figurine