Create or replace trigger postgres

sql

transactional-ddl

trigger-management

database-structures

byAnton Shumikhin·Oct 20, 2024

CREATE OR REPLACE FUNCTION your_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    -- Trigger code here (and remember, no production code is complete without a few 🦆 quacking in the comments!)
END;
$$ LANGUAGE plpgsql;

-- Vanish, old trigger! You have no power here!
DROP TRIGGER IF EXISTS your_trigger ON your_table;

CREATE TRIGGER your_trigger
AFTER INSERT OR UPDATE OR DELETE ON your_table
FOR EACH ROW EXECUTE FUNCTION your_trigger_func();

To recreate a Postgres trigger, update the trigger function with CREATE OR REPLACE FUNCTION, dismiss the outdated trigger with DROP TRIGGER IF EXISTS, and reintroduce it via CREATE TRIGGER. This sequence provides an efficient template to keep your trigger squeaky clean and up-to-date.

Your guide to trigger management in PostgreSQL

Embrace safe replacement inside transactions

When it comes to managing triggers in PostgreSQL, the safety of your data should not be an afterthought. PostgreSQL supports transactional DDL, promising an atomic approach to update database structures. By containing trigger alterations within a BEGIN ... COMMIT transaction block, we ensure an all-or-nothing execution.

-- Ah, the safety of transaction blocks
BEGIN;
DROP TRIGGER IF EXISTS your_trigger ON your_table;
-- Create your shiny new trigger here!
COMMIT;

Verifying if a trigger already exists

A wise developer checks if the ground is clear before leaping. Hence, before creating a trigger, ensure that it does not already exist. This vital information resides under the information_schema.triggers table. Scripts can leverage this information to conditionally create or update triggers like a pro!

Leverage version control for trigger modifications

Where complexities arise, let version control systems emerge! Keep track of your trigger definitions using version control to stay sane amidst chaotic modifications. This lifeline becomes even more vital when collaborating within teams, allowing an easy overview, review, and rollback of changes when needed.

Stay ahead with alternative methods and PostgreSQL novelties

Be informed about future support for "create or replace"

Keeping up-to-date with PostgreSQL forums and documentation can reap benefits in the long run. Future versions might introduce the much-awaited "create or replace" feature for triggers. Stay informed on release notes to make the most of the emerging trigger management features.

Handling constraint triggers

As of PostgreSQL 14, "CREATE OR REPLACE TRIGGER" is still a dream for constraint triggers. Therefore, using the good old DROP TRIGGER IF EXISTS followed by CREATE TRIGGER seems to be the fight worth fighting for those pesky constraint triggers.

Understanding database differences across systems

Knowing how PostgreSQL's transactional DDL operates provides useful context when stacked up against other RDBMS which might have different ideologies towards object creation and replacement.

explain-codes / Sql / Create or replace trigger postgres

Linked

Row-level trigger vs statement-level trigger



How to get the trigger(s) associated with a view or a table in PostgreSQL



How to move tables from public to other schema in Postgres



Postgresql syntax check without running the query



Delete rows with foreign key in PostgreSQL

