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)
