Postgres Pro
Downloads
Home   >   mailing lists

Thread: ALTER TRIGGER Before / After?

ALTER TRIGGER Before / After?

From
Josh Trutwin
Date:
On Postgresql 8.1 I am guessing there isn't a convenient way to alter
a trigger to change its before/after behavior?  I wrote one of my
first triggers using an AFTER and now I release I needed to
do BEFORE.  It's used on a couple tables so I was hoping to avoid
dropping it and re-creating it but if that is my only option, so be
it.

Josh

Re: ALTER TRIGGER Before / After?

From
Richard Huxton
Date:
Josh Trutwin wrote:
> On Postgresql 8.1 I am guessing there isn't a convenient way to alter
> a trigger to change its before/after behavior?  I wrote one of my
> first triggers using an AFTER and now I release I needed to
> do BEFORE.  It's used on a couple tables so I was hoping to avoid
> dropping it and re-creating it but if that is my only option, so be
> it.

What's the problem with drop/create?

BEGIN;
DROP TRIGGER...
CREATE TRIGGER...
COMMIT;

No other activity needs to be interrupted.

A common trick is to put these changes in a script with a ROLLBACK at
the end. That way you can run the script, look for errors and only put
the commit at the end once it all works.


--
   Richard Huxton
   Archonet Ltd

Re: ALTER TRIGGER Before / After?

From
Josh Trutwin
Date:
On Mon, 29 Oct 2007 22:33:28 +0000
Richard Huxton <dev@archonet.com> wrote:

> Josh Trutwin wrote:
> > On Postgresql 8.1 I am guessing there isn't a convenient way to
> > alter a trigger to change its before/after behavior?  I wrote one
> > of my first triggers using an AFTER and now I release I needed to
> > do BEFORE.  It's used on a couple tables so I was hoping to avoid
> > dropping it and re-creating it but if that is my only option, so
> > be it.
>
> What's the problem with drop/create?
>
> BEGIN;
> DROP TRIGGER...
> CREATE TRIGGER...
> COMMIT;
>
> No other activity needs to be interrupted.
>
> A common trick is to put these changes in a script with a ROLLBACK
> at the end. That way you can run the script, look for errors and
> only put the commit at the end once it all works.

Nothing, other than having to script it to work on about 30 tables.
But I'd have to script an ALTER TABLE as well.

And yeah, ROLLBACK on DDL is sure a nice feature that PostgreSQL
has.

Josh