ALTER TRIGGER Before / After?
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
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
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