Version: v2.x

Postgres Triggers

Introduction

Postgres triggers are used to invoke previously defined Postgres functions before or after a specific database event (e.g. INSERT) occurs.

Note

For more information on Postgres triggers, please refer to the Postgres documentation. We also have a Learn Tutorial focusing on PostgresSQL. Information on triggers can be found here.

Examples

Trigger a Postgres function before an article is inserted or updated:

Let's say we want to check if an author is active before a corresponding article can be inserted or updated. We can do so with the following Postgres function:

CREATE FUNCTION check_author_active()
    RETURNS trigger AS $BODY$
    DECLARE active_author BOOLEAN;
    BEGIN
    SELECT author.is_active INTO active_author FROM "authors" author WHERE author.id = NEW."author_id";
    IF active_author != TRUE THEN
        RAISE EXCEPTION 'Author must be active';
    END IF;
    RETURN NEW;
    END;
    $BODY$ LANGUAGE plpgsql;

Now we want to have this function executed whenever a new article is about to be inserted or updated. We can create a Postgres trigger as follows:

CREATE TRIGGER insert_article BEFORE INSERT OR UPDATE ON "articles" FOR EACH ROW EXECUTE PROCEDURE check_author_active();

If someone now tries to insert an article for an author that is not active, the following error will be thrown:

unexpected : Author must be active

Refresh a materialized view when an author gets inserted:

Let's say we want to refresh a materialized view whenever a new author is inserted.

The following Postgres function refreshes a materialized view:

CREATE FUNCTION refresh_materialized_view()
  RETURNS trigger AS $BODY$
  BEGIN
  REFRESH MATERIALIZED VIEW popular_active_authors;
  RETURN NULL;
  END;
  $BODY$ LANGUAGE plpgsql;

Now, to make sure this function gets called whenever a new author is inserted, we can create the following Postgres trigger:

CREATE TRIGGER update_materialized_view AFTER INSERT ON "authors" FOR EACH ROW EXECUTE PROCEDURE refresh_materialized_view();