Postgresql 中文操作指南
45.6. PL/Perl Triggers #
PL/Perl 可以用来编写触发函数。在触发函数中,哈希引用 $_TD 包含关于当前触发事件的信息。$_TD 是一个全局变量,它会为触发器的每次调用获取一个单独的局部值。$_TD 哈希引用的域为:
PL/Perl can be used to write trigger functions. In a trigger function, the hash reference $_TD contains information about the current trigger event. $_TD is a global variable, which gets a separate local value for each invocation of the trigger. The fields of the $_TD hash reference are:
-
$_TD→{new}{foo}
-
NEW value of column foo
-
-
$_TD→{old}{foo}
-
OLD value of column foo
-
-
$_TD→{name}
-
Name of the trigger being called
-
-
$_TD→{event}
-
Trigger event: INSERT, UPDATE, DELETE, TRUNCATE, or UNKNOWN
-
-
$_TD→{when}
-
When the trigger was called: BEFORE, AFTER, INSTEAD OF, or UNKNOWN
-
-
$_TD→{level}
-
The trigger level: ROW, STATEMENT, or UNKNOWN
-
-
$_TD→{relid}
-
OID of the table on which the trigger fired
-
-
$_TD→{table_name}
-
Name of the table on which the trigger fired
-
-
$_TD→{relname}
-
Name of the table on which the trigger fired. This has been deprecated, and could be removed in a future release. Please use $_TD→{table_name} instead.
-
-
$_TD→{table_schema}
-
Name of the schema in which the table on which the trigger fired, is
-
-
$_TD→{argc}
-
Number of arguments of the trigger function
-
-
@{$_TD→{args}}
-
Arguments of the trigger function. Does not exist if $_TD→{argc} is 0.
-
行级触发器可以返回以下任一内容:
Row-level triggers can return one of the following:
-
return;
-
Execute the operation
-
-
"SKIP"
-
Don’t execute the operation
-
-
"MODIFY"
-
Indicates that the NEW row was modified by the trigger function
-
以下是一个触发器函数的示例,展示了以上部分内容:
Here is an example of a trigger function, illustrating some of the above:
CREATE TABLE test (
i int,
v varchar
);
CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) {
return "SKIP"; # skip INSERT/UPDATE command
} elsif ($_TD->{new}{v} ne "immortal") {
$_TD->{new}{v} .= "(modified by trigger)";
return "MODIFY"; # modify row and execute INSERT/UPDATE command
} else {
return; # execute INSERT/UPDATE command
}
$$ LANGUAGE plperl;
CREATE TRIGGER test_valid_id_trig
BEFORE INSERT OR UPDATE ON test
FOR EACH ROW EXECUTE FUNCTION valid_id();