The CREATE TRIGGER statement defines a trigger on a specified table.
Required privileges
To create a trigger, a user must have the TRIGGER privilege on the table and the EXECUTE privilege on the trigger function. By default, the public role has EXECUTE privilege on all functions, so this is granted automatically unless it has been revoked.
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)
