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. If 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$;

The statement LANGUAGE PLPGSQL tells postgres that we can using plpgsql syntax, which is more powerful than plain SQL. 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 4.1.2.4) 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();

References#

• 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
• https://www.postgresqltutorial.com/postgresql-triggers/creating-first-trigger-postgresql/