Definition
- Trigger – procedural code stored in the database that executes automatically in response to a DML event on a table or view.
- BEFORE trigger – fires before the row change is written; can validate, modify, or reject the incoming data.
- AFTER trigger – fires after the change is applied; cannot alter the row but can react by auditing, notifying, or updating derived data.
- INSTEAD OF trigger – replaces the triggering action entirely; used mainly on views to make otherwise non-updatable views writable.
- Row-level – fires once per affected row, with access to
OLDandNEWrow images. - Statement-level – fires once per
INSERT/UPDATE/DELETEstatement, regardless of how many rows are affected.
A trigger is like a silent back-office rule at National Bank of India that fires by itself whenever something happens to a record. BEFORE a deposit is written, a rule can reject it if the amount is negative. AFTER a deposit is written, a rule automatically logs it to the audit book. INSTEAD OF someone trying to change a read-only summary report (a view), a rule redirects the change to the real underlying tables. Nobody calls these rules; they run automatically on the event.
Understanding the Concepts
What is a trigger?
A trigger is bound to a table (or view) and an event. It runs automatically when that event occurs. The trigger has access to the old and new row values and can perform any SQL or procedural logic.
-- PostgreSQL: basic trigger structure
CREATE OR REPLACE FUNCTION trg_audit_balance_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO account_audit(
account_id, old_balance, new_balance,
changed_by, changed_at
) VALUES (
NEW.account_id, OLD.balance, NEW.balance,
current_user, NOW()
);
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_accounts_after_update
AFTER UPDATE OF balance ON accounts
FOR EACH ROW
EXECUTE FUNCTION trg_audit_balance_change();
How does a BEFORE trigger work?
A BEFORE trigger runs before the row change is written. It can validate the data, modify incoming values, or reject the operation entirely by raising an error. It sees the proposed NEW values and can change them.
-- BEFORE INSERT: normalize and validate new account data
CREATE OR REPLACE FUNCTION trg_validate_account()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
-- Reject negative initial balance
IF NEW.balance < 0 THEN
RAISE EXCEPTION 'Initial balance cannot be negative: %',
NEW.balance;
END IF;
-- Normalize holder name to uppercase
NEW.holder_name := UPPER(TRIM(NEW.holder_name));
-- Set default status if not provided
IF NEW.status IS NULL THEN
NEW.status := 'ACTIVE';
END IF;
RETURN NEW; -- proceed with the modified row
END;
$$;
CREATE TRIGGER trg_accounts_before_insert
BEFORE INSERT ON accounts
FOR EACH ROW
EXECUTE FUNCTION trg_validate_account();
-- This insert gets normalized automatically
INSERT INTO accounts (account_id, holder_name, balance)
VALUES ('SAV2001', ' rajesh kumar ', 50000.00);
-- Stored as: holder_name = 'RAJESH KUMAR', status = 'ACTIVE'
-- This insert is rejected
INSERT INTO accounts (account_id, holder_name, balance)
VALUES ('SAV2002', 'Priya Sharma', -1000.00);
-- ERROR: Initial balance cannot be negative: -1000.00
How does an AFTER trigger work?
An AFTER trigger runs once the change is already applied. It cannot alter the row but can react to it: writing an audit record, updating a summary table, or sending a notification.
-- AFTER UPDATE: audit every balance change
CREATE OR REPLACE FUNCTION trg_audit_balance()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO account_audit (
account_id, operation, old_balance, new_balance,
changed_by, changed_at
) VALUES (
NEW.account_id, 'UPDATE', OLD.balance, NEW.balance,
current_user, NOW()
);
RETURN NULL; -- AFTER trigger return value is ignored
END;
$$;
CREATE TRIGGER trg_balance_audit
AFTER UPDATE OF balance ON accounts
FOR EACH ROW
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
EXECUTE FUNCTION trg_audit_balance();
How does an INSTEAD OF trigger work?
An INSTEAD OF trigger replaces the triggering action. It is used mainly on views that are otherwise not updatable, translating a write on the view into changes on the base tables.
-- View that joins two tables (not directly updatable)
CREATE VIEW customer_account_view AS
SELECT c.customer_id, c.full_name, a.account_id, a.balance
FROM customers c
JOIN accounts a ON c.customer_id = a.customer_id;
-- INSTEAD OF UPDATE: redirect the update to the correct base table
CREATE OR REPLACE FUNCTION trg_instead_update_customer_view()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE customers SET full_name = NEW.full_name
WHERE customer_id = NEW.customer_id;
UPDATE accounts SET balance = NEW.balance
WHERE account_id = NEW.account_id;
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_customer_view_update
INSTEAD OF UPDATE ON customer_account_view
FOR EACH ROW
EXECUTE FUNCTION trg_instead_update_customer_view();
What is the difference between row-level and statement-level triggers?
| Aspect | Row-level | Statement-level |
|---|---|---|
| Fires | Once per affected row | Once per SQL statement |
| Access to OLD/NEW | Yes | No |
| Use case | Audit each row, validate each row | Log that a bulk operation happened |
| Syntax | FOR EACH ROW | FOR EACH STATEMENT |
-- Row-level: fires for each of the 1000 updated rows
CREATE TRIGGER trg_row_level
AFTER UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION trg_audit_balance();
-- Statement-level: fires once for the entire UPDATE statement
CREATE TRIGGER trg_statement_level
AFTER UPDATE ON accounts
FOR EACH STATEMENT
EXECUTE FUNCTION trg_log_bulk_operation();
What are common use cases for triggers?
The most common use cases are auditing, enforcing complex business rules, and maintaining derived data.
-- Use case 1: Maintain a running total (derived data)
CREATE OR REPLACE FUNCTION trg_update_branch_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE branch_summary
SET total_deposits = total_deposits + (NEW.balance - OLD.balance)
WHERE branch_id = NEW.branch_id;
RETURN NULL;
END;
$$;
-- Use case 2: BEFORE UPDATE to prevent overdraft
CREATE OR REPLACE FUNCTION trg_prevent_overdraft()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.balance < 0 THEN
RAISE EXCEPTION 'Account % cannot go negative. '
'Current: %, Attempted: %',
NEW.account_id, OLD.balance, NEW.balance;
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_no_overdraft
BEFORE UPDATE OF balance ON accounts
FOR EACH ROW
EXECUTE FUNCTION trg_prevent_overdraft();
What are the disadvantages and risks of triggers?
Triggers are "hidden" side effects. A simple INSERT can silently fire cascading logic that surprises developers. They add overhead to every DML operation. They are hard to version-control, test, and debug. Poorly designed triggers can cause recursion or deadlocks.
-- Danger: cascading triggers
-- Trigger A on accounts fires on UPDATE
-- Trigger A updates branch_summary
-- Trigger B on branch_summary fires on UPDATE
-- Trigger B updates regional_summary
-- Trigger C on regional_summary fires on UPDATE
-- ... a simple balance change cascades through three triggers
-- Danger: recursive trigger
-- A trigger on accounts that also UPDATEs accounts
-- can fire itself infinitely (engine-dependent behavior)
package com.bank.service;
import java.math.BigDecimal;
import java.sql.*;
import java.util.logging.Logger;
public class AccountService {
private static final Logger LOG = Logger.getLogger(AccountService.class.getName());
private final Connection connection;
public AccountService(Connection connection) {
this.connection = connection;
}
public void debit(String accountId, BigDecimal amount) throws SQLException {
// The BEFORE trigger trg_no_overdraft runs automatically
// The AFTER trigger trg_balance_audit logs the change
// The developer does not call them; they fire on the UPDATE
String sql = "UPDATE accounts SET balance = balance - ? WHERE account_id = ?";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setBigDecimal(1, amount);
ps.setString(2, accountId);
ps.executeUpdate();
LOG.info("Debited " + amount + " from " + accountId);
} catch (SQLException e) {
// Trigger raised an exception (e.g., overdraft)
LOG.severe("Debit failed: " + e.getMessage());
throw e;
}
}
}
When should you use triggers and when should you avoid them?
Use triggers for cross-cutting, must-not-be-bypassed concerns tightly coupled to data: auditing, integrity rules that constraints cannot express, and keeping derived data in sync. Avoid pushing ordinary business logic into triggers. Keep triggers small, deterministic, and well-documented.
Guideline: Use triggers for: Avoid triggers for: ─────────────────── ───────────────────── Audit logging General business logic Overdraft prevention Complex workflows Data normalization Email/notification sending Derived column maintenance Cross-service orchestration Making views updatable Anything that should be testable
Visual Overview
┌──────────────────────────────────────────────────┐ │ Trigger Timing and Flow │ ├──────────────────────────────────────────────────┤ │ │ │ INSERT INTO accounts (...) │ │ │ │ │ ▼ │ │ ┌──────────────────────┐ │ │ │ BEFORE Trigger │ Can validate/modify │ │ │ NEW.balance >= 0? │ the incoming row │ │ │ Normalize name? │ or RAISE to reject │ │ └──────────┬───────────┘ │ │ │ (proceed or reject) │ │ ▼ │ │ ┌──────────────────────┐ │ │ │ Write the row │ The actual DML │ │ │ to the table │ operation happens │ │ └──────────┬───────────┘ │ │ │ │ │ ▼ │ │ ┌──────────────────────┐ │ │ │ AFTER Trigger │ React: audit log, │ │ │ Log to audit table │ update summary, │ │ │ Update summary │ send notification │ │ └──────────────────────┘ │ │ │ │ INSTEAD OF (on views only) │ │ ┌──────────────────────┐ │ │ │ Replaces the DML │ Redirects write │ │ │ entirely │ to base tables │ │ └──────────────────────┘ │ └──────────────────────────────────────────────────┘
Key Takeaway
A trigger is procedural code stored in the database that fires automatically in response to a DML event. Its timing determines what it can do. A BEFORE trigger runs before the row is written, so it can validate the incoming data, modify values, or reject the operation by raising an error. An AFTER trigger runs once the change is applied, so it cannot alter the row but can react by writing an audit record, updating a summary table, or sending a notification. An INSTEAD OF trigger replaces the triggering action entirely and is used mainly on views to make otherwise non-updatable views writable.
The main use cases at National Bank of India were a BEFORE UPDATE trigger on the accounts table to reject any change that would make a balance negative, and an AFTER UPDATE trigger to append an immutable audit row with the old and new balances, so those rules held no matter which application, batch job, or engineer wrote to the table.
The advantages are automatic enforcement that cannot be bypassed, centralized rules that work for every writer, and same-transaction consistency. The disadvantages are real: triggers are hidden side effects that complicate debugging, they add overhead to every DML operation, they are hard to version-control and test, and poorly designed triggers can cause recursion, cascading chains, or deadlocks.
Pankaj's guidance is to use triggers for cross-cutting, must-not-be-bypassed concerns tightly coupled to data, especially auditing and integrity that constraints cannot express, and to keep ordinary business logic in the application where it is testable and visible.
Remember one sentence: a trigger fires automatically on DML: BEFORE to validate or modify, AFTER to react or audit, and INSTEAD OF to redirect, usually on views.
---