BEFORE trigger execution in SQL - Step-by-Step Execution

Concept Flow - BEFORE trigger execution

Start: Insert/Update/Delete operation

↓

BEFORE Trigger fires

↓

Trigger code runs: can modify NEW row

↓

Original operation continues with possibly modified data

↓

Operation completes, AFTER triggers may run

When a data change is requested, the BEFORE trigger runs first, allowing changes before the actual data operation happens.

Execution Sample

SQL

CREATE TRIGGER before_insert_example
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  SET NEW.salary = NEW.salary * 1.1;
END;

This trigger increases the salary by 10% before inserting a new employee row.

Execution Table

Step	Action	NEW.salary before	Trigger modifies NEW.salary	NEW.salary after	Operation continues
1	Insert employee with salary 1000 requested	N/A	No	N/A	Waiting for trigger
2	BEFORE INSERT trigger fires	1000	Yes, multiply by 1.1	1100	Trigger ends
3	Insert operation continues with NEW.salary=1100	1100	No	1100	Row inserted with updated salary

💡 Trigger finishes, operation continues with modified NEW data

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
NEW.salary	N/A	1000	1100	1100

Key Moments - 2 Insights

Why can the NEW.salary value be changed inside the BEFORE trigger?

Because the BEFORE trigger runs before the actual insert, it can modify the NEW row values that will be saved, as shown in execution_table step 2.

Does the original insert operation use the old or modified NEW.salary?

It uses the modified NEW.salary after the trigger runs, as shown in execution_table step 3 where the operation continues with salary 1100.

Visual Quiz - 3 Questions

Look at the execution_table, what is NEW.salary after the BEFORE trigger modifies it?

A 1000

B 1100

C 900

D 1200

💡 Hint

Check the 'NEW.salary after' column in step 2 of the execution_table.

At which step does the actual insert operation use the modified salary?

A Step 1

B Step 2

C Step 3

D After all steps

💡 Hint

Look at the 'Operation continues' column in step 3 of the execution_table.

If the BEFORE trigger did not modify NEW.salary, what would the final salary be?

A 1000

B 1100

C 0

D NULL

💡 Hint

Refer to the 'NEW.salary before' value in step 2 and imagine no modification.

Concept Snapshot

BEFORE triggers run before data changes happen.
They can modify NEW row data.
Changes affect the actual operation.
Useful for validation or automatic adjustments.
Runs once per affected row before insert/update/delete.

Full Transcript

When you insert, update, or delete data, a BEFORE trigger runs first. It can change the data before it is saved. For example, if you insert a salary of 1000, the BEFORE trigger can increase it to 1100. Then the insert uses this new value. This lets you adjust or check data automatically before saving. The execution table shows the steps: request insert, trigger runs and changes salary, then insert continues with new salary.

Practice

1. What is the main purpose of a BEFORE trigger in SQL?

easy

A. To execute code after data is inserted or updated

B. To delete data automatically

C. To execute code before a data modification happens

D. To create a backup of the database

Solution

1. Step 1: Understand trigger timing

   A BEFORE trigger runs before the data change happens, allowing checks or modifications.

2. Step 2: Compare options

   To execute code before a data modification happens correctly states the trigger runs before data modification. Options A, C, and D describe other actions not related to BEFORE triggers.

3. Final Answer:

   To execute code before a data modification happens -> Option C

4. Quick Check:

   BEFORE trigger = runs before data change [OK]

Hint: BEFORE triggers run before data changes happen [OK]

Common Mistakes:

• Confusing BEFORE with AFTER triggers
• Thinking triggers delete data automatically
• Assuming triggers create backups

2. Which of the following is the correct syntax to create a BEFORE INSERT trigger in SQL?

easy

A. CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END;

B. CREATE TRIGGER trg AFTER INSERT ON table_name BEGIN ... END;

C. CREATE TRIGGER trg BEFORE UPDATE ON table_name FOR EACH ROW BEGIN ... END;

D. CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END;

Solution

1. Step 1: Identify correct trigger timing and event

   The question asks for a BEFORE INSERT trigger, so timing is BEFORE and event is INSERT.

2. Step 2: Match syntax

   CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END; matches CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END;. Other options have wrong timing or event.

3. Final Answer:

   CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END; -> Option D

4. Quick Check:

   BEFORE INSERT syntax = CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END; [OK]

Hint: BEFORE triggers use 'BEFORE event ON table' syntax [OK]

Common Mistakes:

• Using AFTER instead of BEFORE
• Mixing INSERT with UPDATE or DELETE
• Omitting FOR EACH ROW clause

3. Given this trigger code:

CREATE TRIGGER check_age BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.age < 18 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age must be 18 or older'; END IF; END;

What happens if you try to insert a user with age 16?

medium

A. The insert fails with an error message 'Age must be 18 or older'

B. The age is automatically set to 18

C. The insert is ignored silently

D. The insert succeeds and user is added

Solution

1. Step 1: Understand trigger logic

   The trigger checks if NEW.age is less than 18 and raises an error if true.

2. Step 2: Apply to age 16

   Since 16 < 18, the trigger raises an error with message 'Age must be 18 or older', stopping the insert.

3. Final Answer:

   The insert fails with an error message 'Age must be 18 or older' -> Option A

4. Quick Check:

   BEFORE trigger can stop invalid inserts [OK]

Hint: BEFORE triggers can block invalid data with SIGNAL [OK]

Common Mistakes:

• Thinking insert succeeds despite error
• Assuming silent ignore instead of error
• Believing trigger changes data automatically

4. Consider this trigger code:

CREATE TRIGGER trg BEFORE UPDATE ON products FOR EACH ROW BEGIN SET NEW.price = NEW.price * 0.9; END;

When trying to create this trigger, you get an error. What is the likely cause?

medium

A. The syntax is missing a delimiter or semicolon inside the body

B. The trigger body must be enclosed in BEGIN and END

C. The trigger must specify FOR EACH ROW for row-level triggers

D. You cannot modify NEW values in a BEFORE UPDATE trigger

Solution

1. Step 1: Check trigger syntax

   The trigger body uses multiple statements and must handle delimiters properly.

2. Step 2: Identify missing delimiter

   Without changing the delimiter, the semicolon inside the body ends the statement early, causing syntax error.

3. Final Answer:

   The syntax is missing a delimiter or semicolon inside the body -> Option A

4. Quick Check:

   Use DELIMITER to define trigger body [OK]

Hint: Use DELIMITER to avoid semicolon conflicts in triggers [OK]

Common Mistakes:

• Thinking NEW values can't be changed in BEFORE triggers
• Ignoring delimiter changes for multi-statement bodies
• Missing FOR EACH ROW clause

5. You want to ensure that before inserting a new order, the quantity is never negative. Which trigger code correctly enforces this using a BEFORE INSERT trigger?

hard

A. CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SET NEW.quantity = 0; END IF; END;

B. CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END;

C. CREATE TRIGGER trg AFTER INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END;

D. CREATE TRIGGER trg BEFORE UPDATE ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END;

Solution

1. Step 1: Identify correct timing and event

   We want to check before inserting, so BEFORE INSERT is correct.

2. Step 2: Check enforcement method

   To stop negative quantity, raising an error with SIGNAL is best. CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END; uses BEFORE INSERT and SIGNAL correctly.

3. Step 3: Eliminate wrong options

   CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SET NEW.quantity = 0; END IF; END; silently changes quantity, not enforcing. CREATE TRIGGER trg AFTER INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END; uses AFTER INSERT, too late to stop insert. CREATE TRIGGER trg BEFORE UPDATE ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END; uses BEFORE UPDATE, not insert.

4. Final Answer:

   CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END; -> Option B

5. Quick Check:

   Use BEFORE INSERT + SIGNAL to block invalid data [OK]

Hint: Use BEFORE INSERT with SIGNAL to block bad data [OK]

Common Mistakes:

• Using AFTER INSERT instead of BEFORE INSERT
• Silently fixing data instead of raising error
• Checking on UPDATE instead of INSERT