Constraints, transactions or triggers: which tool to choose?

Three problems, three moments of action

A constraint asks: does this line always respect a rule? A transaction asks: Should these operations succeed or fail together? A trigger asks: should we react automatically when a data event occurs?

The right choice starts with this difference. Use a trigger for all cache rules; using only the application lets each process reimplement the controls.

Constraint: prevent an invalid state

NOT NULL requires a value, UNIQUE avoids a repetition, CHECK checks a condition, and FOREIGN KEY guarantees the existence of a relationship. These rules are declarative and visible in the structure.

To prevent a negative price, CHECK is preferable to a trigger: the rule is short, atomic and understood directly by the DBMS.

CREATE TABLE produits (
  id_produit INTEGER PRIMARY KEY,
  prix NUMERIC NOT NULL CHECK (prix >= 0)
);

Transaction: Protect a compound operation

An order and its payment concern two tables, but form a single business intention. BEGIN opens transaction, COMMIT confirms and ROLLBACK cancels. SAVEPOINT offers a partial return in a longer transaction.

A transaction does not replace constraints: it guarantees the atomicity of operations, while constraints check each proposed state.

BEGIN;
INSERT INTO commandes (id_commande, montant) VALUES (108, 120);
INSERT INTO paiements (id_commande, montant) VALUES (108, 120);
-- COMMIT if everything succeeds, otherwise ROLLBACK
COMMIT;

Trigger: react to an event

PostgreSQL separates the trigger function and the trigger attached to the table. OLD contains the line before modification, NEW the proposed line. AFTER UPDATE is suitable for an already accepted change history.

BEFORE can modify or refuse NEW, but a constraint remains clearer for a simple rule. ROW performs the function for each row; STATEMENT executes it once for the statement.

CREATE FUNCTION journaliser_prix() RETURNS trigger AS $$
BEGIN
  INSERT INTO historique_prix (id_produit, ancien_prix, nouveau_prix)
  VALUES (OLD.id_produit, OLD.prix, NEW.prix);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_prix
AFTER UPDATE OF prix ON produits
FOR EACH ROW EXECUTE FUNCTION journaliser_prix();

When not to use a trigger

A trigger is particularly suitable for an audit or logging that must apply regardless of the caller. Even then, document the effect, test for errors, and measure the cost.

• A constraint directly expresses the simple rule.
• The logic must be visible in an application service and versioned with it.
• A massive modification would trigger expensive processing per line.
• The trigger risks modifying the same table and creating a recursion.
• The team lacks the necessary testing and supervision.

Quick choice guide

First ask if you are protecting a row, a set of operations, or a reaction to an event. Combine the tools when their responsibilities differ: CHECK refuses an invalid price, a transaction groups the modification and its payment, a trigger keeps the old accepted price.

Don't choose on code length. Choose on business rule, visibility, execution time, error behavior and cost under load.

It's up to you

Combine these cases: single email, debit/credit transfer, address history, order linked to a customer and order/payment creation. The expected responses are respectively constraint, transaction, possible trigger, foreign key and transaction.

Advanced micro-training remains in preparation until an editable and resettable PostgreSQL lab has passed its security and robustness testing.