How to create a trigger procedure in PostgreSQL?
A trigger procedure is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger. Note that the function must be declared with no arguments even if it expects to receive arguments specified in CREATE TRIGGER — trigger arguments are passed via TG_ARGV, as described below.
Is the create trigger function the same as the procedure?
In the syntax of CREATE TRIGGER, the keywords FUNCTION and PROCEDURE are equivalent, but the referenced function must in any case be a function, not a procedure. The use of the keyword PROCEDURE here is historical and deprecated.
What are the data types in PostgreSQL trigger statement?
Data type name; the name of the table that caused the trigger invocation. Data type name; the name of the schema of the table that caused the trigger invocation. Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement. Data type array of text; the arguments from the CREATE TRIGGER statement.
When is a PL / pgSQL function called as a trigger?
When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are: Data type RECORD; variable holding the new database row for INSERT / UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETE operations.
When to return NULL in PostgreSQL trigger manager?
Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT / UPDATE / DELETE does not occur for this row). If a nonnull value is returned then the operation proceeds with that row value.
Why is the trigger variable special in PostgreSQL?
The new variable in a trigger function is special, representing the row being inserted. Specifying the trigger as a before insert trigger means you can modify the row before it is written to the table.
When to return NULL in PostgreSQL trigger function?
Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT / UPDATE / DELETE does not occur for this row). If a nonnull value is returned then the operation proceeds with that row value.