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

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

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

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

Hint

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

Practice

1. What is the main reason to keep SQL triggers simple and fast?

easy

A. To avoid slowing down database operations

B. To make triggers run only once a day

C. To allow triggers to modify unrelated tables

D. To enable triggers to run without any conditions

Solution

1. Step 1: Understand trigger execution timing

   Triggers run automatically during data changes, so their speed affects overall database performance.

2. Step 2: Consider impact of complex triggers

   Complex or slow triggers can delay data operations, causing performance issues.

3. Final Answer:

   To avoid slowing down database operations -> Option A

4. Quick Check:

   Trigger speed affects database speed = A [OK]

Hint: Fast triggers keep database operations smooth [OK]

Common Mistakes:

• Thinking triggers run only once a day
• Assuming triggers can modify any table without limits
• Believing triggers run without conditions

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

easy

A. CREATE TRIGGER trg AFTER INSERT employees BEGIN UPDATE stats SET count = count + 1; END;

B. CREATE TRIGGER trg AFTER INSERT ON employees FOR EACH ROW BEGIN UPDATE stats SET count = count + 1; END;

C. CREATE TRIGGER trg BEFORE UPDATE ON employees FOR EACH ROW UPDATE stats SET count = count + 1;

D. CREATE TRIGGER trg ON employees AFTER INSERT BEGIN UPDATE stats SET count = count + 1; END;

Solution

1. Step 1: Check trigger timing and event

   The trigger is AFTER INSERT on the employees table, so syntax must include 'AFTER INSERT ON employees'.

2. Step 2: Verify trigger body structure

   The trigger body must be enclosed in BEGIN ... END; and include FOR EACH ROW for row-level triggers.

3. Final Answer:

   CREATE TRIGGER trg AFTER INSERT ON employees FOR EACH ROW BEGIN UPDATE stats SET count = count + 1; END; -> Option B

4. Quick Check:

   Correct syntax includes timing, event, table, and body [OK]

Hint: Look for 'AFTER INSERT ON table FOR EACH ROW' syntax [OK]

Common Mistakes:

• Omitting 'ON' keyword before table name
• Missing 'FOR EACH ROW' for row-level triggers
• Incorrect order of keywords

3. Given this trigger code:

CREATE TRIGGER trg_update AFTER UPDATE ON orders FOR EACH ROW BEGIN UPDATE inventory SET stock = stock - NEW.quantity WHERE product_id = NEW.product_id; END;

What happens if an update changes 10 units of product_id 5 in orders?

medium

A. The inventory stock for product_id 5 increases by 10

B. No change happens to inventory stock

C. The inventory stock for product_id 5 decreases by 10

D. The trigger causes a syntax error

Solution

1. Step 1: Understand trigger action on update

   The trigger runs AFTER UPDATE on orders and adjusts inventory stock by subtracting NEW.quantity.

2. Step 2: Apply the update values

   For product_id 5, stock is reduced by 10 units as NEW.quantity is 10.

3. Final Answer:

   The inventory stock for product_id 5 decreases by 10 -> Option C

4. Quick Check:

   Trigger subtracts NEW.quantity from stock = B [OK]

Hint: NEW.quantity subtracts from stock after update [OK]

Common Mistakes:

• Confusing NEW with OLD values
• Assuming stock increases instead of decreases
• Thinking trigger causes syntax error

4. You wrote this trigger but it causes a runtime error:

CREATE TRIGGER trg_log AFTER INSERT ON sales FOR EACH ROW BEGIN INSERT INTO sales_log VALUES (NEW.id, NEW.amount); UPDATE sales SET amount = amount * 1.1 WHERE id = NEW.id; END;

What is the likely cause of the error?

medium

A. Trigger tries to update the same table it is triggered on, causing recursion

B. Missing semicolon after INSERT statement

C. NEW keyword cannot be used in AFTER INSERT triggers

D. sales_log table does not exist

Solution

1. Step 1: Identify trigger actions

   The trigger inserts into sales_log and then updates sales table, which is the same table that fired the trigger.

2. Step 2: Understand recursion risk

   Updating sales inside an AFTER INSERT trigger on sales causes the trigger to fire again, leading to infinite recursion and runtime error.

3. Final Answer:

   Trigger tries to update the same table it is triggered on, causing recursion -> Option A

4. Quick Check:

   Updating trigger table inside trigger causes recursion error [OK]

Hint: Avoid updating trigger table inside its own trigger [OK]

Common Mistakes:

• Ignoring recursion from updating same table
• Assuming missing semicolon causes runtime error
• Believing NEW is invalid in AFTER INSERT triggers

5. You want to log every change to the customers table without slowing down transactions. Which approach best improves trigger performance?

hard

A. Use triggers to update multiple related tables during each change

B. Use a BEFORE UPDATE trigger that performs complex calculations before logging

C. Use multiple triggers on the same event to split logging tasks

D. Use a simple AFTER UPDATE trigger that inserts minimal data into a log table

Solution

1. Step 1: Consider trigger timing and complexity

   AFTER UPDATE triggers run after the change, so they don't block the main transaction as much as BEFORE triggers.

2. Step 2: Keep trigger code minimal

   Inserting only essential data into a log table keeps the trigger fast and reduces performance impact.

3. Final Answer:

   Use a simple AFTER UPDATE trigger that inserts minimal data into a log table -> Option D

4. Quick Check:

   Simple AFTER triggers with minimal work improve performance [OK]

Hint: Keep triggers simple and after event for better speed [OK]

Common Mistakes:

• Using BEFORE triggers with complex logic
• Splitting logging into multiple triggers causing overhead
• Updating many tables inside triggers