PostgreSQL Triggers

Summary: in this tutorial, you’ll learn about PostgreSQL triggers and how to use them to automate some database tasks.

Introduction to PostgreSQL triggers #

In PostgreSQL, a trigger is a user-defined function or procedure invoked automatically when an event such as INSERT, UPDATE, DELETE, or TRUNCATE occurs on a table or view.

In practice, you can find the triggers helpful in the following situations:

• Logging
• Auditing
• Validating complex business rules that simple constraints cannot do.

Triggers include four key components:

• Trigger events
• Trigger timing
• Trigger function
• Trigger scope

Trigger Events #

Trigger events are events that cause the trigger to fire. In PostgreSQL, the trigger events are:

• INSERT
• UPDATE
• DELETE
• TRUNCATE

For example, inserting a new row into a table will fire a trigger.

Trigger Functions #

Trigger functions are user-defined functions or procedures that execute when triggers fire.

Trigger functions always return a TRIGGER. Inside trigger functions, you can access special variables such as TG_OP, NEW, and OLD.

• The TG_OP variable stores the operation that causes the trigger to fire. It can be 'INSERT', 'UPDATE' ,'DELETE' and 'TRUNCATE'.
• The NEW variable stores the new row. This variable is available only when the operation is 'INSERT' or 'UPDATE'.
• The OLD variable stores the old row, available in the 'UPDATE' or 'DELETE' operation.

Trigger Timing #

Trigger timing specifies the time when the triggers fire:

• BEFORE – Executes the trigger function before the triggering event.
• AFTER – Executes the trigger function after the triggering event.

Trigger Scope #

• Row-level: The trigger function executes for every affected row. For example, if you issue an UPDATE statement that updates 10 rows, the trigger will fire 10 times, each per row.
• Statement-level: The trigger function executes per SQL statement. For example, if you run an UPDATE statement, the statement-level trigger will fire once, regardless of the number of rows updated.

Creating Triggers #

To create a trigger, you follow these steps:

• First, define a user-defined function or procedure to execute when the trigger fires.
• Second, associate the trigger function with a trigger using the CREATE TRIGGER statement.

The CREATE TRIGGER statement allows you to create a new trigger with key trigger components:

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER }
{ INSERT | DELETE | TRUNCATE | UPDATE [OF column_name, ...] }
ON table_name
[ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE { FUNCTION | PROCEDURE } function_name(arguments);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, the CREATE TRIGGER creates a new trigger, while the CREATE OR REPLACE TRIGGER creates a new trigger or replaces an existing one if the trigger already exists.
• Second, specify the trigger’s name (trigger_name) after the CREATE TRIGGER keywords.
• Third, indicate the trigger timing, such as BEFORE or AFTER, and the trigger event, like INSERT, UPDATE, DELETE, and TRUNCATE.
• Fourth, specify the table name with which the trigger associates after the ON keyword.
• Fifth, define the trigger scope, which can be ROW or STATEMENT after the FOR EACH keyword.
• Finally, associate a user-defined function or procedure name with a trigger in the EXECUTE clause.

To create a trigger function, you need to use the PL/pgSQL procedure language.

Creating Trigger Example #

We’ll create a trigger that logs the safety stock changes in the products table:

First, create a table called safety_stock_logs that stores the logging data including product id, old safety stock, new safety stock, and updated time:

CREATE TABLE IF NOT EXISTS safety_stock_logs (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id INT NOT NULL,
  old_safety_stock INT NOT NULL,
  new_safety_stock INT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE CASCADE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Next, create a trigger function that captures the product id, old safety stock, and new safety stock and inserts them into the safety_stock_logs table:

CREATE OR REPLACE FUNCTION log_safety_stock_changes()
RETURNS TRIGGER 
AS 
$$
BEGIN
  IF NEW.safety_stock != OLD.safety_stock THEN
    INSERT INTO safety_stock_logs (product_id, old_safety_stock, new_safety_stock)
    VALUES (OLD.product_id, OLD.safety_stock, NEW.safety_stock);
  END IF;
  RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Then, create a trigger that invokes the log_safety_stock_changes function whenever the safety stock changes:

CREATE OR REPLACE TRIGGER safety_stock_update_trigger
AFTER UPDATE OF safety_stock
ON products
FOR EACH ROW
EXECUTE FUNCTION log_safety_stock_changes();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

After that, update the safety stock for the product with id 1 to 15:

UPDATE products 
SET safety_stock = 15
WHERE product_id = 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The UPDATE statement causes the trigger to fire, which inserts a new row into the safety_stock_logs table.

Finally, retrieve the data from the safety_stock_logs table:

SELECT
  id,
  product_id,
  old_safety_stock,
  new_safety_stock
FROM
  safety_stock_logs;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id | product_id | old_safety_stock | new_safety_stock |
----+------------+------------------+------------------+-
  1 |          1 |               20 |               15 |Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Triggers are user-defined functions or procedures that are called automatically when events such as INSERT, UPDATE, DELETE, and TRUNCATE occur on a table or view.

Quiz #