BEFORE trigger execution in SQL

Introduction

A BEFORE trigger runs just before a change happens in the database. It lets you check or change data before saving it.

You want to check if new data meets rules before adding it.

You want to change data automatically before saving it.

You want to stop a change if something is wrong.

You want to log or audit data before it changes.

You want to set default values before inserting data.

Syntax

SQL

CREATE TRIGGER trigger_name
BEFORE INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
BEGIN
  -- trigger logic here
END;

BEFORE triggers run before the data change happens.

You can use NEW.column_name to access or change new data in INSERT or UPDATE.

Examples

This trigger checks if the age is at least 18 before adding a new user. If not, it stops the insert.

SQL

CREATE TRIGGER check_age_before_insert
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;

This trigger sets the order status to 'pending' if no status is given before inserting.

SQL

CREATE TRIGGER set_default_status_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.status IS NULL THEN
    SET NEW.status = 'pending';
  END IF;
END;

Sample Program

First, we create a users table. Then, a BEFORE INSERT trigger checks if age is at least 18. The first insert works. The second insert fails with an error.

SQL

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

DELIMITER $$
CREATE TRIGGER check_age_before_insert
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$$
DELIMITER ;

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 20);
SELECT * FROM users;

INSERT INTO users (id, name, age) VALUES (2, 'Bob', 16);

Important Notes

BEFORE triggers can prevent bad data by stopping the operation.

Use NEW to read or change the new row's data in INSERT or UPDATE triggers.

Use OLD to read the old row's data in UPDATE or DELETE triggers.

Summary

BEFORE triggers run before data changes happen.

They let you check or modify data before saving.

You can stop changes if data is invalid.