Trigger performance considerations in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Impact of complex logic inside triggers

What is the main performance concern when a trigger contains complex logic that runs on every row affected by a data modification?

A The trigger may cause significant slowdowns because it executes repeatedly for each row, increasing transaction time.

B The trigger will only run once per transaction, so complex logic has minimal impact.

C Triggers do not affect performance because they run asynchronously after the transaction commits.

D Complex logic inside triggers improves performance by optimizing data changes automatically.

💡 Hint

Think about how often the trigger code runs when many rows are changed.

Triggers that execute complex logic for each row can slow down data modifications because the logic runs repeatedly, increasing the total time for the transaction.

❓ query_result

intermediate

Output rows affected by trigger with row-level execution

Given a table orders with 3 rows updated in a single statement, and a row-level trigger that inserts a log entry per updated row, how many rows will be inserted into the log table?

SQL

CREATE TRIGGER log_order_update AFTER UPDATE ON orders FOR EACH ROW INSERT INTO order_log(order_id, action) VALUES (NEW.id, 'updated');

UPDATE orders SET status = 'shipped' WHERE status = 'processing';

A 1 row will be inserted into <code>order_log</code>.

B 3 rows will be inserted into <code>order_log</code>.

C No rows will be inserted because triggers do not run on UPDATE.

D The number of rows inserted depends on the number of columns updated.

💡 Hint

Consider how row-level triggers behave with multiple rows affected.

Row-level triggers execute once per affected row, so updating 3 rows causes 3 trigger executions and 3 inserts into the log table.

📝 Syntax

advanced

Identifying inefficient trigger design

Which trigger design below is likely to cause the worst performance impact on bulk inserts?

A A trigger that only logs the number of rows inserted after the statement completes.

B A statement-level trigger that runs once after all rows are inserted.

C A trigger that disables indexes before insert and rebuilds them after.

D A row-level trigger that performs a complex SELECT query for each inserted row.

💡 Hint

Think about how often the trigger code runs and what it does each time.

Row-level triggers with complex queries run once per row, causing significant slowdowns on bulk operations compared to statement-level triggers.

🔧 Debug

advanced

Diagnosing trigger-induced slow query

A trigger on the employees table causes an UPDATE statement to run very slowly. The trigger updates a departments table for each employee updated. What is the likely cause?

A The trigger runs an UPDATE on <code>departments</code> for each employee row, causing many repeated updates and locking issues.

B The trigger disables foreign keys, causing data inconsistency and slow queries.

C The trigger uses a temporary table which is not indexed, causing slow lookups.

D The trigger is a statement-level trigger, so it runs only once and cannot cause slowdowns.

💡 Hint

Consider how triggers that modify other tables per row can affect performance.

Row-level triggers that update another table for each row can cause many repeated updates and locking, slowing down the original update statement.

🧠 Conceptual

expert

Best practice to optimize trigger performance on bulk operations

Which approach is best to optimize trigger performance when handling bulk data modifications?

A Avoid using triggers altogether and rely on client-side code for data integrity.

B Disable triggers during bulk operations and re-enable them afterward without any data validation.

C Use statement-level triggers that process all affected rows in a single operation instead of row-level triggers.

D Use row-level triggers with complex logic to handle each row individually for accuracy.

💡 Hint

Think about minimizing repeated executions during bulk changes.

Statement-level triggers run once per statement, allowing batch processing of all affected rows, which improves performance during bulk operations.

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