DPDC — Assistant Engineer (CSE) — Exam: 17.10.2025প্রশ্ন ও সমাধান

A database trigger is a stored procedure that executes automatically in response to a specific event (INSERT, UPDATE, or DELETE) on a table — without being explicitly called by the application.

• Triggers can fire BEFORE the event (e.g. to validate/modify data before it is written) or AFTER it (e.g. to log the change or update a related table).
• Common uses: enforcing complex business rules that a simple CHECK constraint cannot express, auditing/logging changes, automatically keeping a derived/summary column in sync.

sequenceDiagram
    participant App as Application
    participant T as Table (Employee)
    participant Trg as Trigger
    participant Log as AuditLog Table
    App->>T: UPDATE Employee SET salary = ...
    T->>Trg: AFTER UPDATE trigger fires
    Trg->>Log: INSERT audit row (old_salary, new_salary, changed_by, date)

CREATE TABLE AuditLog (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT, old_salary DECIMAL(10,2), new_salary DECIMAL(10,2),
    changed_at DATETIME
);

CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON Employee
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO AuditLog(emp_id, old_salary, new_salary, changed_at)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
END;

Exam takeaway: a trigger = event-driven stored code (BEFORE/AFTER INSERT/UPDATE/DELETE) that runs automatically — most commonly used for auditing and enforcing business rules the schema alone cannot.