postgres trigger creation

Question

How do I only create a trigger if it does not exist?

When I do create or replace, I get a syntax error so I am looking for a way to test for the existence of a trigger.

I can always select * from pg_trigger, but I am sure there is a more suitable way.

Thanks

it's always a good idea to mention what version of postgres you are using — user533832
– user533832, Commented Dec 9, 2010 at 12:13

trashgod · Accepted Answer · 2011-07-28 09:36:08Z

21

Postgres can conditionally drop a trigger - see the docs. Do this before creating the trigger, then it will always work.

DROP TRIGGER IF EXISTS mytrigger ON mytable;

As Jack points out in the comments, this feature has only been available since 8.2; this has been out for more than four years though, so it should be available in your version.

edited Jul 28, 2011 at 9:36

trashgod

206k33 gold badges264 silver badges1.1k bronze badges

answered Dec 9, 2010 at 1:34

El Yobo

15k6 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user533832 Over a year ago

@El Yobo, worth mentioning that this feature was introduced in 8.2

user533832 Over a year ago

@El Yobo It's hard to be sure how many are still using 8.1 - I think a key factor to consider is that it is the default install on RHEL5 and I suspect that means use is still widespread

El Yobo Over a year ago

Yeah, I had the ... pleasure ... of setting up a RHEL server the other day. I thought Debian stable was behind the curve, but RHEL takes the cake.

reza Over a year ago

this is a great answer for dropping but how do I check if a trigger exists before creating it is really what I am after

El Yobo Over a year ago

@reza: if you always drop it first, you will always be able to create it safely. This safely ensures that it never exists when you try to create it.

|

Rob · Accepted Answer · 2017-08-21 13:59:31Z

1

CREATE TRIGGER (NameOfTrigger) AFTER INSERT OR UPDATE ON (NameOfTable)

DROP TRIGGER IF EXISTS (NameOfTrigger) ON (NameOfTable);

edited Aug 21, 2017 at 13:59

Rob♦

27.4k16 gold badges89 silver badges103 bronze badges

answered Aug 21, 2017 at 13:54

bedjaoui djounaydi

212 bronze badges

Comments

St-Lucifer · Accepted Answer · 2024-08-12 06:01:06Z

0

you can replace the existing trigger in postgres while creating the trigger - see the docs

CREATE OR REPLACE TRIGGER YOUR_TRIGGER_NAME ...

This is supported in postgres version 12 and above.

answered Aug 12, 2024 at 6:01

St-Lucifer

214 bronze badges

Collectives™ on Stack Overflow

postgres trigger creation

3 Answers 3

7 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Related