UPDATE trigger with OLD and NEW in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a trigger that fires before an UPDATE on the employees table.

SQL

CREATE TRIGGER before_employee_update BEFORE UPDATE ON employees FOR EACH ROW BEGIN SET NEW.salary = NEW.salary * [1]; END;

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

A1.1

BOLD.salary

CNEW.salary

D0.9

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD.salary instead of a number to multiply.

Trying to assign to OLD.salary which is not allowed.

The trigger multiplies the new salary by 1.1 before updating, giving a 10% raise.

2fill in blank

medium

Complete the code to log the old salary value into a salary_changes table inside the trigger.

SQL

CREATE TRIGGER log_salary_change AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO salary_changes(employee_id, old_salary) VALUES (NEW.id, [1]); END;

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

ANEW.salary

BOLD.id

CNEW.id

DOLD.salary

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW.salary instead of OLD.salary to log the previous value.

Using NEW.id instead of OLD.id for the employee id.

OLD.salary holds the salary value before the update, which we want to log.

3fill in blank

hard

Fix the error in the trigger code to correctly prevent salary from being set below 3000.

SQL

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

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

AOLD.salary

B3000

Csalary

DNEW.salary

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD.salary or NEW.salary as the threshold value.

Not using a numeric constant for the minimum salary.

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

4fill in blank

hard

Fill both blanks to create a trigger that updates the last_modified column to current timestamp on update.

SQL

CREATE TRIGGER update_timestamp BEFORE UPDATE ON employees FOR EACH ROW BEGIN SET NEW.[1] = [2]; END;

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

Alast_modified

BNOW()

CCURRENT_DATE

Dcreated_at

Attempts:

3 left

💡 Hint

Common Mistakes

Using created_at instead of last_modified.

Using CURRENT_DATE which only gives the date, not time.

The trigger sets the last_modified column to the current timestamp using NOW() before updating.

5fill in blank

hard

Fill all three blanks to create a trigger that prevents changing the employee's department to 'HR' if their salary is below 5000.

SQL

CREATE TRIGGER prevent_hr_update BEFORE UPDATE ON employees FOR EACH ROW BEGIN IF NEW.[1] = 'HR' AND OLD.[2] < [3] THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary too low for HR department'; END IF; END;

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

Adepartment

Bsalary

C5000

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW.salary instead of OLD.salary for the salary check.

Using wrong column names like id instead of department or salary.

The trigger checks if the new department is 'HR' and the old salary is less than 5000, then raises an error.