Re: ALTER TRIGGER Before / After?

On Mon, 29 Oct 2007 22:33:28 +0000
Richard Huxton <dev(at)archonet(dot)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