Automating Tasks with Triggers

Imagine you're the admin of a huge database. Suddenly, someone deletes some important info from a table, and everyone yells, "Who did this?!" To avoid these situations, databases let you record changes and track what happened to your data. This is all done with logging and auditing.

• Change logging lets you keep a history of what happened: which record changed, how, and when.
• Data audit is used for a deeper check, including not just the changes, but also info about the user who made them.

Now that you get the "why," let's learn the "how."

Creating a Log Table

Before we start setting up triggers, we need a table to store our change logs. Here's an example:

-- Creating a table for logging changes
CREATE TABLE change_logs (
    log_id SERIAL PRIMARY KEY,       -- Unique record ID
    table_name TEXT NOT NULL,        -- Name of the table where the change happened
    operation TEXT NOT NULL,         -- Operation type: INSERT, UPDATE, DELETE
    change_time TIMESTAMP DEFAULT NOW(), -- Time of the change
    old_data JSONB,                  -- Data before the change (for UPDATE/DELETE)
    new_data JSONB                   -- Data after the change (for INSERT/UPDATE)
);

What's going on here?

1. log_id — unique ID for each log record.
2. table_name — we'll record which table was changed.
3. operation — operation type: INSERT, UPDATE, or DELETE.
4. change_time — logs the exact time of the change.
5. old_data and new_data — data before and after the change in JSON format.

Logging Changes with a Trigger

Now that we've got a log table, let's create a trigger for one of our tables, say, students. It'll log all changes: adding new students, updating them, or deleting them. Here's what we're gonna do:

1. Write a PL/pgSQL function that adds records to the log table.
2. Create a trigger on the students table.

The function will get info about the operation (INSERT, UPDATE, DELETE), plus the data that changed (OLD and NEW).

-- Function to log changes into the log table
CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Logging INSERT operation
    IF TG_OP = 'INSERT' THEN
        INSERT INTO change_logs (table_name, operation, new_data)
        VALUES ('students', 'INSERT', row_to_json(NEW));

    -- Logging DELETE operation
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO change_logs (table_name, operation, old_data)
        VALUES ('students', 'DELETE', row_to_json(OLD));

    -- Logging UPDATE operation
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO change_logs (table_name, operation, old_data, new_data)
        VALUES ('students', 'UPDATE', row_to_json(OLD), row_to_json(NEW));
    END IF;

    RETURN NULL; -- Return NULL since this is an AFTER trigger
END;
$$ LANGUAGE plpgsql;

Here's what's up:

• TG_OP — a special variable that holds the current operation: INSERT, UPDATE, DELETE.
• row_to_json(OLD) and row_to_json(NEW) — turn the row data into JSON for easy storage.
• RETURN NULL — since this is an AFTER trigger, it shouldn't return changed data.

Now let's hook up our function to the students table.

-- Creating a trigger for logging changes
CREATE TRIGGER students_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_changes();

What's happening here?

• AFTER INSERT OR UPDATE OR DELETE — the trigger fires after any of these operations on the students table.
• FOR EACH ROW — the trigger runs for every changed row.
• EXECUTE FUNCTION log_student_changes() — calls our logging function.

Testing the Trigger

Time to check if our trigger works.

1. Inserting a new record

INSERT INTO students (name, age, grade)
VALUES ('Otto Lin', 20, 'A');

Let's see what got logged:

SELECT * FROM change_logs;

Example result:

2. Updating a record

UPDATE students
SET grade = 'B'
WHERE name = 'Otto Lin';

Let's check the log table again:

SELECT * FROM change_logs ORDER BY change_time DESC;

Result:

3. Deleting a record

DELETE FROM students
WHERE name = 'Otto Lin';

And again, let's check the log:

Real-World Usage Examples

1. Logging operations on mission-critical tables: for example, a table with bank accounts needs to log all changes to prevent fraud.
2. System audit: you can keep records for compliance or to analyze user activity.
3. Making sure you can restore data: if someone accidentally deletes data, you can restore it from the log table.

Things to Watch Out For

When you set up logging with triggers, you gotta think about performance. If the trigger fires super often, it can put extra load on your database. So:

• Only use logging on really important tables.
• If your logs get too big, come up with some archiving strategies.

Triggers are like a guitar string: they need to be tuned just right, but when they are, they automate boring stuff and give you awesome control over your data.