Postgres Pro
Downloads
Home   >   mailing lists

Thread: Disabling Trigger

Disabling Trigger

From
"Prasad dev"
Date:
Hello All,

Does any one know how disable a specific trigger i know the following way to
disable trigger but it disables system generated default triggers too.

UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid
='table_name'::pg_catalog.regclass;

I also tried the following

UPDATE pg_trigger SET tgenabled = FALSE where tgname='trigger_name';

But this too doesnt work.

Any help appreciated.

Cheers
Prasad.

Re: Disabling Trigger

From
Tom Lane
Date:
"Prasad dev" <esteem3300@hotmail.com> writes:
> Does any one know how disable a specific trigger i know the following way to
> disable trigger but it disables system generated default triggers too.

FWIW, PG 8.1 will have a supported command for that.

> I also tried the following
> UPDATE pg_trigger SET tgenabled = FALSE where tgname='trigger_name';
> But this too doesnt work.

That should work in recent releases, though you do have an issue of
whether backends will notice it right away.  A dummy update on the
pg_class row is the best way around that problem AFAIK.

What PG version are you using, and what exactly does "not work" mean?

            regards, tom lane

Re: Disabling Trigger

From
"Prasad dev"
Date:
Hi Tom,

Currently i am using 8.0.3 , what i mean by it doesn't work is, when i set
the tgenabled to FALSE for that particular trigger it still fires the
trigger unless there is something more to it which i doesn't know.




>From: Tom Lane <tgl@sss.pgh.pa.us>
>To: "Prasad dev" <esteem3300@hotmail.com>
>CC: pgsql-novice@postgresql.org
>Subject: Re: [NOVICE] Disabling Trigger Date: Tue, 30 Aug 2005 00:42:43
>-0400
>
>"Prasad dev" <esteem3300@hotmail.com> writes:
> > Does any one know how disable a specific trigger i know the following
>way to
> > disable trigger but it disables system generated default triggers too.
>
>FWIW, PG 8.1 will have a supported command for that.
>
> > I also tried the following
> > UPDATE pg_trigger SET tgenabled = FALSE where tgname='trigger_name';
> > But this too doesnt work.
>
>That should work in recent releases, though you do have an issue of
>whether backends will notice it right away.  A dummy update on the
>pg_class row is the best way around that problem AFAIK.
>
>What PG version are you using, and what exactly does "not work" mean?
>
>            regards, tom lane

Re: Disabling Trigger

From
Tom Lane
Date:
"Prasad dev" <esteem3300@hotmail.com> writes:
> Currently i am using 8.0.3 , what i mean by it doesn't work is, when i set
> the tgenabled to FALSE for that particular trigger it still fires the
> trigger unless there is something more to it which i doesn't know.

If you start a fresh session, you'll probably find that it doesn't fire
the trigger anymore.

The trick is to get existing sessions to notice that you changed the
pg_trigger row; they don't watch for that.  What they do watch for is
updates of pg_class, which is why a dummy update of the table's pg_class
row might help.

            regards, tom lane