Using PostgreSQL Triggers Effectively: Beyond the Basics

Pseudo-Types in PostgreSQL

PostgreSQL's type system includes special entries known as pseudo-types. These aren't data types for storing data in columns, but rather serve specific purposes within functions.

The trigger Pseudo-Type

The trigger pseudo-type is used specifically to declare the return type of a trigger function. Trigger functions are special functions in PostgreSQL that automatically execute in response to certain events (like INSERT, UPDATE, or DELETE) on a table.

What the trigger Pseudo-Type Does

• Trigger Function Return Value
  After executing its logic, the trigger function must return a trigger value. This return value doesn't hold any specific data; it simply tells the database engine whether the trigger completed successfully or not.
• Trigger Execution Flow
  When a trigger is fired, its function is called. This function can perform various actions based on the event that triggered it, such as:
  • Modifying data in the table or related tables
  • Logging information
  • Raising errors
• Function Declaration
  When defining a trigger function, you use the trigger pseudo-type to indicate that the function doesn't return a traditional data type like integer or text. Instead, it returns a special value that signifies the success or failure of the trigger execution.

Possible Return Values for a Trigger Function

• TRIGGER_EVENT_CONTINUE: This signifies that the trigger function has processed the event and the original operation should proceed as usual.
• TRIGGER_EVENT_SKIP: This indicates that the trigger function has processed the event and the original operation (INSERT, UPDATE, or DELETE) should be skipped.

Example: A Simple Trigger Function

CREATE FUNCTION log_insert() RETURNS trigger AS $$
BEGIN
  INSERT INTO log_table (table_name, inserted_data)
  VALUES (TG_TABLE_NAME, NEW);
  RETURN trigger_event_continue;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_insert_trigger
AFTER INSERT ON my_table
FOR EACH ROW EXECUTE PROCEDURE log_insert();

In this example:

• The function returns trigger_event_continue to allow the original INSERT operation to proceed.
• Inside the function, it logs the table name and the inserted data to a separate log_table.
• The log_insert function is declared with the trigger pseudo-type.

• Trigger functions can influence the database behavior by modifying data, logging information, or controlling operation flow.
• It dictates the success/failure status of the trigger execution.
• It doesn't represent a data type for storing data.
• The trigger pseudo-type is essential for defining the return type of trigger functions.

---

Enforcing Data Constraints (BEFORE UPDATE)

This trigger prevents updating a product's price to a negative value:

CREATE FUNCTION prevent_negative_price() RETURNS trigger AS $$
BEGIN
  IF NEW.price < 0 THEN
    RAISE EXCEPTION 'Price cannot be negative';
  END IF;
  RETURN trigger_event_continue;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_negative_price_trigger
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE PROCEDURE prevent_negative_price();

Maintaining Audit Trail (AFTER DELETE)

This trigger logs deleted customer information to an audit table:

CREATE FUNCTION log_customer_deletion() RETURNS trigger AS $$
BEGIN
  INSERT INTO customer_audit (customer_id, deleted_at)
  VALUES (OLD.id, CURRENT_TIMESTAMP);
  RETURN trigger_event_continue;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_customer_deletion_trigger
AFTER DELETE ON customers
FOR EACH ROW EXECUTE PROCEDURE log_customer_deletion();

Cascade Delete with Validation (BEFORE DELETE)

This trigger ensures dependent orders are deleted before deleting a customer, preventing orphaned data:

CREATE FUNCTION cascade_delete_orders() RETURNS trigger AS $$
BEGIN
  DELETE FROM orders WHERE customer_id = OLD.id;
  RETURN trigger_event_continue;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cascade_delete_orders_trigger
BEFORE DELETE ON customers
FOR EACH ROW EXECUTE PROCEDURE cascade_delete_orders();

Skipping Duplicate Inserts (BEFORE INSERT)

This trigger checks for existing entries with the same unique identifier and skips insertion if a duplicate is found:

CREATE FUNCTION prevent_duplicate_inserts() RETURNS trigger AS $$
BEGIN
  IF EXISTS (SELECT 1 FROM my_table WHERE unique_id = NEW.unique_id) THEN
    RETURN trigger_event_skip;
  END IF;
  RETURN trigger_event_continue;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_duplicate_inserts_trigger
BEFORE INSERT ON my_table
FOR EACH ROW EXECUTE PROCEDURE prevent_duplicate_inserts();

---

Check Constraints

• Example
  

  CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10, 2) CHECK (price >= 0) -- Enforces non-negative price
  );
  

• Use CHECK constraints within your table definition to enforce data integrity rules at the database level. This prevents invalid data from being inserted in the first place.

Default Values

• Example
  

  CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
  

• Set default values for columns for automatic population during data insertion. This can simplify logic and avoid the need for triggers in some cases.

Stored Procedures

• Example (pseudocode)
  

  CREATE OR REPLACE PROCEDURE validate_and_insert_product(name VARCHAR, price DECIMAL) AS $$
  BEGIN
    IF price < 0 THEN
      RAISE EXCEPTION 'Price cannot be negative';
    END IF;
    INSERT INTO products (name, price) VALUES (name, price);
  END;
  $$ LANGUAGE plpgsql;
  

• Create stored procedures that can be called from your application logic. This approach separates database logic from your application code and allows for better code reuse.

Application-Level Validation

• Advantages
  
  • Improved performance (validation happens outside the database)
  • Easier logic maintenance (logic resides in your application)
• Perform data validation and manipulation within your application code before sending data to the database. This ensures invalid data never reaches the database and reduces reliance on triggers.

Event Triggers (PostgreSQL 10+)

• If you need to react to database events outside of the database itself (e.g., notifying an external system), consider using event triggers (introduced in PostgreSQL 10). These triggers can be written in languages like PL/pgSQL and trigger actions like sending notifications or executing external programs.

The best approach for you depends on your specific requirements. Consider factors like:

• External communication
  Event triggers are useful for actions outside the database.
• Maintainability
  Stored procedures can improve code organization.
• Performance
  Application-level validation might be faster than database triggers.
• Complexity
  Check constraints and default values are often simpler to set up than triggers.