how to disable a trigger

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

how to disable a trigger

Sam Carleton-2
Is there any way to "disable" a trigger in sqlite?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to disable a trigger

Igor Tandetnik
Sam Carleton <[hidden email]> wrote:
> Is there any way to "disable" a trigger in sqlite?

DROP TRIGGER
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to disable a trigger

Sam Carleton-2
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 <[hidden email]> wrote:

> Sam Carleton <[hidden email]> wrote:
> > Is there any way to "disable" a trigger in sqlite?
>
> DROP TRIGGER
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to disable a trigger

Puneet Kishor-2

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 <[hidden email]> wrote:
>
>> Sam Carleton <[hidden email]> wrote:
>>> Is there any way to "disable" a trigger in sqlite?
>>
>> DROP TRIGGER
>> --
>> Igor Tandetnik
>>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to disable a trigger

BareFeetWare-2
In reply to this post by Sam Carleton-2
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
[hidden email]
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to disable a trigger

Igor Tandetnik
In reply to this post by Sam Carleton-2
Sam Carleton <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to disable a trigger

Igor Tandetnik
In reply to this post by BareFeetWare-2
BareFeetWare <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to disable a trigger

Puneet Kishor-2
In reply to this post by Igor Tandetnik

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

> Sam Carleton <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to disable a trigger

Simon Slavin-3
In reply to this post by Sam Carleton-2

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to disable a trigger

Sam Carleton-2
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 <[hidden email]> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users