Deviopath › PostgreSQL

Stop Overselling

Hard · Triggers

A trigger can refuse a change: RAISE EXCEPTION 'message'; aborts the statement, so the row is never saved. A BEFORE INSERT trigger can also update other tables, which keeps related data in sync.

When a row is inserted into sales, check the product's stock:

- If stock is smaller than the sale's quantity, raise an exception (the sale is rejected and nothing changes). - Otherwise, subtract quantity from the product's stock and let the sale be saved.

Selling the last items is fine (stock may reach 0). The check tries several sales one by one, each inside its own exception handler, and shows which were accepted, the final stock, and how many sales were saved.