Triggers
📌 Database Triggers – Quick Notes & MCQs (with Solutions)
✅ Basic Trigger Syntax
sql
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
OLD → data before change (UPDATE/DELETE)NEW → data after change (INSERT/UPDATE)
Delimiter
In MySQL CLI, use DELIMITER // to handle BEGIN...END.
Restrictions
❌ Cannot use COMMIT / ROLLBACK inside triggers
🚫 Cannot call triggers manually
Common Use Cases
Logging, auditing, validation, enforcing business rules
🧪 MCQs Based on Real Accenture Technical Assessment
1️⃣ Student Record Backup
Tables:
sql
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:
sql
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
sql
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:
sql
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
sql
employees(emp_id INT PRIMARY KEY, emp_name VARCHAR(50))
register_log(emp_id INT, joined_on DATETIME)
✅ Trigger:
sql
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
sql
products(prod_id INT PRIMARY KEY, prod_name VARCHAR(50))
product_log(prod_id INT, deleted_at DATETIME)
✅ Trigger:
sql
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
sql
orders(order_id INT PRIMARY KEY, total_amount DECIMAL(10,2))
❗ Total must not be negative.
✅ Trigger:
sql
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
sql
attendance(stud_id INT, percentage INT)
attendance_log(stud_id INT, old_percent INT, new_percent INT, updated_on DATETIME)
✅ Trigger:
sql
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
sql
users(user_id INT PRIMARY KEY, role VARCHAR(20))
✅ Trigger:
sql
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
sql
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:
sql
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
sql
customers(cust_id INT PRIMARY KEY, cust_name VARCHAR(50))
customer_log(cust_id INT, created_on DATETIME)
✅ Trigger:
sql
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)
sql
products(prod_id INT, price DECIMAL(10,2))
✅ Trigger:
sql
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.