PL/pgSQL Trigger Procedures

PL/pgSQL can be used to define trigger procedures. 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.

触发器函数通过CREATE FUNCTION定义，无参数，且返回类型必须为trigger，如果需要向函数里传递参数，可以借助TG_ARGV。

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:

触发器函数在被调用的时候，一些特殊变量会自动被创建：

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is unassigned in statement-level triggers and for DELETE operations.

OLD

Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is unassigned in statement-level triggers and for INSERT operations.

TG_NAME

Data type name; variable that contains the name of the trigger actually fired.

TG_WHEN

Data type text; a string of BEFORE, AFTER, or INSTEAD OF, depending on the trigger's definition.

TG_LEVEL

Data type text; a string of either ROW or STATEMENT depending on the trigger's definition.

TG_OP

Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.

TG_TABLE_NAME

Data type name; the name of the table that caused the trigger invocation.

TG_TABLE_SCHEMA

Data type name; the name of the schema of the table that caused the trigger invocation.

TG_NARGS

Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement.

TG_ARGV[]

Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.

例子：

CREATE OR REPLACE FUNCTION insert_a_feature()
  RETURNS trigger AS
$BODY$
declare
	new_fid char varying;
begin
	new_fid := NEW.fid;
	EXECUTE format('delete from %I where fid=%s', TG_TABLE_NAME, quote_literal(new_fid));
	return NEW;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION insert_a_feature()
  OWNER TO postgres;

其中：quote_literal返回给出字串的一个适用于在sql语句字串里当做文本使用的形式。

转自：http://www.postgresql.org/docs/current/static/plpgsql-trigger.html