BEFORE UPDATE triggers in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a BEFORE UPDATE trigger in MySQL?

A BEFORE UPDATE trigger is a special procedure that runs automatically before a row in a table is updated. It lets you check or change data before the update happens.

beginner

When does a BEFORE UPDATE trigger execute?

It executes right before the database changes the data in a row during an UPDATE operation.

intermediate

Can you modify the new values in a BEFORE UPDATE trigger?

Yes, you can change the new values using the NEW keyword inside the trigger before they are saved to the table.

intermediate

What happens if a BEFORE UPDATE trigger signals an error?

The update operation is stopped and the error is returned. No changes are made to the row.

advanced

Write a simple BEFORE UPDATE trigger that prevents changing a user's email to an empty string.

CREATE TRIGGER prevent_empty_email BEFORE UPDATE ON users FOR EACH ROW BEGIN IF NEW.email = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email cannot be empty'; END IF; END;

When does a BEFORE UPDATE trigger run in MySQL?

A After a row is updated

B After a row is deleted

C Before a row is inserted

D Before a row is updated

A BEFORE UPDATE trigger runs before the database updates a row.

Which keyword lets you access the new values in a BEFORE UPDATE trigger?

A OLD

B NEW

C CURRENT

D NEXT

The NEW keyword refers to the new values that will be saved.

What happens if a BEFORE UPDATE trigger raises an error?

A The update is canceled

B The database restarts

C The trigger is ignored

D The update continues anyway

Raising an error in a BEFORE UPDATE trigger stops the update.

Can a BEFORE UPDATE trigger modify the data before it is saved?

A No, it can only read data

B Only if the table is empty

C Yes, it can change NEW values

D Only for numeric columns

BEFORE UPDATE triggers can change the NEW values before saving.

Which SQL statement creates a BEFORE UPDATE trigger?

A CREATE TRIGGER ... BEFORE UPDATE

B CREATE PROCEDURE ... BEFORE UPDATE

C CREATE FUNCTION ... BEFORE UPDATE

D CREATE EVENT ... BEFORE UPDATE

Triggers are created with CREATE TRIGGER and specify BEFORE UPDATE.

Explain what a BEFORE UPDATE trigger does and give an example use case.

Think about checking or changing data before it is saved.

Describe how you would prevent invalid data from being saved using a BEFORE UPDATE trigger.

Use conditional logic and SIGNAL to block bad data.