How To Trigger Query In PostgreSQL

Triggers in PostgreSQL are used with functions to perform an operation like delete, update, insert, etc. before or after a query runs. Let's see examples of how to create a trgigger in postgresql.

Create Function Returning Trigger

Our use case is that when users added a new post to your website, you may want to check its content and publish it automatically after check. So, if the post status is set as published in the admin_check table, the trigger will perform an insertion operation in the posts table.

CREATE OR REPLACE FUNCTION publish_post() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
  -- New represent the row after update
  -- Old represent the row before update
  -- If admin publish the post, insert it into the posts table and delete it from admin_check table
  IF NEW.status = 'published' THEN
    INSERT INTO posts VALUES(OLD.id, OLD.content, NEW.status);
    DELETE FROM admin_check WHERE id = NEW.id;
  END IF;
  RETURN NULL;
  END;
$$;

Create Trigger

In this step, we will create a trigger by specifying when the function above will run. In our case, the function will be executed after updating the status of the new post in the admin_check table.

CREATE OR REPLACE TRIGGER after_update_status
  -- When status column of the admin_check table was updated, the function publish_post will trigger.
  -- Also You can use the INSERT, DELETE, TRUNCATE operations
AFTER UPDATE OF status 
ON admin_check 
FOR EACH ROW
EXECUTE PROCEDURE publish_post();

Create Trigger Running Before Database Operation In Postgresql

The example above was covering the operation after. If you want to do something before a query, then here is an example of how to update the updated_at column when updated other columns of the row.

-- Function
CREATE OR REPLACE FUNCTION update_updated_at() RETURNS TRIGGER 
  LANGUAGE plpgsql
  AS
  $$
  BEGIN
    -- Note that, the type of updated_at must be TIMESTAMP
    -- Assign the date, which is date of the update
    NEW.updated_at = CURRENT_TIMESTAMP;
    -- Replace the new row with the old row
    RETURN NEW;
  END;
  $$;

-- Trigger
CREATE OR REPLACE TRIGGER after_update_content
BEFORE UPDATE --Trigger a function when any of the all columns was updated
ON posts
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at();

Conclusion

In this guide, we have shown you how to trigger a query before or after running anohter query in postgresql such as delete, update, insert, etc.

Thank you for reading