Re: disable trigger from transaction - Mailing list pgsql-general

I don't know if droping a trigger inside a transaction will work. Besides
that, we want the trigger to do its work in all other circumstances. With a
hundred connections on the database, I don't know what kind of issues that
would cause if the trigger were there, and suddenly, not there. We figured
this was a safe approach.

On Monday 24 January 2005 01:27 pm, Jeff Davis saith:
> Would it work to just do a DROP TRIGGER at the begining of the
> transaction and a CREATE TRIGGER at the end?
>
> Regards,
>     Jeff Davis
>
> On Mon, 2005-01-24 at 06:50 -0500, Terry Lee Tucker wrote:
> > Razvan,
> >
> > I don't believe there is a way of doing this from by way of some
> > postgreSQL command. We accomplish this by creating a table called
> > "override". It is defined as:
> > recid     | integer                    | not null default
> >     nextval('public.override_recid_seq'::text)
> > trig_name | character varying | not null
> > pid          | integer                  | not null
> > batch     | character varying | not null
> > Indexes:
> >     "override_pkey" primary key, btree (recid)
> >     "override_pid_key" unique, btree (pid, trig_name)
> >     "override_pid_pkey1" btree (pid, batch)
> >
> > We use this table to accomplish what you are talking about. We insert
> > into the table the trigger name, pid, and some made up string into batch.
> > We use batch so we can provide different levels of override, but you may
> > not need that. For the triggers we are interested in overriding, we code
> > them to check for the existance of a record in override that matches the
> > trigger name and the pid, and possibly, a batch name. If we find an
> > override record, we simply return.
> >
> > Here is an example:
> >     SELECT INTO ovrRec * FROM override WHERE
> >         pid = pg_backend_pid () AND trig_name = name;
> >     IF FOUND THEN
> >         IF dbg THEN
> >             RAISE NOTICE ''%: Overriding'', name;
> >         END IF;
> >         RETURN true;                        -- outa here
> >     END IF;
> >     RETURN false;
> >
> > Actually, we put the above code into a function and call the function
> > from triggers that we may need to override from some other place.
> >
> > Maybe some of the others have a better way. Hope this helps.
> >
> > On Monday 24 January 2005 06:02 am, Postgres General saith:
> > > hello,
> > >
> > > I am interested in disabling a trigger from a transaction.
> > > I am not want to disable the trigger globally but only for the current
> > > transaction.
> > >
> > > Can I do it somehow ?
> > >
> > >
> > > thanks,
> > > Razvan Radu
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 5: Have you checked our
> > > extensive FAQ?
> > >
> > >                http://www.postgresql.org/docs/faq
> >
> > __
> >  Work: 1-336-372-6812
> >  Cell: 1-336-363-4719
> > email: terry@esc1.com
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

--
 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com