Re: [sqlite] how to disable a trigger
Simon, Thank you! I have an odd ball case where the tables have two homes: One is a 'system' db one is a 'document' db. Depending on the case, the user can make changes to the document db, which in that case, needs the triggers, but in the case where the system db is being recloned to the document db, the triggers need to be off. Sam On Mon, Oct 3, 2011 at 4:51 AM, Simon Slavinwrote: > > On 3 Oct 2011, at 4:12am, Sam Carleton wrote: > > > Ok, how do I list what a trigger is so that I can add it back once I want > to > > "reactive" it? > > > To list all triggers: > > SELECT * FROM sqlite_master WHERE type='trigger' > > To list all triggers for a particular table: > > SELECT * FROM sqlite_master WHERE tbl_name='myTable' > > If you know the name of a trigger, > > SELECT * FROM sqlite_master WHERE name='myTrigger' > > To get just the text of the trigger and nothing else > > SELECT sql FROM sqlite_master WHERE name='myTrigger' > > to delete a trigger > > DROP TRIGGER myTrigger > > Warning: messing with TRIGGERs by disabling them should probably only be > done when importing startup data. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
On 3 Oct 2011, at 4:12am, Sam Carleton wrote: > Ok, how do I list what a trigger is so that I can add it back once I want to > "reactive" it? To list all triggers: SELECT * FROM sqlite_master WHERE type='trigger' To list all triggers for a particular table: SELECT * FROM sqlite_master WHERE tbl_name='myTable' If you know the name of a trigger, SELECT * FROM sqlite_master WHERE name='myTrigger' To get just the text of the trigger and nothing else SELECT sql FROM sqlite_master WHERE name='myTrigger' to delete a trigger DROP TRIGGER myTrigger Warning: messing with TRIGGERs by disabling them should probably only be done when importing startup data. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
On Oct 2, 2011, at 10:29 PM, Igor Tandetnik wrote: > Sam Carletonwrote: >> Is there any way to "disable" a trigger in sqlite? > > If you have control over the trigger's definition, you could do something > like this: > > create trigger MyTrigger on ... > when (select enabled from TriggerControl where name='MyTrigger') > begin > ... > end; > > where TriggerControl(name text, enabled integer) is a table with a row for > each trigger you want to manage. You can effectively turn a trigger on and > off with > > update TriggerControl set enabled=? where name='MyTrigger'; > very clever. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
BareFeetWarewrote: > On 03/10/2011, at 2:12 PM, Sam Carleton wrote: > >> Ok, how do I list what a trigger is so that I can add it back once I want to >> "reactive" it? > > select SQL from SQLite_Master where name = 'trigger name' and Type = 'trigger' And be careful to run this statmenet *before* you drop the trigger. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
Sam Carletonwrote: > Is there any way to "disable" a trigger in sqlite? If you have control over the trigger's definition, you could do something like this: create trigger MyTrigger on ... when (select enabled from TriggerControl where name='MyTrigger') begin ... end; where TriggerControl(name text, enabled integer) is a table with a row for each trigger you want to manage. You can effectively turn a trigger on and off with update TriggerControl set enabled=? where name='MyTrigger'; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
On 03/10/2011, at 2:12 PM, Sam Carleton wrote: > Ok, how do I list what a trigger is so that I can add it back once I want to > "reactive" it? select SQL from SQLite_Master where name = 'trigger name' and Type = 'trigger' Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
On Oct 2, 2011, at 10:12 PM, Sam Carleton wrote: > Ok, how do I list what a trigger is so that I can add it back once I want to > "reactive" it? You are looking to temporarily deactivate a TRIGGER, but there is no such mechanism. You could simply copy the code for the TRIGGER, then DROP it, and then add it back again when you need it. Generally the idea behind a TRIGGER is that it fires on the set event without any intervention. Giving the ability to deactivate it would make it discretional, so there isn't any SUSPEND TRIGGER command. > > On Sun, Oct 2, 2011 at 9:07 PM, Igor Tandetnikwrote: > >> Sam Carleton wrote: >>> Is there any way to "disable" a trigger in sqlite? >> >> DROP TRIGGER >> -- >> Igor Tandetnik >> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
Ok, how do I list what a trigger is so that I can add it back once I want to "reactive" it? On Sun, Oct 2, 2011 at 9:07 PM, Igor Tandetnikwrote: > Sam Carleton wrote: > > Is there any way to "disable" a trigger in sqlite? > > DROP TRIGGER > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
Sam Carletonwrote: > Is there any way to "disable" a trigger in sqlite? DROP TRIGGER -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users