The CREATE TRIGGER statement defines a trigger on a specified table.
Required privileges
To create a trigger, a user must have CREATE privilege on the schema of the trigger.
Synopsis
Parameters
| Parameter | Description |
|---|---|
trigger_create_name | The name of the trigger. |
table_name | The name of the table associated with the trigger. |
func_name | The trigger function that is executed when the trigger activates. |
a_expr | Boolean condition that determines if the trigger function should execute for a given row. For details, refer to Trigger conditions. |
trigger_func_args | A comma-separated list of constant string arguments. |
Examples
The following are examples of basic triggers. For more detailed examples of trigger usage, see Triggers.
Create a BEFORE trigger
Create a sample table:
CREATE TABLE lock_table ( id INT PRIMARY KEY, name TEXT NOT NULL, is_locked BOOLEAN DEFAULT FALSE ); Populate lock_table with sample values:
INSERT INTO lock_table VALUES (1, 'Record 1', FALSE); INSERT INTO lock_table VALUES (2, 'Record 2', TRUE); Create a trigger function that prevents "locked" rows from being deleted:
CREATE OR REPLACE FUNCTION prevent_delete_locked() RETURNS TRIGGER AS $$ BEGIN IF (OLD).is_locked THEN RAISE EXCEPTION 'Record is locked and cannot be deleted'; END IF; RETURN OLD; END; $$ LANGUAGE PLpgSQL; Create a trigger that executes prevent_delete_locked before a DELETE is issued on lock_table:
CREATE TRIGGER prevent_locked_delete BEFORE DELETE ON lock_table FOR EACH ROW EXECUTE FUNCTION prevent_delete_locked(); Test the trigger by attempting to delete a row:
DELETE FROM lock_table WHERE id = 2; ERROR: Record is locked and cannot be deleted SQLSTATE: P0001 View lock_table to verify that the row was not deleted:
SELECT * FROM lock_table; id | name | is_locked -----+----------+------------ 1 | Record 1 | f 2 | Record 2 | t (2 rows) Create an AFTER trigger
Create two sample tables. stock contains a product inventory, and orders_placed contains a list of orders on those products:
CREATE TABLE stock ( "product_id" STRING PRIMARY KEY, "quantity_on_hand" INTEGER NOT NULL DEFAULT 1 ); CREATE TABLE orders_placed ( "product_id" STRING NOT NULL REFERENCES stock ("product_id"), "quantity" INTEGER NOT NULL DEFAULT 1 ); Populate stock with three products each at 1000 count:
INSERT INTO stock ("product_id", "quantity_on_hand") VALUES ('a', 1000), ('b', 1000), ('c', 1000); Create a trigger function that updates the stock table to reflect the quantity on hand after each order that is placed:
CREATE OR REPLACE FUNCTION update_stock_after_order() RETURNS TRIGGER AS $$ BEGIN UPDATE stock SET quantity_on_hand = quantity_on_hand - (NEW).quantity WHERE stock.product_id = (NEW).product_id; RETURN NULL; END; $$ LANGUAGE PLpgSQL; Create a trigger that executes update_stock_after_order after an INSERT is issued on orders_placed (i.e., an order is placed):
CREATE TRIGGER trg_update_stock_after_order AFTER INSERT ON orders_placed FOR EACH ROW EXECUTE FUNCTION update_stock_after_order(); Test the trigger by inserting some sample orders:
INSERT INTO orders_placed (product_id, quantity) VALUES ('a', 1), ('b', 3); View the stock table to see that the quantities have decreased accordingly:
SELECT * FROM stock; product_id | quantity_on_hand -------------+------------------- a | 999 b | 997 c | 1000 (3 rows)