8.1 CREATE TRIGGER

In addition to features of PostgreSQL, triggers can be created with OR REPLACE option and DO option.

Synopsis

CREATE [ OR REPLACE ][ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    { EXECUTE PROCEDURE function_name ( arguments ) | DO [ LANGUAGE lang_name ] code }

Description

Refer to the PostgreSQL Documentation for information about CREATE TRIGGER. This section describes OR REPLACE option and DO option.

A trigger which is created with OR REPLACE option and DO option will be associated with the specified table or view and will execute the specified code by the specified procedural language of DO (unnamed code block) when certain events occur.

Parameters

OR REPLACE: If the specified trigger is not defined in the table, it defines a new trigger.
If the specified trigger is already defined in the table, the named trigger replaces existing trigger.

code: When the certain events occur, it executes the code in a specified procedural language. The unnamed code block does not require a prior definition like a function. Syntax is same as procedural language.

lang_name: The name of the language that the function is implemented in. Can be SQL, C, internal, or the name of a user-defined procedural language. For backward compatibility, the name can be enclosed by single quotes. The default is 'plpgsql'.

Note

A normal trigger cannot be replaced by a constraint trigger.
A constraint trigger cannot be replaced by a normal trigger.
A trigger defined with DO option cannot be replaced by a trigger defined with EXECUTE PROCEDURE option.
A trigger defined with EXECUTE PROCEDURE option cannot be replaced by a trigger defined with DO option.

Examples

CREATE TRIGGER view_insert
    INSTEAD OF INSERT ON my_view
    FOR EACH ROW
    EXECUTE PROCEDURE view_insert_row();

Example 1:
To replace the above trigger, issues the following statement. This trigger executes view_insert_row() to update the table referred by the view.

CREATE OR REPLACE TRIGGER view_insert
    INSTEAD OF UPDATE ON my_view
    FOR EACH ROW
    EXECUTE PROCEDURE view_insert_row();

Example 2:
It executes the code block that is specified by DO before the table is updated.
(Example that LANGUAGE is plpgsql)

CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts 
    FOR EACH ROW 
    DO $$BEGIN RETURN NEW; END;$$ ;

Information

When a trigger created with DO option, a new function is created internally. The name of function is "schema name"."on table name"_"trigger name"_TRIGPROC(serial number).