I want to "create or replace" a trigger for a postgres table. However, there is not such sql expression.
I see that I can do a "DROP TRIGGER IF EXISTS" first (http://www.postgresql.org/docs/9.5/static/sql-droptrigger.html).
My question are:
- Is there a recommended/better option than (
DROP + CREATE trigger)
- Is there a reason why there is not such "create or replace trigger" (which might imply that I should not be wanting to do it)
Note that there is a "Create or Replace Trigger" in oracle (https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm). Then,
- Is such command planned for Postgres at all?
Postgresql has transaction DDL so BEGIN > DROP > CREATE > COMMIT is the equivalent of CREATE OR REPLACE
This is a nice write-up of how postgre's transactional DDL compares to other systems (such as oracle)
Current postgres planned features regarding triggers do not include adding the REPLACE syntax.
No way to create or replace a trigger but can do this way
DROP TRIGGER IF EXISTS yourtrigger_name on "yourschemaname"."yourtablename";
You should use two statements: one for drop trigger and another for creating a trigger.
Example:
DROP TRIGGER IF EXISTS my_trigger
ON my_schema.my_table;
CREATE TRIGGER my_trigger
BEFORE INSERT OR UPDATE
ON my_schema.my_table
FOR EACH ROW EXECUTE PROCEDURE my_schema.my_function();
you can use below code.
DO $$ BEGIN
CREATE (trigger, type , ...);
EXCEPTION
WHEN others THEN null;
END $$;
sample:
DO $$ BEGIN
CREATE TRIGGER trigger_workIDExist
BEFORE INSERT OR UPDATE ON "GalleryModel"
FOR EACH ROW EXECUTE PROCEDURE check_workIDExist();
EXCEPTION
WHEN others THEN null;
END $$;
You can combine CREATE OR REPLACE FUNCTION trigger_function with the following script in your SQL:
DO $$
BEGIN
IF NOT EXISTS(SELECT *
FROM information_schema.triggers
WHERE event_object_table = 'table_name'
AND trigger_name = 'trigger_name'
)
THEN
CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW EXECUTE PROCEDURE trigger_function();
END IF;
END;
$$
