PostgreSQL TRIGGER: Modify data at INSERT, UPDATE, and DELETE

Introduction

PostgreSQL triggers offer a robust mechanism to react and modify data during INSERT, UPDATE, and DELETE operations, allowing for complex business logic to be embedded within the database layer.

Understanding PostgreSQL Triggers

Triggers in PostgreSQL are database callback functions that are automatically performed, or triggered, when certain events occur on a table or view. Common use cases for triggers include enforcing business rules, auditing, and maintaining complex data integrity.

To create a trigger, first, you need to define a trigger function using the CREATE FUNCTION statement. This function—written in PL/pgSQL (Postgres procedural language)—defines the operations to be carried out when the trigger fires. Next, you associate this function with a specific event on a table using the CREATE TRIGGER statement.

Creating a Basic Trigger Function

CREATE FUNCTION example_trigger_function() RETURNS trigger AS $
BEGIN
  -- Trigger logic goes here
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

Here, RETURNS trigger signifies that the functions is a trigger function, and RETURNS NEW means that the function will return the modified row for INSERT and UPDATE triggers, or NULL for DELETE triggers.

Attaching the Trigger to a Table Event

CREATE TRIGGER example_trigger
AFTER INSERT ON your_table
FOR EACH ROW
EXECUTE FUNCTION example_trigger_function();

This statement creates a trigger named example_trigger that fires after each row is inserted into your_table. The EXECUTE FUNCTION clause specifies the trigger function to run.

Working with INSERT Triggers

INSERT triggers are invoked whenever new rows are added to a table. They can be defined to run before or after the actual insert operation.

Before Insert Trigger Example

CREATE FUNCTION before_insert_function() RETURNS trigger AS $
BEGIN
  -- Automatically set a timestamp before inserting a new row
  NEW.created_at := current_timestamp;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON your_table
FOR EACH ROW
EXECUTE FUNCTION before_insert_function();

In this example, the before_insert_trigger automatically sets the created_at column to the current timestamp before a new row is inserted into your_table.

Manipulating Data with UPDATE Triggers

UPDATE triggers can take actions when a row in a watched table is modified. You can control which columns’ modifications should fire the trigger.

Simple Update Trigger Example

CREATE FUNCTION update_trigger_function() RETURNS trigger AS $
BEGIN
  -- Check if the 'price' column was updated
  IF OLD.price <> NEW.price THEN
    NEW.last_updated := current_timestamp;
    -- Additional logic for price change...
  END IF;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER update_price_trigger
AFTER UPDATE OF price ON your_table
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
EXECUTE FUNCTION update_trigger_function();

This update trigger updates the last_updated column whenever there is a change in the price column of a row.

Using DELETE Triggers

DELETE triggers fire when a row is being deleted. These are often used for audit purposes or to cascade custom delete actions.

Basic Delete Trigger Example

CREATE FUNCTION delete_trigger_function() RETURNS trigger AS $
BEGIN
  -- Record deletion activity to an audit table
  INSERT INTO audit_table(operation, old_data, timestamp)
  VALUES ('DELETE', ROW(OLD.*), current_timestamp);
  RETURN OLD;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER delete_audit_trigger
AFTER DELETE ON your_table
FOR EACH ROW
EXECUTE FUNCTION delete_trigger_function();

This trigger function copies deleted rows into an audit_table to keep a record of deletions.

Advanced Trigger Concepts

PostgreSQL triggers can be crafted to handle more sophisticated scenarios, such as working with conditional triggers, using transition tables, and executing dynamic commands.

Conditional Trigger Execution

CREATE TRIGGER advanced_condition_trigger
BEFORE UPDATE ON sales
FOR EACH ROW
WHEN (NEW.revenue > OLD.revenue)
EXECUTE FUNCTION increase_counter_function();

This trigger only executes if the revenue of a sale increases compared to its previous value.

Transition Tables

CREATE TRIGGER summarizing_trigger
AFTER UPDATE ON large_table
REFERENCING NEW TABLE AS new_data
FOR EACH STATEMENT
EXECUTE FUNCTION summarize_changes_function();

Transition tables allow triggers to process all changed rows in bulk rather than one at a time, which can be more efficient for batch operations.

Conclusion

PostgreSQL triggers are powerful tools for maintaining data integrity and automating complex database operations. While they may initially appear daunting, mastering them will help you create more robust, self-maintaining database systems. Invest time into understanding their nuances, and you’ll be rewarded with cleaner, more reliable data management.