Creating A Trigger in PostgreSQL
We can use trigger to automate oerations, for example, preventing insertion of a record if certain condition is met. In this post, we show an example trigger function and its usage.
Suppose we have a
loan table with following column:
copy_id: id for an item
lend_date: date when it is lent
return_date: date when it is returned, can be
NULL(has been returned yet)
Create a trigger function
In this example, we create a function to check whether the new record is a valid record.
lend_date for new record is later than existing record, and is smaller than
return_date of the copy,
or if this copy hasn’t been returned (
loan.return_data IS NULL), then this is invalid record.
CREATE OR REPLACE FUNCTION check_loan_validity() RETURNS TRIGGER LANGUAGE PLPGSQL AS $func$ BEGIN IF EXISTS(SELECT 1 FROM loan WHERE loan.copy_id = NEW.copy_id AND NEW.lend_date >= loan.lend_date AND (NEW.lend_date <= loan.return_date OR loan.return_date IS NULL)) THEN RAISE EXCEPTION 'Invalid lend_date provided: %', NEW.lend_date; END IF; RETURN NEW; END; $func$;
LANGUAGE PLPGSQL tells postgres that we can using plpgsql syntax, which is more powerful than plain
See more discussion here.
You can also put the language declaration after the function body (see also this post):
$func$ <function body> $func$ LANGUAGE PLPGSQL;
All string inside the dollar signs are function body definition. The use of dollar sign here is to simplify the writing of the function, as indicated by official doc on functions:
The function body is simply a string literal so far as CREATE FUNCTION is concerned. It is often helpful to use dollar quoting (see Section 18.104.22.168) to write the function body, rather than the normal single quote syntax. Without dollar quoting, any single quotes or backslashes in the function body must be escaped by doubling them
When creating a trigger function, the variable
NEW represents the new records that is going to be inserted or updated.
Create the trigger
After creating the trigger function, we can now create a trigger on this table.
CREATE TRIGGER check_loan_trigger BEFORE INSERT ON loan FOR EACH ROW EXECUTE PROCEDURE check_loan_validity();
- dollar quoting: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING
- Why are
$$used in PL/PgSQL: https://stackoverflow.com/a/12172353/6064933
- official doc on trigger: https://www.postgresql.org/docs/current/plpgsql-trigger.html
License CC BY-NC-ND 4.0