BEFORE INSERT triggers in MySQL - Step-by-Step Execution

Concept Flow - BEFORE INSERT triggers

New row data ready for insert

↓

BEFORE INSERT trigger fires

↓

Modify row data

↓

Insert modified row into table

↓

Insert done

When a new row is about to be inserted, the BEFORE INSERT trigger runs first. It can change the row data or check conditions before the actual insert happens.

Execution Sample

MySQL

CREATE TRIGGER before_insert_example
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
END;

This trigger sets the created_at column to the current time before inserting a new employee row.

Execution Table

Step	Action	NEW row data before	Trigger effect	NEW row data after	Result
1	Prepare new row for insert	{"name": "Alice", "created_at": NULL}	Trigger fires	{"name": "Alice", "created_at": NULL}	Trigger starts
2	Trigger sets created_at = NOW()	{"name": "Alice", "created_at": NULL}	Set NEW.created_at = current timestamp	{"name": "Alice", "created_at": "2024-06-01 10:00:00"}	Row data modified
3	Insert row into employees table	{"name": "Alice", "created_at": "2024-06-01 10:00:00"}	No further changes	{"name": "Alice", "created_at": "2024-06-01 10:00:00"}	Row inserted with modified data
4	End	N/A	N/A	N/A	Trigger and insert complete

💡 Trigger finishes before insert; modified row data is inserted into the table.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
NEW.name	N/A	Alice	Alice	Alice
NEW.created_at	NULL	NULL	2024-06-01 10:00:00	2024-06-01 10:00:00

Key Moments - 2 Insights

Why does the trigger use NEW.created_at instead of just created_at?

In the execution_table at Step 2, NEW refers to the new row being inserted. The trigger modifies NEW.created_at to change the value before the insert happens.

Can the BEFORE INSERT trigger prevent the insert from happening?

Yes, if the trigger raises an error or condition fails, the insert can be stopped. But in this example, the trigger only modifies data and allows the insert to continue (see Step 3).

Visual Quiz - 3 Questions

Look at the execution_table at Step 2, what value does NEW.created_at get set to?

A "2024-06-01 10:00:00"

B NULL

C "Alice"

D No change

💡 Hint

Check the 'NEW row data after' column in Step 2 of the execution_table.

At which step does the actual insert into the table happen?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at the 'Result' column describing when the row is inserted.

If the trigger did not set NEW.created_at, what would be the value after Step 3?

A "2024-06-01 10:00:00"

B "Alice"

C NULL

D Trigger error

💡 Hint

Refer to the 'NEW row data before' and 'after' columns in Steps 1 and 2.

Concept Snapshot

BEFORE INSERT triggers run before a new row is inserted.
They can modify the new row's data using NEW.column_name.
If needed, they can stop the insert by raising errors.
Useful for setting default values or validating data.
The insert uses the modified NEW data after the trigger finishes.

Full Transcript

A BEFORE INSERT trigger in MySQL runs just before a new row is added to a table. It can change the data in the new row by using the NEW keyword. For example, it can set a timestamp column to the current time. The trigger runs first, then the insert happens with the changed data. If the trigger raises an error, the insert can be stopped. This helps keep data clean and consistent automatically.