INSERT trigger in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a trigger that activates after an INSERT on the employees table.

SQL

CREATE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(action) VALUES([1]);
END;

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

A'INSERT'

BNEW.id

COLD.name

DNOW()

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD values in an INSERT trigger causes errors.

Using a column value instead of a string literal for the action.

The trigger inserts the string 'INSERT' into the audit_log table to record the action.

2fill in blank

medium

Complete the code to reference the new employee's name in the trigger.

SQL

CREATE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(description) VALUES(CONCAT('New employee: ', [1]));
END;

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

ANEW.name

Bemployees.name

COLD.name

DCURRENT_USER

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD instead of NEW in an INSERT trigger.

Referencing the table name directly instead of NEW.

NEW.name refers to the inserted row's name in an INSERT trigger.

3fill in blank

hard

Fix the error in the trigger by completing the missing keyword.

SQL

CREATE TRIGGER trg_before_insert
[1] INSERT ON employees
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
END;

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

AFOR

BBEFORE

CON

DAFTER

Attempts:

3 left

💡 Hint

Common Mistakes

Using AFTER instead of BEFORE when modifying NEW values.

Omitting the timing keyword causes syntax errors.

The trigger must be defined as BEFORE INSERT to modify NEW values before insertion.

4fill in blank

hard

Fill both blanks to create a trigger that logs the new employee's ID and name after insert.

SQL

CREATE TRIGGER trg_log_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(emp_id, emp_name) VALUES([1], [2]);
END;

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

ANEW.id

BOLD.id

CNEW.name

DOLD.name

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD values in an INSERT trigger.

Mixing NEW and OLD references.

NEW.id and NEW.name refer to the inserted row's ID and name.

5fill in blank

hard

Fill all three blanks to create a trigger that prevents inserting employees with salary less than 3000.

SQL

CREATE TRIGGER trg_check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary [1] [2] THEN
    SIGNAL SQLSTATE [3] SET MESSAGE_TEXT = 'Salary too low';
  END IF;
END;

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

A<

B3000

C'45000'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong comparison operators.

Using incorrect SQLSTATE codes.

Not using SIGNAL to raise an error.

The trigger checks if NEW.salary is less than 3000 and raises an error with SQLSTATE '45000'.