Triggers
Triggers
📌 Database Triggers – Quick Notes & MCQs (with Solutions)
✅ Basic Trigger Syntax
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- trigger actions
END;🧠 Key Points to Remember
Concept | Explanation |
|---|---|
Trigger Timing | Can be BEFORE or AFTER an event. |
Trigger Event | Can be INSERT, UPDATE, or DELETE. |
FOR EACH ROW | Trigger executes once for every affected row. |
OLD & NEW Keywords |
|
Delimiter | In MySQL CLI, use |
Restrictions | ❌ Cannot use COMMIT / ROLLBACK inside triggers |
Common Use Cases | Logging, auditing, validation, enforcing business rules |
🧪 MCQs Based on Real Accenture Technical Assessment
1️⃣ Student Record Backup
Tables:
students(student_id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50))
student_backup(student_id INT, name VARCHAR(50), department VARCHAR(50), deleted_on DATETIME)Question:
Backup every deleted student record into student_backup.
✅ Correct Trigger:
CREATE TRIGGER after_student_delete
AFTER DELETE ON students
FOR EACH ROW
BEGIN
INSERT INTO student_backup (student_id, name, department, deleted_on)
VALUES (OLD.student_id, OLD.name, OLD.department, NOW());
END;✔ Answer: AFTER DELETE using OLD values.
2️⃣ Salary Update Log
employees(emp_id INT PRIMARY KEY, salary DECIMAL(10,2))
salary_log(emp_id INT, old_salary DECIMAL(10,2), new_salary DECIMAL(10,2), updated_on DATETIME)Log whenever salary changes.
✅ Trigger:
CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_log (emp_id, old_salary, new_salary, updated_on)
VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
END;✔ Answer: AFTER UPDATE with OLD & NEW.
3️⃣ New Employee Registration Log
employees(emp_id INT PRIMARY KEY, emp_name VARCHAR(50))
register_log(emp_id INT, joined_on DATETIME)✅ Trigger:
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO register_log (emp_id, joined_on)
VALUES (NEW.emp_id, NOW());
END;✔ Answer: AFTER INSERT using NEW.
4️⃣ Product Deletion Tracker
products(prod_id INT PRIMARY KEY, prod_name VARCHAR(50))
product_log(prod_id INT, deleted_at DATETIME)✅ Trigger:
CREATE TRIGGER after_product_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
INSERT INTO product_log (prod_id, deleted_at)
VALUES (OLD.prod_id, NOW());
END;✔ Answer: AFTER DELETE.
5️⃣ Validate Order Total Before Insert
orders(order_id INT PRIMARY KEY, total_amount DECIMAL(10,2))❗ Total must not be negative.
✅ Trigger:
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
IF NEW.total_amount < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Total amount cannot be negative';
END IF;
END;✔ Answer: BEFORE INSERT with validation.
✔ Answer: SIGNAL SQLSTATE terminate the query and show an error message
6️⃣ Attendance Update Monitor
attendance(stud_id INT, percentage INT)
attendance_log(stud_id INT, old_percent INT, new_percent INT, updated_on DATETIME)✅ Trigger:
CREATE TRIGGER after_attendance_update
AFTER UPDATE ON attendance
FOR EACH ROW
BEGIN
INSERT INTO attendance_log (stud_id, old_percent, new_percent, updated_on)
VALUES (OLD.stud_id, OLD.percentage, NEW.percentage, NOW());
END;✔ Answer: AFTER UPDATE.
7️⃣ Prevent Deletion of Admin Users
users(user_id INT PRIMARY KEY, role VARCHAR(20))✅ Trigger:
CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
IF OLD.role = 'Admin' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete Admin users';
END IF;
END;✔ Answer: BEFORE DELETE to block action.
8️⃣ Log Department Change Only If Changed
employees(emp_id INT, department VARCHAR(50))
dept_change_log(emp_id INT, old_dept VARCHAR(50), new_dept VARCHAR(50), updated_on DATETIME)✅ Trigger:
CREATE TRIGGER after_dept_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.department <> NEW.department THEN
INSERT INTO dept_change_log (emp_id, old_dept, new_dept, updated_on)
VALUES (OLD.emp_id, OLD.department, NEW.department, NOW());
END IF;
END;✔ Answer: AFTER UPDATE (log when changed).
9️⃣ Customer Insertion Tracker
customers(cust_id INT PRIMARY KEY, cust_name VARCHAR(50))
customer_log(cust_id INT, created_on DATETIME)✅ Trigger:
CREATE TRIGGER after_customer_insert
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
INSERT INTO customer_log (cust_id, created_on)
VALUES (NEW.cust_id, NOW());
END;
✔ Answer: AFTER INSERT.
🔟 Product Price Validation (Prevent Negative Price)
products(prod_id INT, price DECIMAL(10,2))✅ Trigger:
CREATE TRIGGER before_price_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
IF NEW.price < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price cannot be negative';
END IF;
END;
✔ Answer: BEFORE UPDATE with validation.