Trigger performance considerations in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a trigger that activates {{BLANK_1}} a row is inserted.

SQL

CREATE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  -- trigger logic here
END; -- This trigger runs [1] a row is inserted

Drag options to blanks, or click blank then click option'

Awhen

Bafter

Cinstead of

Dbefore

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing BEFORE and AFTER timing.

Using 'when' which is not a valid trigger timing keyword.

The trigger is defined as AFTER INSERT, so it runs after a row is inserted.

2fill in blank

medium

Complete the code to avoid performance issues by limiting the trigger to run {{BLANK_1}} times per statement.

SQL

CREATE TRIGGER trg_bulk_update
AFTER UPDATE ON orders
[1]
BEGIN
  -- trigger logic
END;

Drag options to blanks, or click blank then click option'

AFOR EACH STATEMENT

BFOR EACH

CFOR EACH ROW

DFOR EACH TRANSACTION

Attempts:

3 left

💡 Hint

Common Mistakes

Using FOR EACH ROW causes the trigger to run many times, slowing performance.

FOR EACH TRANSACTION is not a valid trigger clause.

Using FOR EACH STATEMENT makes the trigger run once per statement, improving performance for bulk operations.

3fill in blank

hard

Fix the error in the trigger code by choosing the correct keyword for referencing the new row's column value.

SQL

CREATE TRIGGER trg_check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary [1] 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary must be positive';
  END IF;
END;

Drag options to blanks, or click blank then click option'

A<

B>=

C<=

D==

Attempts:

3 left

💡 Hint

Common Mistakes

Using '==' which is not valid SQL syntax.

Checking for salary >= 0 instead of < 0.

The condition should check if salary is less than 0 to raise an error for negative values.

4fill in blank

hard

Fill both blanks to create a trigger that avoids performance issues by limiting the operations inside the trigger to {{BLANK_1}} and using {{BLANK_2}} operators.

SQL

CREATE TRIGGER trg_limit_ops
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  -- Only [1] operations allowed
  IF NEW.stock [2] OLD.stock THEN
    -- update logic
  END IF;
END;

Drag options to blanks, or click blank then click option'

Asimple

Bcomplex

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using complex operations inside triggers.

Using '<' when the logic requires '>'.

Keeping trigger operations simple and using straightforward comparisons like '>' helps maintain good performance.

5fill in blank

hard

Fill all three blanks to optimize trigger performance by avoiding {{BLANK_1}}, minimizing {{BLANK_2}}, and using {{BLANK_3}} triggers when possible.

SQL

CREATE TRIGGER trg_optimize
BEFORE INSERT ON sales
FOR EACH ROW
BEGIN
  -- Avoid [1]
  -- Minimize [2]
  -- Prefer [3] triggers
END;

Drag options to blanks, or click blank then click option'

Aheavy computations

Bnested queries

Cstatement-level

Drow-level

Attempts:

3 left

💡 Hint

Common Mistakes

Using row-level triggers unnecessarily.

Including heavy computations inside triggers.

To keep triggers fast, avoid heavy computations and nested queries, and prefer statement-level triggers when possible.

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