Trigger functions in SQL

A stock floor enforced by the table itself: the trigger runs on every update.

-- A trigger function sees NEW and OLD; the trigger wires it to a table.
CREATE FUNCTION guard_stock()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.stock < 0 THEN
        RAISE EXCEPTION 'stock for % cannot go below zero', NEW.sku;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER stock_floor
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION guard_stock();

-- A legal update sails through the guard.
UPDATE products
SET stock = 3
WHERE sku = 'PFD-M';

SELECT sku, stock
FROM products
WHERE sku = 'PFD-M';

How it works

1. RETURNS trigger functions see NEW and OLD row values.
2. RAISE EXCEPTION vetoes the write; returning NEW lets it through.
3. CREATE TRIGGER ... BEFORE UPDATE ... FOR EACH ROW wires it to products.

Keywords and builtins used here

• AS
• BEFORE
• BEGIN
• CREATE
• EACH
• END
• EXCEPTION
• EXECUTE
• FOR
• FROM
• FUNCTION
• IF
• LANGUAGE
• NEW
• ON
• RETURN
• RETURNS
• ROW
• SELECT
• SET
• THEN
• TRIGGER
• UPDATE
• WHERE
• trigger

The run, in numbers

Lines

26

Characters to type

507

Tokens

79

Three-star pace

65 tpm

At the three-star pace of 65 tokens a minute, this run takes about 73 seconds.

Type this snippet

Step 4 of 4 in Errors & triggers, step 20 of 23 in PostgreSQL Stored Procedures.

← Previous Next →