SQL Trigger examples with full code
Below are real-time SQL Trigger examples with full code that are used in real projects (banking, e-commerce, attendance, inventory, audit logs).
I’ll explain WHEN it is used + WHY + CODE + OUTPUT effect.
Get Pinjari Akbar’s stories in your inbox
Join Medium for free to get updates from this writer.
Examples are written in MySQL (most common).
(If you want Oracle / SQL Server / PostgreSQL, tell me.)
🔹 What is a Trigger? (Quick)
A Trigger is an automatic program that runs when an event occurs on a table:
- INSERT
- UPDATE
- DELETE
Types:
- BEFORE Trigger
- AFTER Trigger
✅ REAL-TIME TRIGGER EXAMPLES
1️⃣ Auto-Update Stock in Inventory (E-Commerce)
📌 Real-time use:
When a sale happens, product stock should reduce automatically.
Tables
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(50),
stock INT
);
CREATE TABLE sales (
sale_id INT PRIMARY KEY AUTO_INCREMENT,
product_id INT,
quantity INT
);Trigger Code
DELIMITER $$
CREATE TRIGGER reduce_stock_after_sale
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
UPDATE products
SET stock = stock - NEW.quantity
WHERE product_id = NEW.product_id;
END$$
DELIMITER ;🔄 What Happens?
INSERT INTO sales (product_id, quantity)
VALUES (1, 2);👉 Stock of product 1 decreases automatically.
2️⃣ Bank Account Balance Update (Banking System)
📌 Real-time use:
Whenever a transaction happens → update account balance.
Tables
CREATE TABLE accounts (
acc_no INT PRIMARY KEY,
balance DECIMAL(10,2)
);
CREATE TABLE transactions (
txn_id INT PRIMARY KEY AUTO_INCREMENT,
acc_no INT,
amount DECIMAL(10,2),
txn_type VARCHAR(10) -- deposit / withdraw
);Trigger Code
DELIMITER $$
CREATE TRIGGER update_balance
AFTER INSERT ON transactions
FOR EACH ROW
BEGIN
IF NEW.txn_type = 'deposit' THEN
UPDATE accounts
SET balance = balance + NEW.amount
WHERE acc_no = NEW.acc_no;
ELSE
UPDATE accounts
SET balance = balance - NEW.amount
WHERE acc_no = NEW.acc_no;
END IF;
END$$DELIMITER ;3️⃣ Prevent Negative Salary (HR System)
📌 Real-time use:
Don’t allow invalid salary entry.
Table
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
salary DECIMAL(10,2)
);Trigger Code (Validation)
DELIMITER $$
CREATE TRIGGER check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary cannot be negative';
END IF;
END$$DELIMITER ;4️⃣ Audit Log (Very Important — Real Projects)
📌 Real-time use:
Track who updated what & when (banks, govt, schools).
Tables
CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE user_audit (
audit_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
action VARCHAR(20),
action_time TIMESTAMP
);Trigger Code
DELIMITER $$
CREATE TRIGGER user_update_audit
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO user_audit (user_id, action, action_time)
VALUES (OLD.user_id, 'UPDATED', NOW());
END$$DELIMITER ;5️⃣ Student Attendance Percentage Auto-Calculate (Education)
📌 Real-time use:
When attendance added → percentage updates.
Tables
CREATE TABLE students (
student_id INT PRIMARY KEY,
total_classes INT,
attended INT,
percentage DECIMAL(5,2)
);Trigger Code
DELIMITER $$
CREATE TRIGGER calc_attendance
BEFORE UPDATE ON students
FOR EACH ROW
BEGIN
SET NEW.percentage =
(NEW.attended / NEW.total_classes) * 100;
END$$DELIMITER ;6️⃣ Prevent Delete (Security Trigger)
📌 Real-time use:
Important records should not be deleted.
DELIMITER $$
CREATE TRIGGER prevent_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Deletion not allowed';
END$$DELIMITER ;🔑 Key Interview Points
QuestionAnswerTrigger fires automatically?YesCan trigger call another trigger?NoCan trigger use COMMIT?❌ NoNEW & OLD keywords used?Yes
📌 Want Next?
Tell me what you need:
- 🔸 Triggers for DSC / Exams (Telugu)
- 🔸 Oracle / SQL Server triggers
- 🔸 Interview questions with answers
- 🔸 Mini project using trigger
I’ll explain step-by-step, exam-oriented 👍
