Обсуждение: Re: How to create trigger if it does not exist

Andrus ha escrito:
> CREATE TRIGGER mycheck_trigger BEFORE INSERT OR UPDATE ON mytbl
>     FOR EACH ROW EXECUTE PROCEDURE mycheck_pkey();
>
> aborts transaction if trigger already exists.
>
> There in no CREATE OR REPLACE TRIGGER command in PostgreSQL
>
> How to create trigger only when it does not exist ?
>
> Andrus.

DROP TRIGGER IF EXISTS...

See:

http://www.postgresql.org/docs/8.2/static/sql-droptrigger.html

On May 26, 5:58 pm, "Andrus" <kobrule...@hot.ee> wrote:
> Thank you.
> This doc says that dropping trigger drops depending objects also.

Only if you use CASCADE (default is RESTRICT).

"Rodrigo De Le�n" <rdeleonp@gmail.com> kirjutas s�numis
news:1180317464.302359.95000@k79g2000hse.googlegroups.com...
> On May 26, 5:58 pm, "Andrus" <kobrule...@hot.ee> wrote:
>> Thank you.
>> This doc says that dropping trigger drops depending objects also.
>
> Only if you use CASCADE (default is RESTRICT).
>

If I do not use CASCADE deleting trigger fail if there are objects which
depend on trigger.
So I MUST use cascase.

Andrs.

>> How to create trigger only when it does not exist ?

> DROP TRIGGER IF EXISTS...
>
> See:
>
> http://www.postgresql.org/docs/8.2/static/sql-droptrigger.html

Thank you.
This doc says that dropping trigger drops depending objects also.

Which objects depend on the user-defined trigger so that they are also
dropped ?

Andrus.

Hi Guys,
Using CASCADE can be a bit dangerous as there might be other tables,
functions, views etc. that will be dropped but they are not meant to be.

Try this:

DO
$$
BEGIN
IF NOT EXISTS(SELECT *
                     FROM information_schema.triggers
                     WHERE event_object_table = 'tablename'
                     AND trigger_name = 'triggername'
                     )
                    THEN
                                 <Insert your create trigger syntx here>;


    END IF ;

END;
$$






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-create-trigger-if-it-does-not-exist-tp1882226p5745434.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.