Trigger performance considerations in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a database trigger?

A database trigger is a special procedure that automatically runs when certain events happen in the database, like inserting, updating, or deleting data.

beginner

Why can triggers affect database performance?

Triggers run automatically during data changes, so if they do a lot of work or run many times, they can slow down the database operations.

intermediate

Name one way to reduce the performance impact of triggers.

Keep the trigger code simple and fast, doing only what is necessary to avoid slowing down the main database operation.

intermediate

What is a potential problem of having multiple triggers on the same table?

Multiple triggers can cause extra processing time because each trigger runs in sequence, increasing the total time for the database operation.

intermediate

How can you monitor the performance impact of triggers?

You can use database tools or logs to measure how long triggers take to run and check if they slow down data operations.

What happens if a trigger contains complex queries that take a long time to run?

A The trigger will be skipped to keep performance fast.

B The trigger will run after the operation without affecting speed.

C The database operation will be slower because the trigger runs during the operation.

D The database will automatically optimize the trigger to run faster.

Triggers run during the database operation, so complex or slow queries inside them will slow down the operation.

Which of these is a good practice to improve trigger performance?

A Keep trigger logic simple and fast.

B Avoid using triggers and do all work manually outside the database.

C Include heavy calculations inside the trigger.

D Create many triggers for the same event.

Keeping trigger logic simple and fast helps reduce the performance impact on database operations.

What is a risk of having multiple triggers on the same table for the same event?

A Triggers will cancel each other out.

B Triggers will run in sequence, increasing operation time.

C Only the first trigger will run.

D Triggers will run in parallel without delay.

Multiple triggers run one after another, which can increase the total time for the database operation.

How can you check if a trigger is causing slow database operations?

A By using database monitoring tools to measure trigger execution time.

B By disabling all triggers permanently.

C By guessing based on the number of triggers.

D By rewriting the entire database.

Database monitoring tools can help measure how long triggers take and identify performance issues.

When does a trigger execute in relation to the database operation?

A Only after the database operation finishes.

B Only before the database operation starts.

C Randomly at any time.

D Before or after the data change event, depending on trigger type.

Triggers can be set to run before or after data changes, affecting when they impact performance.

Explain how triggers can impact database performance and what you can do to minimize this impact.

Think about when triggers run and how their complexity affects speed.

Describe best practices for writing efficient triggers to avoid slowing down your database.

Focus on simplicity and monitoring.

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