BEFORE UPDATE triggers in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a BEFORE UPDATE trigger that sets the new value of a column.

MySQL

CREATE TRIGGER update_salary BEFORE UPDATE ON employees FOR EACH ROW SET NEW.salary = NEW.salary [1] 1000;

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

A-

B*

C+

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using '-' will decrease the salary instead of increasing it.

Using '*' or '/' will multiply or divide the salary, which is not intended here.

The trigger adds 1000 to the new salary before updating the row.

2fill in blank

medium

Complete the code to prevent updating the salary if the new salary is less than the old salary.

MySQL

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

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

A=

B>

C>=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' would check if the new salary is greater, which is not the goal.

Using '=' or '>=' will not correctly prevent salary decrease.

The trigger checks if the new salary is less than the old salary and resets it to the old salary to prevent decrease.

3fill in blank

hard

Fix the error in the trigger code to correctly assign a new value to a column in a BEFORE UPDATE trigger.

MySQL

CREATE TRIGGER update_bonus BEFORE UPDATE ON employees FOR EACH ROW SET [1].bonus = NEW.bonus + 500;

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

ANEW

Bbonus

Cemployees

DOLD

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD instead of NEW causes errors because OLD is read-only.

Using the table name or column name alone is invalid syntax.

In BEFORE UPDATE triggers, you assign new values using the NEW keyword, not OLD or table names.

4fill in blank

hard

Fill both blanks to create a BEFORE UPDATE trigger that prevents the 'status' column from being changed to 'inactive'.

MySQL

CREATE TRIGGER prevent_inactive BEFORE UPDATE ON users FOR EACH ROW BEGIN IF NEW.status [1] 'inactive' THEN SET NEW.status = [2].status; END IF; END;

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

A=

B!=

COLD

DNEW

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' would check for inequality, which is not correct here.

Using NEW.status to reset the value causes no change.

The trigger checks if the new status is 'inactive' and resets it to the old status to prevent the change.

5fill in blank

hard

Fill all three blanks to create a BEFORE UPDATE trigger that increases 'points' by 10 only if the new 'level' is greater than the old 'level'.

MySQL

CREATE TRIGGER level_up BEFORE UPDATE ON players FOR EACH ROW BEGIN IF NEW.level [1] OLD.level THEN SET NEW.points = NEW.points [2] [3]; END IF; END;

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

A>

B+

C10

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' would check the wrong condition.

Using '-' instead of '+' would subtract points.

The trigger checks if the new level is greater than the old level, then adds 10 points to the new points value.