Testing triggers is tricky because you never call them directly. You perform an action on the table, and the trigger fires silently in the background. Your job is to verify the side effects. If you are not looking for them, you will miss them.
Identify the trigger — what table, what event (INSERT/UPDATE/DELETE), what timing (BEFORE/AFTER)
Read the trigger code — understand what side effects it creates
Set up test data in the source table AND any tables the trigger reads from
Perform the triggering action (INSERT, UPDATE, or DELETE)
Check the side effect tables — was the audit log created? Was the total recalculated?
For BEFORE triggers — verify the data was modified or rejected as expected
Test with data that should NOT trigger the action — verify the trigger stays silent
-- Test 1: Balance change should create audit log
SELECT COUNT(*) FROM account_audit_log WHERE account_id = 'ACC001';
-- Note the count (e.g., 0)
UPDATE accounts SET balance = 45000.00 WHERE account_id = 'ACC001';
SELECT * FROM account_audit_log
WHERE account_id = 'ACC001'
ORDER BY changed_at DESC LIMIT 1;
-- Expected: field_changed = 'balance', old_value = '50000.00', new_value = '45000.00'
-- Test 2: Non-balance update should NOT create audit log
SELECT COUNT(*) AS before_count FROM account_audit_log WHERE account_id = 'ACC001';
UPDATE accounts SET holder_name = 'Rajesh K Verma' WHERE account_id = 'ACC001';
SELECT COUNT(*) AS after_count FROM account_audit_log WHERE account_id = 'ACC001';
-- Expected: before_count = after_count (no new entry)
-- Test 3: Status change should create audit log
UPDATE accounts SET status = 'FROZEN' WHERE account_id = 'ACC001';
SELECT * FROM account_audit_log
WHERE account_id = 'ACC001' AND field_changed = 'status'
ORDER BY changed_at DESC LIMIT 1;
-- Expected: old_value = 'ACTIVE', new_value = 'FROZEN'
-- Test 4: Update to same value should NOT create audit log
UPDATE accounts SET balance = 45000.00 WHERE account_id = 'ACC001';
-- Balance is already 45000.00, so trigger's IF condition should skip
SELECT COUNT(*) FROM account_audit_log WHERE account_id = 'ACC001';
-- Count should NOT increase-- Setup
INSERT INTO departments (dept_id, dept_name, max_salary)
VALUES (1, 'Engineering', 200000.00);
INSERT INTO employees (emp_id, emp_name, salary, dept_id)
VALUES (1, 'Ravi Kumar', 100000.00, 1);
-- Test 1: Valid salary increase — should succeed
UPDATE employees SET salary = 120000.00 WHERE emp_id = 1;
SELECT salary FROM employees WHERE emp_id = 1;
-- Expected: 120000.00
-- Test 2: Salary cut > 10% — should be REJECTED
UPDATE employees SET salary = 100000.00 WHERE emp_id = 1;
-- 120000 * 0.90 = 108000. Trying to set 100000 < 108000
-- Expected: ERROR — Salary reduction cannot exceed 10%
SELECT salary FROM employees WHERE emp_id = 1;
-- Expected: Still 120000.00 (unchanged)
-- Test 3: Salary exceeds department max — should be REJECTED
UPDATE employees SET salary = 250000.00 WHERE emp_id = 1;
-- Expected: ERROR — Salary exceeds department maximum
-- Test 4: Exactly 10% reduction — should succeed
UPDATE employees SET salary = 108000.00 WHERE emp_id = 1;
-- 120000 * 0.90 = 108000. Exactly 10% — boundary test
-- Expected: SUCCESS
-- Test 5: Verify updated_at was auto-set
SELECT updated_at FROM employees WHERE emp_id = 1;
-- Should be approximately NOW()Trigger chains happen when trigger A modifies table B, and table B has its own trigger that modifies table C. This is the domino effect. It is the most dangerous pattern in databases and the hardest to test.
-- Scenario: Insert an order item → trigger updates order total
-- → that update fires the audit trigger
-- Setup
INSERT INTO orders (order_id, customer_id, total_amount, item_count)
VALUES (1001, 101, 0.00, 0);
-- Action: Add an item
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1001, 501, 2, 250.00);
-- Verify trigger 1: Order total updated
SELECT total_amount, item_count FROM orders WHERE order_id = 1001;
-- Expected: total_amount = 500.00, item_count = 1
-- Verify trigger 2: Audit log created for the order total change
SELECT * FROM order_audit_log
WHERE order_id = 1001
ORDER BY changed_at DESC LIMIT 1;
-- Expected: old_total = 0.00, new_total = 500.00
-- Add another item and verify the chain again
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1001, 502, 1, 1000.00);
SELECT total_amount, item_count FROM orders WHERE order_id = 1001;
-- Expected: total_amount = 1500.00, item_count = 2In MySQL, a trigger cannot modify the same table it is defined on. If you try, you get ERROR 1442: Can not update table in stored function/trigger because it is already used by statement that invoked this stored function/trigger. This is a MySQL limitation, not a bug. PostgreSQL allows it with some restrictions.
Q: How do you test database triggers? What challenges do you face?
A: I test triggers by performing the triggering action (INSERT/UPDATE/DELETE) and then verifying all side effects in related tables. My approach: (1) Check that the trigger fires when it should — verify audit logs, recalculated totals, or validation errors. (2) Check that the trigger does NOT fire when it should not — for example, updating a non-tracked column should not create an audit entry. (3) Test boundary values in BEFORE triggers — like exactly 10% salary reduction. (4) Test trigger chains — action on table A fires trigger that modifies table B, which fires another trigger on table C. The main challenges are: triggers are invisible to the caller, chains can cascade unpredictably, and debugging is harder because there is no call stack to trace.
Key Point: Test triggers by verifying their side effects. Always test three things: the trigger fires when expected, it stays silent when not expected, and chains propagate correctly.