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; $$;
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();
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