Row-level triggers for audit logs in Supabase - Mini Project: Build & Apply

Row-level triggers for audit logs

📖 Scenario: You manage a database in Supabase for a small online store. You want to keep track of every change made to the orders table. This helps you see who changed what and when.

🎯 Goal: Create a row-level trigger in Supabase that logs every insert, update, and delete on the orders table into an orders_audit table.

📋 What You'll Learn

Create an orders_audit table with columns to store order ID, operation type, changed data, and timestamp.

Write a trigger function called log_order_changes that inserts audit records on insert, update, and delete.

Create a row-level trigger on the orders table that calls log_order_changes after each insert, update, or delete.

💡 Why This Matters

🌍 Real World

Audit logs are essential in real-world databases to track changes, meet compliance, and troubleshoot issues.

💼 Career

Database administrators and backend developers often implement triggers for auditing and data integrity.

1

Create the orders_audit table

Create a table called orders_audit with these columns: audit_id as a serial primary key, order_id as integer, operation as text, changed_data as jsonb, and changed_at as timestamp with time zone defaulting to now().

Supabase

-- Your code here

Hint

Use SERIAL PRIMARY KEY for audit_id. Use TIMESTAMPTZ DEFAULT now() for the timestamp.

2

Create the trigger function log_order_changes

Write a trigger function called log_order_changes in PL/pgSQL that inserts a row into orders_audit. It should detect the operation type using TG_OP. For INSERT and UPDATE, use NEW row data. For DELETE, use OLD row data. Insert order_id, operation, and changed_data as JSON.

Supabase

CREATE TABLE orders_audit (
  audit_id SERIAL PRIMARY KEY,
  order_id INTEGER,
  operation TEXT,
  changed_data JSONB,
  changed_at TIMESTAMPTZ DEFAULT now()
);

-- Write the trigger function log_order_changes here

Hint

Use TG_OP to check the operation type. Use to_jsonb(NEW) or to_jsonb(OLD) to store changed data.

3

Create the row-level trigger on orders

Create a trigger called orders_audit_trigger on the orders table. It should fire AFTER INSERT OR UPDATE OR DELETE for each row and call the log_order_changes function.

Supabase

CREATE TABLE orders_audit (
  audit_id SERIAL PRIMARY KEY,
  order_id INTEGER,
  operation TEXT,
  changed_data JSONB,
  changed_at TIMESTAMPTZ DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_order_changes() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO orders_audit(order_id, operation, changed_data) VALUES (NEW.id, TG_OP, to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO orders_audit(order_id, operation, changed_data) VALUES (NEW.id, TG_OP, to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO orders_audit(order_id, operation, changed_data) VALUES (OLD.id, TG_OP, to_jsonb(OLD));
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger orders_audit_trigger on orders table here

Hint

Use CREATE TRIGGER with AFTER INSERT OR UPDATE OR DELETE and FOR EACH ROW.

4

Verify the trigger setup

Add a comment at the end of your SQL script confirming that the trigger orders_audit_trigger is created and ready to log changes.

Supabase

CREATE TABLE orders_audit (
  audit_id SERIAL PRIMARY KEY,
  order_id INTEGER,
  operation TEXT,
  changed_data JSONB,
  changed_at TIMESTAMPTZ DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_order_changes() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO orders_audit(order_id, operation, changed_data) VALUES (NEW.id, TG_OP, to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO orders_audit(order_id, operation, changed_data) VALUES (NEW.id, TG_OP, to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO orders_audit(order_id, operation, changed_data) VALUES (OLD.id, TG_OP, to_jsonb(OLD));
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION log_order_changes();

-- Add a confirmation comment here

Hint

Just add a comment line confirming the trigger is ready.

Practice

1. What is the main purpose of a row-level trigger in Supabase for audit logs?

easy

A. To automatically record changes made to individual rows in a table

B. To speed up database queries by caching results

C. To create new tables automatically when data is inserted

D. To delete old data from the database periodically

Solution

1. Step 1: Understand what row-level triggers do

   Row-level triggers activate for each row affected by an insert, update, or delete operation.

2. Step 2: Connect triggers to audit logs

   They are used to record changes to rows, capturing who changed what and when.

3. Final Answer:

   To automatically record changes made to individual rows in a table -> Option A

4. Quick Check:

   Row-level trigger purpose = record row changes [OK]

Hint: Triggers log row changes automatically for audit [OK]

Common Mistakes:

• Thinking triggers speed up queries
• Confusing triggers with table creation
• Assuming triggers delete old data

2. Which of the following is the correct syntax to create a row-level trigger in Supabase that fires AFTER an UPDATE on a table named users?

easy

A. CREATE TRIGGER user_update AFTER INSERT ON users FOR EACH ROW EXECUTE FUNCTION audit_log();

B. CREATE TRIGGER user_update BEFORE UPDATE ON users FOR EACH STATEMENT EXECUTE FUNCTION audit_log();

C. CREATE TRIGGER user_update AFTER UPDATE ON users FOR EACH ROW EXECUTE FUNCTION audit_log();

D. CREATE TRIGGER user_update AFTER UPDATE ON users FOR EACH STATEMENT EXECUTE FUNCTION audit_log();

Solution

1. Step 1: Identify trigger timing and event

   The question asks for AFTER UPDATE trigger on table users.

2. Step 2: Confirm row-level execution

   Row-level triggers use FOR EACH ROW, not FOR EACH STATEMENT.

3. Final Answer:

   CREATE TRIGGER user_update AFTER UPDATE ON users FOR EACH ROW EXECUTE FUNCTION audit_log(); -> Option C

4. Quick Check:

   AFTER UPDATE + FOR EACH ROW = CREATE TRIGGER user_update AFTER UPDATE ON users FOR EACH ROW EXECUTE FUNCTION audit_log(); [OK]

Hint: Use AFTER UPDATE and FOR EACH ROW for row-level update triggers [OK]

Common Mistakes:

• Using BEFORE instead of AFTER
• Using FOR EACH STATEMENT instead of FOR EACH ROW
• Using INSERT event instead of UPDATE

3. Given this trigger function snippet in Supabase SQL:

CREATE OR REPLACE FUNCTION audit_log() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table(user_id, action, changed_at)
  VALUES (NEW.id, TG_OP, NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

What will happen when a row in the users table is updated?

medium

A. A new row is inserted into audit_table with the user's id, action type, and timestamp

B. The updated row in users is deleted

C. The trigger function will cause a syntax error and fail

D. Nothing happens because OLD is not used

Solution

1. Step 1: Analyze the trigger function logic

   The function inserts a row into audit_table with NEW.id, operation type TG_OP, and current time.

2. Step 2: Understand trigger behavior on update

   On update, NEW holds the new row data, so the insert logs the change.

3. Final Answer:

   A new row is inserted into audit_table with the user's id, action type, and timestamp -> Option A

4. Quick Check:

   Trigger inserts audit record on update = A new row is inserted into audit_table with the user's id, action type, and timestamp [OK]

Hint: Trigger inserts audit row using NEW and TG_OP on update [OK]

Common Mistakes:

• Thinking OLD is required for insert
• Assuming trigger deletes rows
• Confusing syntax causing errors

4. You wrote this trigger function to log deletes:

CREATE OR REPLACE FUNCTION audit_log_delete() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table(user_id, action, changed_at)
  VALUES (NEW.id, TG_OP, NOW());
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

But it fails when deleting rows. What is the error?

medium

A. TG_OP cannot be used in trigger functions

B. RETURN OLD is invalid in DELETE triggers

C. NOW() is not allowed in trigger functions

D. Using NEW.id on DELETE triggers causes an error because NEW is NULL

Solution

1. Step 1: Understand DELETE trigger context

   On DELETE, NEW is NULL because the row is removed; OLD holds the deleted row.

2. Step 2: Identify the error in the function

   The function uses NEW.id which is NULL on DELETE, causing failure.

3. Final Answer:

   Using NEW.id on DELETE triggers causes an error because NEW is NULL -> Option D

4. Quick Check:

   DELETE triggers use OLD, not NEW [OK]

Hint: Use OLD, not NEW, in DELETE triggers to avoid errors [OK]

Common Mistakes:

• Using NEW in DELETE triggers
• Thinking RETURN OLD is invalid
• Believing TG_OP or NOW() cause errors

5. You want to create an audit log that records who updated a row in the orders table, including old and new values. Which approach correctly combines row-level triggers and audit logging in Supabase?

hard

A. Create a trigger function that only logs NEW data and create a BEFORE INSERT trigger on orders

B. Create a trigger function that inserts into audit_orders using both OLD and NEW row data, then create AFTER UPDATE trigger FOR EACH ROW on orders

C. Create a trigger function that deletes old rows from orders and logs the deletion

D. Create a trigger function that logs changes only when rows are inserted, ignoring updates

Solution

1. Step 1: Identify audit log needs

   You want to log who updated rows and capture old and new values for changes.

2. Step 2: Choose correct trigger timing and data

   Use AFTER UPDATE trigger FOR EACH ROW to access OLD and NEW data and insert audit record.

3. Step 3: Confirm audit table insert logic

   The trigger function should insert both OLD and NEW values into audit_orders for full change history.

4. Final Answer:

   Create a trigger function that inserts into audit_orders using both OLD and NEW row data, then create AFTER UPDATE trigger FOR EACH ROW on orders -> Option B

5. Quick Check:

   Use AFTER UPDATE + OLD/NEW + FOR EACH ROW for full audit [OK]

Hint: Use AFTER UPDATE trigger with OLD and NEW for full audit logs [OK]

Common Mistakes:

• Logging only NEW data misses old values
• Using BEFORE INSERT triggers for updates
• Deleting rows instead of logging changes