1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>38.6.PL/Perl Triggers</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="plperl.html" title="Chapter38.PL/Perl - Perl Procedural Language">
<link rel="prev" href="plperl-trusted.html" title="38.5.Trusted and Untrusted PL/Perl">
<link rel="next" href="plperl-missing.html" title="38.7.Limitations and Missing Features">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="plperl-triggers"></a>38.6.PL/Perl Triggers</h2></div></div></div>
<p> PL/Perl can be used to write trigger functions. In a trigger function,
the hash reference <code class="varname">$_TD</code> contains information about the
current trigger event. The fields of the <code class="varname">$_TD</code> hash
reference are:
</p>
<div class="variablelist"><dl>
<dt><span class="term"><code class="literal">$_TD->{new}{foo}</code></span></dt>
<dd><p> <code class="literal">NEW</code> value of column <code class="literal">foo</code>
</p></dd>
<dt><span class="term"><code class="literal">$_TD->{old}{foo}</code></span></dt>
<dd><p> <code class="literal">OLD</code> value of column <code class="literal">foo</code>
</p></dd>
<dt><span class="term"><code class="literal">$_TD->{name}</code></span></dt>
<dd><p> Name of the trigger being called
</p></dd>
<dt><span class="term"><code class="literal">$_TD->{event}</code></span></dt>
<dd><p> Trigger event: <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, <code class="literal">DELETE</code>, or <code class="literal">UNKNOWN</code>
</p></dd>
<dt><span class="term"><code class="literal">$_TD->{when}</code></span></dt>
<dd><p> When the trigger was called: <code class="literal">BEFORE</code>, <code class="literal">AFTER</code>, or <code class="literal">UNKNOWN</code>
</p></dd>
<dt><span class="term"><code class="literal">$_TD->{level}</code></span></dt>
<dd><p> The trigger level: <code class="literal">ROW</code>, <code class="literal">STATEMENT</code>, or <code class="literal">UNKNOWN</code>
</p></dd>
<dt><span class="term"><code class="literal">$_TD->{relid}</code></span></dt>
<dd><p> OID of the table on which the trigger fired
</p></dd>
<dt><span class="term"><code class="literal">$_TD->{relname}</code></span></dt>
<dd><p> Name of the table on which the trigger fired
</p></dd>
<dt><span class="term"><code class="literal">$_TD->{argc}</code></span></dt>
<dd><p> Number of arguments of the trigger function
</p></dd>
<dt><span class="term"><code class="literal">@{$_TD->{args}}</code></span></dt>
<dd><p> Arguments of the trigger function. Does not exist if <code class="literal">$_TD->{argc}</code> is 0.
</p></dd>
</dl></div>
<p>
</p>
<p> Triggers can return one of the following:
</p>
<div class="variablelist"><dl>
<dt><span class="term"><code class="literal">return;</code></span></dt>
<dd><p> Execute the statement
</p></dd>
<dt><span class="term"><code class="literal">"SKIP"</code></span></dt>
<dd><p> Don't execute the statement
</p></dd>
<dt><span class="term"><code class="literal">"MODIFY"</code></span></dt>
<dd><p> Indicates that the <code class="literal">NEW</code> row was modified by
the trigger function
</p></dd>
</dl></div>
<p>
</p>
<p> Here is an example of a trigger function, illustrating some of the
above:
</p>
<pre class="programlisting">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 PROCEDURE valid_id();</pre>
<p>
</p>
</div></body>
</html>
|
