Create Trigger

Trigger for a before operation

Lets look at an example of how to create a trigger which will get executed before an operation is executed.

CREATE FUNCTION check_user_type()
RETURNS trigger AS $BODY$
DECLARE active_user BOOLEAN;
BEGIN
SELECT users.age INTO active_user FROM "users" WHERE users.id = NEW."id";
IF active_user > 13 THEN
RAISE EXCEPTION 'User must be atleast 13';
END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

Trigger for a materialized view

Let’s say we want to refresh a materialized view whenever a new user is inserted.

The following Postgres function refreshes a materialized view:

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

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

CREATE TRIGGER update_materialized_view AFTER INSERT
ON "users" FOR EACH ROW EXECUTE PROCEDURE refresh_materialized_view();
Did you find this page helpful?
Start with GraphQL on Hasura for Free
  • ArrowBuild apps and APIs 10x faster
  • ArrowBuilt-in authorization and caching
  • Arrow8x more performant than hand-rolled APIs
Promo
footer illustration
Brand logo
© 2024 Hasura Inc. All rights reserved
Github
Titter
Discord
Facebook
Instagram
Youtube
Linkedin