Implement an Audit Log Trigger for DML Operations

Audit Log Trigger using Stored Procedure

Write a PostgreSQL query to create a trigger that calls a stored procedure to log every INSERT, UPDATE, and DELETE operation on a table.

Solution:

-- Create or replace a function named audit_log_function that returns a trigger
CREATE OR REPLACE FUNCTION audit_log_function() RETURNS TRIGGER AS $$
BEGIN
-- Insert a record into the audit_log table with the operation type, table name, and current timestamp
INSERT INTO audit_log (operation, table_name, change_time)
VALUES (TG_OP, TG_TABLE_NAME, NOW());
-- Return the new row for INSERT/UPDATE operations
RETURN NEW;
END;
-- Specify the language used in the function as PL/pgSQL
$$ LANGUAGE plpgsql;
-- Create a trigger named trg_audit_log to log DML operations on target_table
CREATE TRIGGER trg_audit_log
-- Trigger after INSERT, UPDATE, or DELETE operations on target_table
AFTER INSERT OR UPDATE OR DELETE ON target_table
-- Execute the audit_log_function for each affected row
FOR EACH ROW EXECUTE FUNCTION audit_log_function();

Explanation:

• Purpose of the Query:
• The goal is to capture and log every DML operation (INSERT, UPDATE, DELETE) on a table for auditing purposes.
• This demonstrates how to use a stored procedure within a trigger to record changes.
• Key Components:
• The stored procedure uses TG_OP and TG_TABLE_NAME to dynamically capture operation type and table name.
• The trigger is set to fire after DML operations on the target table.
• Real-World Application:
• Useful for maintaining audit trails in systems where data integrity and compliance are critical.

Notes:

• Ensure the audit_log table exists with appropriate columns.
• Triggers are executed for each row affected by the operation.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a trigger that logs DML operations along with the current user and client IP into an audit table.
• Write a PostgreSQL query to create a trigger that logs only UPDATE operations on sensitive columns into an audit table.
• Write a PostgreSQL query to create a trigger that logs both the old and new row values on DELETE and UPDATE events.
• Write a PostgreSQL query to create a trigger that appends a custom message based on the table name during any DML operation.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Using triggers with stored procedures Home.