Database triggers for automation in Supabase - Mini Project: Build & Apply

Database triggers for automation

📖 Scenario: You are managing a simple online store database using Supabase. You want to automate the process of updating the last_order_date for customers whenever they place a new order.

🎯 Goal: Create a database trigger in Supabase that automatically updates the last_order_date column in the customers table whenever a new order is inserted into the orders table.

📋 What You'll Learn

Create a customers table with id, name, and last_order_date columns

Create an orders table with id, customer_id, and order_date columns

Create a trigger function that updates last_order_date in customers when a new order is inserted

Create a trigger on orders that calls the trigger function after insert

💡 Why This Matters

🌍 Real World

Automating database updates saves time and reduces errors in real-world applications like online stores, where customer data must stay current.

💼 Career

Understanding database triggers is essential for backend developers and database administrators to implement automation and maintain data integrity.

1

Create the customers and orders tables

Write SQL commands to create a table called customers with columns id (primary key, integer), name (text), and last_order_date (timestamp). Also create a table called orders with columns id (primary key, integer), customer_id (integer), and order_date (timestamp).

Supabase

-- Your SQL code here to create customers and orders tables

Hint

Use SERIAL PRIMARY KEY for auto-incrementing IDs. Use REFERENCES customers(id) to link customer_id to customers.

2

Create the trigger function to update last_order_date

Write a SQL function called update_last_order_date that updates the last_order_date column in the customers table to the order_date of the newly inserted order. Use NEW.customer_id and NEW.order_date to access the inserted row values.

Supabase

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

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  order_date TIMESTAMP NOT NULL
);

-- Write the trigger function update_last_order_date here

Hint

Use CREATE OR REPLACE FUNCTION with RETURNS TRIGGER. Use NEW to access the inserted row.

3

Create the trigger on orders table

Write a SQL command to create a trigger called after_order_insert on the orders table. This trigger should fire AFTER INSERT and call the update_last_order_date function.

Supabase

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

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  order_date TIMESTAMP NOT NULL
);

CREATE OR REPLACE FUNCTION update_last_order_date()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE customers
  SET last_order_date = NEW.order_date
  WHERE id = NEW.customer_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger after_order_insert on orders table here

Hint

Use CREATE TRIGGER with AFTER INSERT ON orders and FOR EACH ROW. Call the function with EXECUTE FUNCTION update_last_order_date().

4

Verify the trigger by inserting a new order

Write a SQL command to insert a new order into the orders table with customer_id 1 and order_date '2024-06-01 10:00:00'. This will test if the trigger updates the last_order_date in customers.

Supabase

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

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  order_date TIMESTAMP NOT NULL
);

CREATE OR REPLACE FUNCTION update_last_order_date()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE customers
  SET last_order_date = NEW.order_date
  WHERE id = NEW.customer_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION update_last_order_date();

INSERT INTO customers (name) VALUES ('Customer 1');

-- Insert a new order with customer_id 1 and order_date '2024-06-01 10:00:00' here

Hint

Use INSERT INTO orders (customer_id, order_date) VALUES (1, '2024-06-01 10:00:00') to add the new order.

Practice

1. What is the main purpose of a database trigger in Supabase?

easy

A. To manually update data in the database

B. To automatically run a function when data changes in a table

C. To create a backup of the database

D. To increase the database storage size

Solution

1. Step 1: Understand what triggers do

   Triggers run automatically when data changes like insert, update, or delete.

2. Step 2: Identify the purpose in Supabase

   Supabase triggers automate running functions on data changes without manual intervention.

3. Final Answer:

   To automatically run a function when data changes in a table -> Option B

4. Quick Check:

   Trigger = auto-run function on data change [OK]

Hint: Triggers run code automatically on data changes [OK]

Common Mistakes:

• Thinking triggers require manual activation
• Confusing triggers with backups
• Assuming triggers increase storage

2. Which of the following is the correct syntax to create a trigger in Supabase SQL?

easy

A. CREATE TRIGGER trigger_name WHEN INSERT ON table_name EXECUTE function_name();

B. CREATE TRIGGER trigger_name BEFORE UPDATE table_name CALL function_name();

C. CREATE TRIGGER trigger_name ON table_name AFTER DELETE RUN function_name();

D. CREATE TRIGGER trigger_name AFTER INSERT ON table_name EXECUTE FUNCTION function_name();

Solution

1. Step 1: Recall correct trigger syntax in PostgreSQL (used by Supabase)

   The correct syntax uses CREATE TRIGGER, timing (AFTER/BEFORE), event (INSERT/UPDATE/DELETE), ON table, and EXECUTE FUNCTION.

2. Step 2: Match options to syntax

   CREATE TRIGGER trigger_name AFTER INSERT ON table_name EXECUTE FUNCTION function_name(); matches the correct syntax: CREATE TRIGGER name AFTER INSERT ON table EXECUTE FUNCTION func(); others use wrong keywords like CALL, RUN, or WHEN.

3. Final Answer:

   CREATE TRIGGER trigger_name AFTER INSERT ON table_name EXECUTE FUNCTION function_name(); -> Option D

4. Quick Check:

   CREATE TRIGGER ... EXECUTE FUNCTION ... [OK]

Hint: Use EXECUTE FUNCTION, not CALL or RUN in triggers [OK]

Common Mistakes:

• Using CALL or RUN instead of EXECUTE FUNCTION
• Missing ON keyword before table name
• Using WHEN instead of BEFORE or AFTER

3. Given this trigger function in Supabase SQL:

CREATE FUNCTION log_insert() RETURNS trigger AS $$ BEGIN INSERT INTO logs(table_name, action) VALUES (TG_TABLE_NAME, 'insert'); RETURN NEW; END; $$ LANGUAGE plpgsql;

And this trigger:

CREATE TRIGGER after_insert_log AFTER INSERT ON users EXECUTE FUNCTION log_insert();

What happens when a new row is inserted into the users table?

medium

A. A new row is added to the logs table recording the insert action on users

B. The insert into users is blocked and logs table is updated

C. Nothing happens because the trigger is AFTER INSERT

D. The users table row is deleted after insert

Solution

1. Step 1: Understand the trigger function behavior

   The function inserts a row into logs with the table name and action 'insert' whenever triggered.

2. Step 2: Understand the trigger timing and event

   The trigger runs AFTER INSERT on users, so after a new user row is added, the function runs and logs the action.

3. Final Answer:

   A new row is added to the logs table recording the insert action on users -> Option A

4. Quick Check:

   AFTER INSERT triggers log insert action [OK]

Hint: AFTER INSERT triggers run after data is added [OK]

Common Mistakes:

• Thinking AFTER INSERT blocks the insert
• Assuming trigger does nothing
• Confusing trigger timing with BEFORE

4. You wrote this trigger function:

CREATE FUNCTION update_timestamp() RETURNS trigger AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

And this trigger:

CREATE TRIGGER update_time BEFORE UPDATE ON orders EXECUTE FUNCTION update_timestamp();

But the updated_at column is not changing on updates. What is the likely problem?

medium

A. The trigger is AFTER UPDATE instead of BEFORE UPDATE

B. The trigger function is missing RETURN OLD

C. The trigger function does not modify NEW row correctly

D. The trigger function is not marked as VOLATILE

Solution

1. Step 1: Check how NEW row is modified in trigger

   The function sets NEW.updated_at = NOW() but does not assign it back properly; in PL/pgSQL, you must assign NEW.updated_at and then RETURN NEW.

2. Step 2: Confirm trigger timing and return

   The trigger is BEFORE UPDATE and returns NEW, which is correct. The problem is likely the function does not properly update the NEW record.

3. Final Answer:

   The trigger function does not modify NEW row correctly -> Option C

4. Quick Check:

   Modify NEW row correctly in BEFORE UPDATE triggers [OK]

Hint: Always RETURN NEW after modifying it in BEFORE UPDATE triggers [OK]

Common Mistakes:

• Not returning NEW after changes
• Using AFTER UPDATE instead of BEFORE UPDATE
• Returning OLD instead of NEW

5. You want to automatically archive deleted rows from the products table into products_archive using a trigger. Which approach correctly achieves this in Supabase?

hard

A. Create an AFTER DELETE trigger on products that inserts OLD row into products_archive

B. Create a BEFORE DELETE trigger on products that deletes from products_archive

C. Create an AFTER INSERT trigger on products_archive that copies rows to products

D. Create a BEFORE UPDATE trigger on products that moves rows to products_archive

Solution

1. Step 1: Understand the goal

   You want to save deleted rows from products into products_archive automatically.

2. Step 2: Choose correct trigger timing and action

   AFTER DELETE trigger on products can access OLD row data and insert it into products_archive. BEFORE DELETE is too early to archive after deletion. Other options do not archive deleted rows.

3. Final Answer:

   Create an AFTER DELETE trigger on products that inserts OLD row into products_archive -> Option A

4. Quick Check:

   AFTER DELETE triggers archive OLD row [OK]

Hint: Use AFTER DELETE triggers to archive deleted rows [OK]

Common Mistakes:

• Using BEFORE DELETE to archive after deletion
• Confusing insert and delete triggers
• Trying to archive on update instead of delete