Trigger-based validation that rejects bad inserts

A trigger runs a function automatically before every INSERT. If the new row breaks a rule, the function raises an error and the row is never saved.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Stopping bad rows at the door with a trigger

CREATE OR REPLACE FUNCTION check_revenue() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN IF NEW.revenue < 0 THEN RAISE EXCEPTION 'Revenue cannot be negative'; END IF; RETURN NEW; END $$;
CREATE TRIGGER revenue_check BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION check_revenue()

Result:

Step by step

A trigger function RETURNS TRIGGER and is written in PL/pgSQL.

CREATE OR REPLACE FUNCTION check_revenue() RETURNS TRIGGER LANGUAGE plpgsql

If the new row's revenue is below 0, raise an error. Otherwise RETURN NEW lets it in.

CREATE OR REPLACE FUNCTION check_revenue() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN IF NEW.revenue < 0 THEN RAISE EXCEPTION 'Revenue cannot be negative'; END IF; RETURN NEW; END $$;

BEFORE INSERT ... FOR EACH ROW runs the check on every new row.

CREATE OR REPLACE FUNCTION check_revenue() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN IF NEW.revenue < 0 THEN RAISE EXCEPTION 'Revenue cannot be negative'; END IF; RETURN NEW; END $$;
CREATE TRIGGER revenue_check BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION check_revenue();

Practice

Try each one in your head first, then open the answer.

1. check_revenue() already exists. Attach it to orders as a trigger called revenue_check that runs before every insert.

CREATE TRIGGER revenue_check BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION check_revenue()

2. check_price() already exists. Attach it to products as a trigger called price_check that runs before every insert.

CREATE TRIGGER price_check BEFORE INSERT ON products FOR EACH ROW EXECUTE FUNCTION check_price()

3. Write check_revenue(): a trigger function that raises 'Revenue cannot be negative' when NEW.revenue < 0, and otherwise returns NEW.

CREATE OR REPLACE FUNCTION check_revenue() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN IF NEW.revenue < 0 THEN RAISE EXCEPTION 'Revenue cannot be negative'; END IF; RETURN NEW; END $$

4. Write check_revenue() (raise 'Revenue cannot be negative' when NEW.revenue < 0, otherwise return NEW), then attach it to orders as revenue_check, before every insert.

CREATE OR REPLACE FUNCTION check_revenue() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN IF NEW.revenue < 0 THEN RAISE EXCEPTION 'Revenue cannot be negative'; END IF; RETURN NEW; END $$; CREATE TRIGGER revenue_check BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION check_revenue()

5. Write check_category(): raise 'Category is required' when NEW.category IS NULL, otherwise return NEW. Attach it to orders as category_check, before every insert.

CREATE OR REPLACE FUNCTION check_category() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN IF NEW.category IS NULL THEN RAISE EXCEPTION 'Category is required'; END IF; RETURN NEW; END $$; CREATE TRIGGER category_check BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION check_category()

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed