INSERT trigger in SQL

Introduction

An INSERT trigger runs automatically when new data is added to a table. It helps you check or change data right when it enters the database.

You want to log every new user added to your system.

You need to check that new orders have valid prices before saving.

You want to automatically set a timestamp when a new record is created.

You want to prevent adding duplicate entries in a table.

You want to update related tables when new data is inserted.

Syntax

SQL

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements here
END;

The trigger runs after a new row is inserted into the table.

Use NEW.column_name to access the inserted row's data.

Examples

This trigger adds a log entry every time a new user is inserted.

SQL

CREATE TRIGGER log_new_user
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_log(user_id, action) VALUES (NEW.id, 'added');
END;

This trigger stops inserting an order if the price is not positive.

SQL

CREATE TRIGGER check_price
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.price <= 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price must be positive';
  END IF;
END;

Sample Program

This example creates a products table and a log table. The trigger adds a log entry every time a new product is inserted.

SQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2)
);

CREATE TABLE product_log (
  log_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  action VARCHAR(20)
);

DELIMITER $$
CREATE TRIGGER after_product_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
  INSERT INTO product_log(product_id, action) VALUES (NEW.id, 'inserted');
END$$
DELIMITER ;

INSERT INTO products (id, name, price) VALUES (1, 'Pen', 1.20);

SELECT * FROM product_log;

Important Notes

Triggers can slow down inserts if they do a lot of work.

Use BEFORE INSERT triggers to check or change data before saving.

Use AFTER INSERT triggers to react after data is saved.

Summary

INSERT triggers run automatically when new rows are added.

They help check, change, or log data during insertion.

Use NEW to access the inserted row's values inside the trigger.