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