CREATE TRIGGER

The CREATE TRIGGER defines a on a specified table.

Required privileges

To create a trigger, a user must have the TRIGGER 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 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_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 .

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 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 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)

See also