PostgreSQL AFTER triggers

Triggers in PostgreSQL are functions that automatically execute in response to specific events on a table, such as INSERT, UPDATE, or DELETE. An AFTER trigger is executed after the triggering event has completed, allowing it to access the final state of the data.

Usage

AFTER triggers are used when you need to perform actions after a data modification event, ensuring that any dependent operations occur only after the initial event is successfully completed. They are defined using the CREATE TRIGGER statement.

CREATE TRIGGER trigger_name
AFTER event [OR event ...]
ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();

In this syntax, AFTER event specifies the event (INSERT, UPDATE, DELETE) that activates the trigger, and function_name() is the function to execute after the event.

Examples

1. Basic AFTER INSERT Trigger

CREATE OR REPLACE FUNCTION log_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO user_logs(user_id, action, log_time)
  VALUES (NEW.id, 'User Created', NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION log_new_user();

This example logs a new user's entry into user_logs after an insertion into the users table.

2. AFTER UPDATE Trigger

CREATE OR REPLACE FUNCTION update_inventory_log()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO inventory_logs(product_id, change, log_time)
  VALUES (NEW.product_id, NEW.quantity - OLD.quantity, NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_inventory_update
AFTER UPDATE ON inventory
FOR EACH ROW
EXECUTE FUNCTION update_inventory_log();

This trigger logs the quantity change of a product to inventory_logs after the inventory table is updated.

3. AFTER DELETE Trigger with Conditional Logic

CREATE OR REPLACE FUNCTION log_deleted_order()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.status = 'completed' THEN
    INSERT INTO order_logs(order_id, action, log_time)
    VALUES (OLD.id, 'Completed Order Deleted', NOW());
  END IF;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_order_delete
AFTER DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION log_deleted_order();

This example logs the deletion of completed orders from the orders table, only if the order's status was 'completed'.

Tips and Best Practices

• Use AFTER triggers for dependent operations. Apply them when subsequent actions depend on the completion of the original transaction.
• Keep trigger functions efficient. Ensure that the function executed by the trigger is optimized to prevent slowing down transactions.
• Avoid complex logic. Simplify logic within triggers to maintain clarity and ease of maintenance.
• Test triggers thoroughly. Ensure triggers do not unintentionally affect application logic by testing them comprehensively.
• Document trigger behavior. Keep clear documentation of what each trigger is designed to do, especially in larger applications.
• Monitor performance impact. Use logging or monitoring tools to track the performance impact of triggers in production databases.
• Understand transaction interactions. AFTER triggers are part of the transaction; if an error occurs in the trigger function, the entire transaction may be rolled back.
• Consider limitations. Be aware of potential conflicts with foreign key constraints or cascading actions when using AFTER triggers.
• Manage trigger lifecycle. Know how to disable or drop a trigger when it is no longer needed using ALTER TABLE or DROP TRIGGER.