You're building an e-commerce platform. When an order is placed, you need to: (1) Update product inventory. (2) Log the transaction for auditing. (3) Update customer lifetime value statistics. (4) Send notification to warehouse system.

You could do this in application code, but what if someone writes directly to the database? What if there's a bug in the application? What if you have multiple applications accessing the same database?

A trigger ensures these actions happen automatically, every time, no exceptions. When a row is inserted into orders table, the trigger fires and executes all the necessary updates. The database guarantees it happens atomically within the same transaction.

Stripe uses triggers to automatically log every transaction change for auditing. When a transaction amount is updated, a trigger records the old value, new value, who changed it, and when. This audit trail is required for PCI compliance and fraud investigation. The trigger ensures no change goes unlogged, even if application code has a bug.

Amazon uses triggers to maintain denormalized product data. When a product name changes, a trigger updates all orders that reference that product. This keeps historical order data accurate without requiring application code to remember to update multiple tables.

Healthcare systems use triggers to enforce medication safety rules. When a prescription is inserted, a trigger checks for drug interactions, validates dosage ranges, and logs the prescription for regulatory compliance. These checks happen at the database level, ensuring safety even if the application has a bug.

Stripe: Audit Logging for PCI Compliance

Stripe logs every transaction change for PCI compliance. Triggers ensure every change is logged automatically without manual code.

Triggers on transactions table log every INSERT, UPDATE, DELETE. Audit log stores old/new values, who changed it, when. Immutable audit trail.

CREATE FUNCTION log_transaction_changes() RETURNS TRIGGER AS $$

BEGIN

INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)

VALUES (

'transactions',

COALESCE(NEW.transaction_id, OLD.transaction_id),

TG_OP,

CASE WHEN TG_OP != 'INSERT' THEN row_to_json(OLD) ELSE NULL END,

CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW) ELSE NULL END,

current_user

);

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_transactions

AFTER INSERT OR UPDATE OR DELETE ON transactions

FOR EACH ROW

EXECUTE FUNCTION log_transaction_changes();

Logs 1 billion transaction changes per day

Zero missed audit logs - trigger cannot be bypassed

PCI compliance guaranteed at database level

Fraud investigation uses audit trail

Trigger overhead: 5ms per transaction

PostgreSQL

Amazon: Denormalization Maintenance

Amazon orders have denormalized product names. Triggers update orders when product name changes to maintain historical accuracy.

Trigger on products table updates orders.product_name when products.name changes. Ensures historical accuracy without manual updates.

CREATE FUNCTION sync_product_name() RETURNS TRIGGER AS $$

BEGIN

IF OLD.name IS DISTINCT FROM NEW.name THEN

UPDATE orders