Creating a PostgreSQL Trigger with a When Condition
Summary: in this tutorial, you will learn how to create a conditional trigger that fires only when a condition is true.
In PostgreSQL, a trigger is a database object that automatically executes a function whenINSERT,UPDATE,DELETE, orTRUNCATE event occurs on a table.
Sometimes, you want the trigger to be activated only when a specific condition is met. To do that, you specify a boolean condition in theWHEN clause of theCREATE TRIGGER statement, like so:
CREATE TRIGGER trigger_nameON table_nameWHEN conditionEXECUTE FUNCTION function_name(arguments);In this syntax, thecondition is a boolean expression. If thecondition is true, the trigger is fired; otherwise, the trigger will not be activated.
In row-level triggers, you can access the old/new values of columns of the row within the condition. However, in statement-level triggers, you do not have access to column values.
PostgreSQL Trigger When Condition example
First,create a table calledorders to store order data:
CREATE TABLE orders ( order_idINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_idINT NOT NULL, total_amountNUMERIC NOT NULL DEFAULT 0, status VARCHAR(20)NOT NULL);Second, create another table calledcustomer_stats to store the total spent amount by customers:
CREATE TABLE customer_stats ( customer_idINT PRIMARY KEY, total_spentNUMERIC NOT NULL DEFAULT 0);Third, create anAFTER INSERT trigger that inserts a row into thecustomer_stats table when a new row is inserted into theorders table:
CREATE OR REPLACE FUNCTION insert_customer_stats()RETURNS TRIGGERAS $$BEGIN INSERT INTO customer_stats (customer_id) VALUES (NEW.customer_id); RETURN NULL;END;$$LANGUAGE plpgsql;CREATE TRIGGER insert_customer_stats_triggerAFTER INSERT ON ordersFOR EACHROWEXECUTE FUNCTION insert_customer_stats();Fourth, define anAFTER UPDATE trigger on theorders table with a condition:
CREATE OR REPLACE FUNCTION update_customer_stats()RETURNS TRIGGERAS$$BEGIN IF NEW.status= 'completed' THEN -- Update the total_spent for the customer UPDATE customer_stats SET total_spent= total_spent+ NEW.total_amount WHERE customer_id= NEW.customer_id; END IF; RETURN NULL;END;$$LANGUAGE plpgsql;CREATE TRIGGER update_customer_stats_triggerAFTER UPDATE ON ordersFOR EACHROWWHEN (OLD.status<> 'completed' AND NEW.status= 'completed')EXECUTE FUNCTION update_customer_stats();TheAFTER UPDATE trigger fires only when the status of the row changes from non-completed state to completed.
Fifth,insert some rows into theorders table:
INSERT INTO orders (customer_id, total_amount,status)VALUES (1,100,'pending'), (2,200,'pending');TheAFTER INSERT trigger fires and insert rows into thecustomer_stats table.
Sixth, change the order statuses of customer id 1 and 2 tocompleted:
UPDATE orderSET status = 'completed'WHERE customer_idIN (1,2);TheAFTER UPDATE trigger fires and updates thetotal_spent column in thecustomer_stats table.
Finally, retrieve the data from thecustomer_stats table:
SELECT * FROM customer_stats;Output:
customer_id | total_spent-------------+------------- 1 | 100 2 | 200(2 rows)Summary
- Specify a condition in the
WHENclause of theCREATE TRIGGERstatement to instruct PostgreSQL to fire the trigger when the condition is true.
Last updated on