Why triggers automate responses in MySQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a trigger that runs before an insert.

MySQL

CREATE TRIGGER before_insert_trigger BEFORE INSERT ON employees FOR EACH ROW BEGIN [1]; END;

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

ADROP TABLE employees

BSET NEW.created_at = NOW()

CSELECT * FROM employees

DUPDATE employees SET salary = 0

Attempts:

3 left

💡 Hint

Common Mistakes

Using SELECT or DROP statements inside a trigger incorrectly.

Not using SET to assign values to NEW.

The trigger sets the created_at field to the current time before inserting a new row.

2fill in blank

medium

Complete the code to create a trigger that logs deletions.

MySQL

CREATE TRIGGER log_delete AFTER DELETE ON orders FOR EACH ROW BEGIN INSERT INTO audit_log (order_id, action) VALUES ([1], 'deleted'); END;

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

Aorder_id

BNEW.order_id

COLD.order_id

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW in a DELETE trigger causes errors.

Not specifying the correct column from OLD.

In a DELETE trigger, OLD holds the row being deleted, so we use OLD.order_id to log it.

3fill in blank

hard

Fix the error in the trigger code to update salary after insert.

MySQL

CREATE TRIGGER update_salary AFTER INSERT ON employees FOR EACH ROW BEGIN UPDATE employees SET salary = salary * 1.1 WHERE id = [1]; END;

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

ANEW.id

BOLD.id

Cid

DNEW.salary

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD in an AFTER INSERT trigger.

Using column names without table alias.

After inserting a new employee, use NEW.id to update that employee's salary.

4fill in blank

hard

Fill both blanks to create a trigger that prevents negative stock.

MySQL

CREATE TRIGGER prevent_negative_stock BEFORE UPDATE ON products FOR EACH ROW BEGIN IF NEW.stock [1] 0 THEN SIGNAL SQLSTATE [2] SET MESSAGE_TEXT = 'Stock cannot be negative'; END IF; END;

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

A<

B>

C45000

D'45000'

Attempts:

3 left

💡 Hint

Common Mistakes

Using > instead of < for the stock check.

Not quoting the SQLSTATE code.

The trigger checks if the new stock is less than zero and raises an error with SQLSTATE '45000'.

5fill in blank

hard

Fill all three blanks to create a trigger that logs salary changes.

MySQL

CREATE TRIGGER log_salary_change AFTER UPDATE ON employees FOR EACH ROW BEGIN IF NEW.salary [1] OLD.salary THEN INSERT INTO salary_log (employee_id, old_salary, new_salary) VALUES ([2], [3], NEW.salary); END IF; END;

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

A>

BOLD.id

COLD.salary

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of > to check salary increase.

Using NEW instead of OLD for old salary or ID.

The trigger checks if the new salary is greater than the old salary and logs the change with the employee's ID and old salary.