BEFORE INSERT triggers in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a BEFORE INSERT trigger that sets the 'created_at' column to the current timestamp.

MySQL

CREATE TRIGGER set_created_at BEFORE INSERT ON users FOR EACH ROW SET NEW.created_at = [1];

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

ANOW()

BCURRENT_DATE

CSYSDATE()

DGETDATE()

Attempts:

3 left

💡 Hint

Common Mistakes

Using a function that returns only the date, which misses the time part.

Using a function from another SQL dialect that MySQL does not support.

The NOW() function returns the current date and time in MySQL, which is suitable for a timestamp column.

2fill in blank

medium

Complete the code to create a BEFORE INSERT trigger that prevents inserting a user with an empty username.

MySQL

CREATE TRIGGER check_username BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.username = [1] THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Username cannot be empty'; END IF; END;

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

ANULL

B0

C' '

D''

Attempts:

3 left

💡 Hint

Common Mistakes

Checking for NULL instead of an empty string.

Using a space character instead of an empty string.

Checking for an empty string '' ensures the username is not blank.

3fill in blank

hard

Fix the error in the trigger code that tries to set NEW.status to 'active' before insert.

MySQL

CREATE TRIGGER activate_user BEFORE INSERT ON users FOR EACH ROW SET NEW.status = [1];

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

A"active"

Bactive

C'active'

DACTIVE

Attempts:

3 left

💡 Hint

Common Mistakes

Using unquoted strings which cause syntax errors.

Using double quotes which MySQL treats differently.

String literals in MySQL must be enclosed in single quotes, so 'active' is correct.

4fill in blank

hard

Fill both blanks to create a BEFORE INSERT trigger that sets NEW.created_at to current timestamp and NEW.updated_at to NULL.

MySQL

CREATE TRIGGER set_timestamps BEFORE INSERT ON users FOR EACH ROW BEGIN SET NEW.created_at = [1]; SET NEW.updated_at = [2]; END;

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

ANOW()

BNULL

CCURRENT_TIMESTAMP

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 instead of NULL for updated_at.

Using CURRENT_TIMESTAMP for updated_at when it should be NULL.

NOW() sets the current timestamp, and NULL sets the updated_at column to no value initially.

5fill in blank

hard

Fill all three blanks to create a BEFORE INSERT trigger that sets NEW.created_at to current timestamp, NEW.updated_at to NULL, and prevents inserting if NEW.email is NULL.

MySQL

CREATE TRIGGER validate_user BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.email IS [1] THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email cannot be NULL'; END IF; SET NEW.created_at = [2]; SET NEW.updated_at = [3]; END;

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

ANULL

BNOW()

D''

Attempts:

3 left

💡 Hint

Common Mistakes

Using empty string instead of NULL to check email.

Using wrong functions for timestamps.

The trigger checks if NEW.email IS NULL to prevent missing emails, sets created_at to current time with NOW(), and sets updated_at to NULL initially.