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.