Let us build three real triggers that you will see in production systems. Each one solves a different problem. Each one creates a different kind of side effect that QA must verify.
Every bank, insurance company, and healthcare system needs an audit trail. When someone changes a balance, we need to know: Who changed it? What was the old value? What is the new value? When did it happen? This is not optional — it is a regulatory requirement.
-- Audit log table
CREATE TABLE account_audit_log (
audit_id INT AUTO_INCREMENT PRIMARY KEY,
account_id VARCHAR(20) NOT NULL,
field_changed VARCHAR(50) NOT NULL,
old_value VARCHAR(255),
new_value VARCHAR(255),
changed_by VARCHAR(100),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- The trigger
DELIMITER //
CREATE TRIGGER trg_account_balance_audit
AFTER UPDATE ON accounts
FOR EACH ROW
BEGIN
-- Only log if balance actually changed
IF OLD.balance != NEW.balance THEN
INSERT INTO account_audit_log
(account_id, field_changed, old_value, new_value, changed_by)
VALUES
(OLD.account_id, 'balance',
CAST(OLD.balance AS CHAR),
CAST(NEW.balance AS CHAR),
CURRENT_USER());
END IF;
-- Also log status changes
IF OLD.status != NEW.status THEN
INSERT INTO account_audit_log
(account_id, field_changed, old_value, new_value, changed_by)
VALUES
(OLD.account_id, 'status',
OLD.status, NEW.status,
CURRENT_USER());
END IF;
END //
DELIMITER ;DELIMITER //
CREATE TRIGGER trg_validate_salary_change
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
-- Rule 1: Salary cannot decrease by more than 10%
IF NEW.salary < (OLD.salary * 0.90) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary reduction cannot exceed 10%';
END IF;
-- Rule 2: Salary cannot exceed department max
-- (Business rule that cannot be a simple CHECK constraint)
IF NEW.salary > (
SELECT max_salary FROM departments WHERE dept_id = NEW.dept_id
) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary exceeds department maximum';
END IF;
-- Rule 3: Auto-set updated_at timestamp
SET NEW.updated_at = NOW();
END //
DELIMITER ;DELIMITER //
CREATE TRIGGER trg_update_order_total
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
-- Recalculate order total whenever a new item is added
UPDATE orders
SET total_amount = (
SELECT SUM(quantity * unit_price)
FROM order_items
WHERE order_id = NEW.order_id
),
item_count = (
SELECT COUNT(*)
FROM order_items
WHERE order_id = NEW.order_id
),
updated_at = NOW()
WHERE order_id = NEW.order_id;
END //
-- Also handle item deletion
CREATE TRIGGER trg_update_order_total_on_delete
AFTER DELETE ON order_items
FOR EACH ROW
BEGIN
UPDATE orders
SET total_amount = (
SELECT COALESCE(SUM(quantity * unit_price), 0)
FROM order_items
WHERE order_id = OLD.order_id
),
item_count = (
SELECT COUNT(*)
FROM order_items
WHERE order_id = OLD.order_id
),
updated_at = NOW()
WHERE order_id = OLD.order_id;
END //
DELIMITER ;Notice we used COALESCE in the DELETE trigger but not in the INSERT trigger. After an INSERT, there is always at least one row. But after a DELETE, there might be zero rows left — and SUM of zero rows returns NULL, not 0. This is a real bug you should look for.
-- List all triggers in a database
SHOW TRIGGERS FROM your_database;
-- See full trigger code
SHOW CREATE TRIGGER trg_account_balance_audit;
-- Drop a trigger
DROP TRIGGER IF EXISTS trg_account_balance_audit;
-- Query trigger metadata
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE,
ACTION_TIMING, ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_database';Q: Can you give an example of a BEFORE trigger for data validation?
A: A common BEFORE UPDATE trigger on an employees table validates salary changes. Before any salary update is saved, the trigger checks: (1) the new salary is not more than 10% lower than the old salary (preventing drastic pay cuts), and (2) the new salary does not exceed the department maximum. If either rule is violated, the trigger uses SIGNAL SQLSTATE 45000 to reject the update with a descriptive error message. The trigger can also auto-set fields like updated_at = NOW(). This validation cannot be done with simple CHECK constraints because it involves cross-table lookups and OLD vs NEW comparisons.
Key Point: Production triggers solve three problems: audit logging (AFTER UPDATE), data validation (BEFORE UPDATE with SIGNAL), and auto-calculation (AFTER INSERT/DELETE). Each creates side effects that QA must verify.