Trigger performance considerations in SQL - Mini Project: Build & Apply

Trigger Performance Considerations

📖 Scenario: You are managing a small online store database. You want to use triggers to automatically update stock quantities when orders are placed. However, you also want to keep your database fast and responsive.

🎯 Goal: Build a simple trigger setup that updates stock quantities after an order is inserted, while considering performance by limiting the trigger's work.

📋 What You'll Learn

Create a table called products with columns product_id (integer) and stock (integer).

Create a table called orders with columns order_id (integer), product_id (integer), and quantity (integer).

Create a trigger function called update_stock that subtracts the ordered quantity from the product stock.

Create a trigger called after_order_insert that calls update_stock after a new order is inserted.

Ensure the trigger only updates stock for the product in the new order to keep performance efficient.

💡 Why This Matters

🌍 Real World

Triggers help automate database tasks like updating stock levels when orders change, saving manual work and reducing errors.

💼 Career

Understanding trigger performance is important for database administrators and developers to keep applications fast and reliable.

1

Create the products and orders tables

Create a table called products with columns product_id as integer primary key and stock as integer. Also create a table called orders with columns order_id as integer primary key, product_id as integer, and quantity as integer.

SQL

-- Create products and orders tables
-- Your code here

Need a hint?

Use CREATE TABLE statements with the exact column names and types.

2

Create the trigger function update_stock

Create a trigger function called update_stock that subtracts NEW.quantity from the stock of the product with product_id = NEW.product_id in the products table.

SQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  stock INT
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT,
  quantity INT
);

-- Create trigger function update_stock
-- Your code here

Need a hint?

Use a trigger function in PL/pgSQL that updates only the affected product's stock.

3

Create the trigger after_order_insert

Create a trigger called after_order_insert on the orders table that calls the update_stock function after each row is inserted.

SQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  stock INT
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT,
  quantity INT
);

CREATE FUNCTION update_stock() RETURNS trigger AS $$
BEGIN
  UPDATE products
  SET stock = stock - NEW.quantity
  WHERE product_id = NEW.product_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger after_order_insert
-- Your code here

Need a hint?

Use CREATE TRIGGER with AFTER INSERT and FOR EACH ROW to call the trigger function.

4

Ensure trigger updates only the affected product

Verify that the trigger function updates stock only for the product with product_id = NEW.product_id to keep performance efficient.

SQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  stock INT
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT,
  quantity INT
);

CREATE FUNCTION update_stock() RETURNS trigger AS $$
BEGIN
  UPDATE products
  SET stock = stock - NEW.quantity
  WHERE product_id = NEW.product_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

-- Confirm WHERE clause limits update to NEW.product_id
-- Your code here

Need a hint?

Check that the UPDATE statement has a WHERE clause filtering by NEW.product_id.