When would you use a trigger vs application code vs a database constraint?

Use constraints for simple validation (NOT NULL, CHECK, FOREIGN KEY). Use triggers for automation that must happen every time (audit logging, denormalization). Use application code for business logic that changes frequently. Hierarchy: Constraints (simplest) → Triggers (automatic) → Application (flexible). Example: CHECK constraint for price > 0, trigger for audit logging, application code for email notifications.

What are the performance implications of triggers?

Triggers add overhead. Simple triggers: 5-10ms. Complex triggers: 50-100ms. This multiplies with volume. Optimization: Keep simple, use WHEN clause, use statement-level for bulk, avoid queries, monitor with EXPLAIN ANALYZE. Trade-off: Triggers ensure consistency but cost performance.

How do you debug a trigger that is causing issues?

Query trigger metadata, temporarily disable trigger, add logging, use RAISE NOTICE, check execution time with EXPLAIN ANALYZE, review audit logs, test in isolation. Key is making hidden behavior visible through logging and monitoring.

Create a trigger that updates product inventory when an order is placed.

CREATE FUNCTION update_inventory() RETURNS TRIGGER AS $$

BEGIN

UPDATE products

SET stock = stock - NEW.quantity

WHERE product_id = NEW.product_id;

IF (SELECT stock FROM products WHERE product_id = NEW.product_id) < 0 THEN

RAISE EXCEPTION 'Insufficient inventory';

END IF;

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER maintain_inventory

AFTER INSERT ON order_items

FOR EACH ROW

EXECUTE FUNCTION update_inventory();

Trigger fires after order item insert. Updates stock automatically. Validates sufficient inventory. Raises exception if stock negative. Executes in same transaction, so if check fails, order rolls back.