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.