BEFORE trigger execution in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a BEFORE INSERT trigger that sets the new salary to 5000.

SQL

CREATE TRIGGER set_salary_before_insert BEFORE INSERT ON employees FOR EACH ROW BEGIN SET NEW.salary = [1]; END;

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

A5000

BNULL

Csalary

DDEFAULT

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD.salary instead of NEW.salary

Assigning NULL which removes the salary

Using DEFAULT keyword incorrectly

The trigger sets the NEW.salary value to 5000 before inserting the row.

2fill in blank

medium

Complete the code to create a BEFORE UPDATE trigger that prevents salary from being set below 3000.

SQL

CREATE TRIGGER check_salary_before_update BEFORE UPDATE ON employees FOR EACH ROW BEGIN IF NEW.salary < [1] THEN SET NEW.salary = [2]; END IF; END;

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

A5000

B3000

C1000

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD.salary instead of NEW.salary

Setting salary to a value higher than the minimum

Using incorrect comparison operators

The trigger checks if the new salary is less than 3000 and sets it to 3000 if true.

3fill in blank

hard

Fix the error in the BEFORE INSERT trigger that tries to modify OLD values.

SQL

CREATE TRIGGER fix_trigger BEFORE INSERT ON employees FOR EACH ROW BEGIN SET [1].salary = 4000; END;

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

Asalary

BOLD

Cemployees

DNEW

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to set OLD values in an INSERT trigger

Using table name instead of NEW or OLD

Assigning values directly to column names

In a BEFORE INSERT trigger, only NEW can be modified. OLD is not available.

4fill in blank

hard

Fill both blanks to create a BEFORE UPDATE trigger that changes the new name to uppercase.

SQL

CREATE TRIGGER uppercase_name_before_update BEFORE UPDATE ON employees FOR EACH ROW BEGIN SET NEW.name = [1](NEW.name); END;

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

ATRIM

BLOWER

CUPPER

DLENGTH

Attempts:

3 left

💡 Hint

Common Mistakes

Using LOWER instead of UPPER

Using LENGTH which returns a number

Using TRIM which removes spaces

The UPPER function converts the new name to uppercase before update.

5fill in blank

hard

Fill all three blanks to create a BEFORE INSERT trigger that sets new salary to 6000 if NULL or less than 6000.

SQL

CREATE TRIGGER set_min_salary_before_insert BEFORE INSERT ON employees FOR EACH ROW BEGIN IF NEW.salary IS [1] OR NEW.salary [2] [3] THEN SET NEW.salary = 6000; END IF; END;

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

ANULL

B<

C6000

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using IS NOT NULL instead of IS NULL

Using > instead of < for comparison

Comparing to wrong salary value

The trigger checks if NEW.salary is NULL or less than 6000, then sets it to 6000.

Practice

1. What is the main purpose of a BEFORE trigger in SQL?

easy

A. To execute code after data is inserted or updated

B. To delete data automatically

C. To execute code before a data modification happens

D. To create a backup of the database

Solution

1. Step 1: Understand trigger timing

   A BEFORE trigger runs before the data change happens, allowing checks or modifications.

2. Step 2: Compare options

   To execute code before a data modification happens correctly states the trigger runs before data modification. Options A, C, and D describe other actions not related to BEFORE triggers.

3. Final Answer:

   To execute code before a data modification happens -> Option C

4. Quick Check:

   BEFORE trigger = runs before data change [OK]

Hint: BEFORE triggers run before data changes happen [OK]

Common Mistakes:

• Confusing BEFORE with AFTER triggers
• Thinking triggers delete data automatically
• Assuming triggers create backups

2. Which of the following is the correct syntax to create a BEFORE INSERT trigger in SQL?

easy

A. CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END;

B. CREATE TRIGGER trg AFTER INSERT ON table_name BEGIN ... END;

C. CREATE TRIGGER trg BEFORE UPDATE ON table_name FOR EACH ROW BEGIN ... END;

D. CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END;

Solution

1. Step 1: Identify correct trigger timing and event

   The question asks for a BEFORE INSERT trigger, so timing is BEFORE and event is INSERT.

2. Step 2: Match syntax

   CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END; matches CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END;. Other options have wrong timing or event.

3. Final Answer:

   CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END; -> Option D

4. Quick Check:

   BEFORE INSERT syntax = CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END; [OK]

Hint: BEFORE triggers use 'BEFORE event ON table' syntax [OK]

Common Mistakes:

• Using AFTER instead of BEFORE
• Mixing INSERT with UPDATE or DELETE
• Omitting FOR EACH ROW clause

3. Given this trigger code:

CREATE TRIGGER check_age BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.age < 18 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age must be 18 or older'; END IF; END;

What happens if you try to insert a user with age 16?

medium

A. The insert fails with an error message 'Age must be 18 or older'

B. The age is automatically set to 18

C. The insert is ignored silently

D. The insert succeeds and user is added

Solution

1. Step 1: Understand trigger logic

   The trigger checks if NEW.age is less than 18 and raises an error if true.

2. Step 2: Apply to age 16

   Since 16 < 18, the trigger raises an error with message 'Age must be 18 or older', stopping the insert.

3. Final Answer:

   The insert fails with an error message 'Age must be 18 or older' -> Option A

4. Quick Check:

   BEFORE trigger can stop invalid inserts [OK]

Hint: BEFORE triggers can block invalid data with SIGNAL [OK]

Common Mistakes:

• Thinking insert succeeds despite error
• Assuming silent ignore instead of error
• Believing trigger changes data automatically

4. Consider this trigger code:

CREATE TRIGGER trg BEFORE UPDATE ON products FOR EACH ROW BEGIN SET NEW.price = NEW.price * 0.9; END;

When trying to create this trigger, you get an error. What is the likely cause?

medium

A. The syntax is missing a delimiter or semicolon inside the body

B. The trigger body must be enclosed in BEGIN and END

C. The trigger must specify FOR EACH ROW for row-level triggers

D. You cannot modify NEW values in a BEFORE UPDATE trigger

Solution

1. Step 1: Check trigger syntax

   The trigger body uses multiple statements and must handle delimiters properly.

2. Step 2: Identify missing delimiter

   Without changing the delimiter, the semicolon inside the body ends the statement early, causing syntax error.

3. Final Answer:

   The syntax is missing a delimiter or semicolon inside the body -> Option A

4. Quick Check:

   Use DELIMITER to define trigger body [OK]

Hint: Use DELIMITER to avoid semicolon conflicts in triggers [OK]

Common Mistakes:

• Thinking NEW values can't be changed in BEFORE triggers
• Ignoring delimiter changes for multi-statement bodies
• Missing FOR EACH ROW clause

5. You want to ensure that before inserting a new order, the quantity is never negative. Which trigger code correctly enforces this using a BEFORE INSERT trigger?

hard

A. CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SET NEW.quantity = 0; END IF; END;

B. CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END;

C. CREATE TRIGGER trg AFTER INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END;

D. CREATE TRIGGER trg BEFORE UPDATE ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END;

Solution

1. Step 1: Identify correct timing and event

   We want to check before inserting, so BEFORE INSERT is correct.

2. Step 2: Check enforcement method

   To stop negative quantity, raising an error with SIGNAL is best. CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END; uses BEFORE INSERT and SIGNAL correctly.

3. Step 3: Eliminate wrong options

   CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SET NEW.quantity = 0; END IF; END; silently changes quantity, not enforcing. CREATE TRIGGER trg AFTER INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END; uses AFTER INSERT, too late to stop insert. CREATE TRIGGER trg BEFORE UPDATE ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END; uses BEFORE UPDATE, not insert.

4. Final Answer:

   CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END; -> Option B

5. Quick Check:

   Use BEFORE INSERT + SIGNAL to block invalid data [OK]

Hint: Use BEFORE INSERT with SIGNAL to block bad data [OK]

Common Mistakes:

• Using AFTER INSERT instead of BEFORE INSERT
• Silently fixing data instead of raising error
• Checking on UPDATE instead of INSERT