Re: [sqlite] how to disable a trigger

2011-10-03 Thread Sam Carleton 
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 Slavin  wrote:

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

2011-10-03 Thread Simon Slavin 

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

2011-10-02 Thread Mr. Puneet Kishor 

On Oct 2, 2011, at 10:29 PM, Igor Tandetnik wrote:

> Sam Carleton  wrote:
>> 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

2011-10-02 Thread Igor Tandetnik 
BareFeetWare  wrote:
> 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

2011-10-02 Thread Igor Tandetnik 
Sam Carleton  wrote:
> 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

2011-10-02 Thread BareFeetWare 
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

2011-10-02 Thread Mr. Puneet Kishor 

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 Tandetnik  wrote:
> 
>> 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

2011-10-02 Thread Sam Carleton 
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 Tandetnik  wrote:

> 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

2011-10-02 Thread Igor Tandetnik 
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] how to disable a trigger

2011-10-02 Thread Sam Carleton 
Is there any way to "disable" a trigger in sqlite?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users