Trigger performance considerations in SQL

Introduction

Triggers automatically run code when data changes. Understanding their performance helps keep your database fast and smooth.

When you want to automatically check or change data after insert, update, or delete.

When you need to keep data consistent without extra manual steps.

When you want to log changes in data for tracking.

When you want to enforce rules that can't be done with simple constraints.

When you want to avoid slowing down your database with heavy trigger code.

Syntax

SQL

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
  -- trigger code here
END;

Triggers run automatically after or before data changes.

Keep trigger code simple to avoid slowing down database operations.

Examples

This trigger logs every new employee added.

SQL

CREATE TRIGGER log_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (action, emp_id) VALUES ('insert', NEW.id);
END;

This trigger updates the timestamp before an order changes.

SQL

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
  SET NEW.updated_at = CURRENT_TIMESTAMP;
END;

Sample Program

This example creates a trigger that logs inserts into employees. When we add Alice, the audit_log records it.

SQL

CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE audit_log (action VARCHAR(10), emp_id INT);

CREATE TRIGGER log_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (action, emp_id) VALUES ('insert', NEW.id);
END;

INSERT INTO employees (id, name) VALUES (1, 'Alice');
SELECT * FROM audit_log;

Important Notes

Triggers run inside the same transaction as the data change, so slow triggers slow down inserts/updates/deletes.

Avoid complex queries or loops inside triggers to keep performance good.

Test triggers carefully to prevent unexpected slowdowns or errors.

Summary

Triggers run automatically on data changes to help automate tasks.

Keep trigger code simple and fast to avoid slowing your database.

Use triggers wisely to maintain data integrity and logging.

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