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
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>43.10. Trigger Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plpgsql-errors-and-messages.html" title="43.9. Errors and Messages" /><link rel="next" href="plpgsql-implementation.html" title="43.11. PL/pgSQL under the Hood" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">43.10. Trigger Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-errors-and-messages.html" title="43.9. Errors and Messages">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 43. <span class="application">PL/pgSQL</span> — <acronym class="acronym">SQL</acronym> Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpgsql-implementation.html" title="43.11. PL/pgSQL under the Hood">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-TRIGGER"><div class="titlepage"><div><div><h2 class="title" style="clear: both">43.10. Trigger Functions <a href="#PLPGSQL-TRIGGER" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpgsql-trigger.html#PLPGSQL-DML-TRIGGER">43.10.1. Triggers on Data Changes</a></span></dt><dt><span class="sect2"><a href="plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER">43.10.2. Triggers on Events</a></span></dt></dl></div><a id="id-1.8.8.12.2" class="indexterm"></a><p>
<span class="application">PL/pgSQL</span> can be used to define trigger
functions on data changes or database events.
A trigger function is created with the <code class="command">CREATE FUNCTION</code>
command, declaring it as a function with no arguments and a return type of
<code class="type">trigger</code> (for data change triggers) or
<code class="type">event_trigger</code> (for database event triggers).
Special local variables named <code class="varname">TG_<em class="replaceable"><code>something</code></em></code> are
automatically defined to describe the condition that triggered the call.
</p><div class="sect2" id="PLPGSQL-DML-TRIGGER"><div class="titlepage"><div><div><h3 class="title">43.10.1. Triggers on Data Changes <a href="#PLPGSQL-DML-TRIGGER" class="id_link">#</a></h3></div></div></div><p>
A <a class="link" href="triggers.html" title="Chapter 39. Triggers">data change trigger</a> is declared as a
function with no arguments and a return type of <code class="type">trigger</code>.
Note that the function must be declared with no arguments even if it
expects to receive some arguments specified in <code class="command">CREATE TRIGGER</code>
— such arguments are passed via <code class="varname">TG_ARGV</code>, as described
below.
</p><p>
When a <span class="application">PL/pgSQL</span> function is called as a
trigger, several special variables are created automatically in the
top-level block. They are:
</p><div class="variablelist"><dl class="variablelist"><dt id="PLPGSQL-DML-TRIGGER-NEW"><span class="term"><code class="varname">NEW</code> <code class="type">record</code></span> <a href="#PLPGSQL-DML-TRIGGER-NEW" class="id_link">#</a></dt><dd><p>
new database row for <code class="command">INSERT</code>/<code class="command">UPDATE</code> operations in row-level
triggers. This variable is null in statement-level triggers
and for <code class="command">DELETE</code> operations.
</p></dd><dt id="PLPGSQL-DML-TRIGGER-OLD"><span class="term"><code class="varname">OLD</code> <code class="type">record</code></span> <a href="#PLPGSQL-DML-TRIGGER-OLD" class="id_link">#</a></dt><dd><p>
old database row for <code class="command">UPDATE</code>/<code class="command">DELETE</code> operations in row-level
triggers. This variable is null in statement-level triggers
and for <code class="command">INSERT</code> operations.
</p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-NAME"><span class="term"><code class="varname">TG_NAME</code> <code class="type">name</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-NAME" class="id_link">#</a></dt><dd><p>
name of the trigger which fired.
</p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-WHEN"><span class="term"><code class="varname">TG_WHEN</code> <code class="type">text</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-WHEN" class="id_link">#</a></dt><dd><p>
<code class="literal">BEFORE</code>, <code class="literal">AFTER</code>, or
<code class="literal">INSTEAD OF</code>, depending on the trigger's definition.
</p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-LEVEL"><span class="term"><code class="varname">TG_LEVEL</code> <code class="type">text</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-LEVEL" class="id_link">#</a></dt><dd><p>
<code class="literal">ROW</code> or <code class="literal">STATEMENT</code>,
depending on the trigger's definition.
</p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-OP"><span class="term"><code class="varname">TG_OP</code> <code class="type">text</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-OP" class="id_link">#</a></dt><dd><p>
operation for which the trigger was fired:
<code class="literal">INSERT</code>, <code class="literal">UPDATE</code>,
<code class="literal">DELETE</code>, or <code class="literal">TRUNCATE</code>.
</p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-RELID"><span class="term"><code class="varname">TG_RELID</code> <code class="type">oid</code> (references <a class="link" href="catalog-pg-class.html" title="53.11. pg_class"><code class="structname">pg_class</code></a>.<code class="structfield">oid</code>)</span> <a href="#PLPGSQL-DML-TRIGGER-TG-RELID" class="id_link">#</a></dt><dd><p>
object ID of the table that caused the trigger invocation.
</p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-RELNAME"><span class="term"><code class="varname">TG_RELNAME</code> <code class="type">name</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-RELNAME" class="id_link">#</a></dt><dd><p>
table that caused the trigger
invocation. This is now deprecated, and could disappear in a future
release. Use <code class="literal">TG_TABLE_NAME</code> instead.
</p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-TABLE-NAME"><span class="term"><code class="varname">TG_TABLE_NAME</code> <code class="type">name</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-TABLE-NAME" class="id_link">#</a></dt><dd><p>
table that caused the trigger invocation.
</p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-TABLE-SCHEMA"><span class="term"><code class="varname">TG_TABLE_SCHEMA</code> <code class="type">name</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-TABLE-SCHEMA" class="id_link">#</a></dt><dd><p>
schema of the table that caused the trigger invocation.
</p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-NARGS"><span class="term"><code class="varname">TG_NARGS</code> <code class="type">integer</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-NARGS" class="id_link">#</a></dt><dd><p>
number of arguments given to the trigger
function in the <code class="command">CREATE TRIGGER</code> statement.
</p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-ARGV"><span class="term"><code class="varname">TG_ARGV</code> <code class="type">text[]</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-ARGV" class="id_link">#</a></dt><dd><p>
arguments from
the <code class="command">CREATE TRIGGER</code> statement.
The index counts from 0. Invalid
indexes (less than 0 or greater than or equal to <code class="varname">tg_nargs</code>)
result in a null value.
</p></dd></dl></div><p>
</p><p>
A trigger function must return either <code class="symbol">NULL</code> or a
record/row value having exactly the structure of the table the
trigger was fired for.
</p><p>
Row-level triggers fired <code class="literal">BEFORE</code> 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
<code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> does not occur
for this row). If a nonnull
value is returned then the operation proceeds with that row value.
Returning a row value different from the original value
of <code class="varname">NEW</code> alters the row that will be inserted or
updated. Thus, if the trigger function wants the triggering
action to succeed normally without altering the row
value, <code class="varname">NEW</code> (or a value equal thereto) has to be
returned. To alter the row to be stored, it is possible to
replace single values directly in <code class="varname">NEW</code> and return the
modified <code class="varname">NEW</code>, or to build a complete new record/row to
return. In the case of a before-trigger
on <code class="command">DELETE</code>, the returned value has no direct
effect, but it has to be nonnull to allow the trigger action to
proceed. Note that <code class="varname">NEW</code> is null
in <code class="command">DELETE</code> triggers, so returning that is
usually not sensible. The usual idiom in <code class="command">DELETE</code>
triggers is to return <code class="varname">OLD</code>.
</p><p>
<code class="literal">INSTEAD OF</code> triggers (which are always row-level triggers,
and may only be used on views) can return null to signal that they did
not perform any updates, and that the rest of the operation for this
row should be skipped (i.e., subsequent triggers are not fired, and the
row is not counted in the rows-affected status for the surrounding
<code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code>).
Otherwise a nonnull value should be returned, to signal
that the trigger performed the requested operation. For
<code class="command">INSERT</code> and <code class="command">UPDATE</code> operations, the return value
should be <code class="varname">NEW</code>, which the trigger function may modify to
support <code class="command">INSERT RETURNING</code> and <code class="command">UPDATE RETURNING</code>
(this will also affect the row value passed to any subsequent triggers,
or passed to a special <code class="varname">EXCLUDED</code> alias reference within
an <code class="command">INSERT</code> statement with an <code class="literal">ON CONFLICT DO
UPDATE</code> clause). For <code class="command">DELETE</code> operations, the return
value should be <code class="varname">OLD</code>.
</p><p>
The return value of a row-level trigger
fired <code class="literal">AFTER</code> or a statement-level trigger
fired <code class="literal">BEFORE</code> or <code class="literal">AFTER</code> is
always ignored; it might as well be null. However, any of these types of
triggers might still abort the entire operation by raising an error.
</p><p>
<a class="xref" href="plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE" title="Example 43.3. A PL/pgSQL Trigger Function">Example 43.3</a> shows an example of a
trigger function in <span class="application">PL/pgSQL</span>.
</p><div class="example" id="PLPGSQL-TRIGGER-EXAMPLE"><p class="title"><strong>Example 43.3. A <span class="application">PL/pgSQL</span> Trigger Function</strong></p><div class="example-contents"><p>
This example trigger ensures that any time a row is inserted or updated
in the table, the current user name and time are stamped into the
row. And it checks that an employee's name is given and that the
salary is a positive value.
</p><pre class="programlisting">
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- Who works for us when they must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE FUNCTION emp_stamp();
</pre></div></div><br class="example-break" /><p>
Another way to log changes to a table involves creating a new table that
holds a row for each insert, update, or delete that occurs. This approach
can be thought of as auditing changes to a table.
<a class="xref" href="plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE" title="Example 43.4. A PL/pgSQL Trigger Function for Auditing">Example 43.4</a> shows an example of an
audit trigger function in <span class="application">PL/pgSQL</span>.
</p><div class="example" id="PLPGSQL-TRIGGER-AUDIT-EXAMPLE"><p class="title"><strong>Example 43.4. A <span class="application">PL/pgSQL</span> Trigger Function for Auditing</strong></p><div class="example-contents"><p>
This example trigger ensures that any insert, update or delete of a row
in the <code class="literal">emp</code> table is recorded (i.e., audited) in the <code class="literal">emp_audit</code> table.
The current time and user name are stamped into the row, together with
the type of operation performed on it.
</p><pre class="programlisting">
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- making use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE FUNCTION process_emp_audit();
</pre></div></div><br class="example-break" /><p>
A variation of the previous example uses a view joining the main table
to the audit table, to show when each entry was last modified. This
approach still records the full audit trail of changes to the table,
but also presents a simplified view of the audit trail, showing just
the last modified timestamp derived from the audit trail for each entry.
<a class="xref" href="plpgsql-trigger.html#PLPGSQL-VIEW-TRIGGER-AUDIT-EXAMPLE" title="Example 43.5. A PL/pgSQL View Trigger Function for Auditing">Example 43.5</a> shows an example
of an audit trigger on a view in <span class="application">PL/pgSQL</span>.
</p><div class="example" id="PLPGSQL-VIEW-TRIGGER-AUDIT-EXAMPLE"><p class="title"><strong>Example 43.5. A <span class="application">PL/pgSQL</span> View Trigger Function for Auditing</strong></p><div class="example-contents"><p>
This example uses a trigger on the view to make it updatable, and
ensure that any insert, update or delete of a row in the view is
recorded (i.e., audited) in the <code class="literal">emp_audit</code> table. The current time
and user name are recorded, together with the type of operation
performed, and the view displays the last modified time of each row.
</p><pre class="programlisting">
CREATE TABLE emp (
empname text PRIMARY KEY,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer,
stamp timestamp NOT NULL
);
CREATE VIEW emp_view AS
SELECT e.empname,
e.salary,
max(ea.stamp) AS last_updated
FROM emp e
LEFT JOIN emp_audit ea ON ea.empname = e.empname
GROUP BY 1, 2;
CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
BEGIN
--
-- Perform the required operation on emp, and create a row in emp_audit
-- to reflect the change made to emp.
--
IF (TG_OP = 'DELETE') THEN
DELETE FROM emp WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
OLD.last_updated = now();
INSERT INTO emp_audit VALUES('D', current_user, OLD.*);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('U', current_user, NEW.*);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp VALUES(NEW.empname, NEW.salary);
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('I', current_user, NEW.*);
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
FOR EACH ROW EXECUTE FUNCTION update_emp_view();
</pre></div></div><br class="example-break" /><p>
One use of triggers is to maintain a summary table
of another table. The resulting summary can be used in place of the
original table for certain queries — often with vastly reduced run
times.
This technique is commonly used in Data Warehousing, where the tables
of measured or observed data (called fact tables) might be extremely large.
<a class="xref" href="plpgsql-trigger.html#PLPGSQL-TRIGGER-SUMMARY-EXAMPLE" title="Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table">Example 43.6</a> shows an example of a
trigger function in <span class="application">PL/pgSQL</span> that maintains
a summary table for a fact table in a data warehouse.
</p><div class="example" id="PLPGSQL-TRIGGER-SUMMARY-EXAMPLE"><p class="title"><strong>Example 43.6. A <span class="application">PL/pgSQL</span> Trigger Function for Maintaining a Summary Table</strong></p><div class="example-contents"><p>
The schema detailed here is partly based on the <span class="emphasis"><em>Grocery Store
</em></span> example from <span class="emphasis"><em>The Data Warehouse Toolkit</em></span>
by Ralph Kimball.
</p><pre class="programlisting">
--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE time_dimension (
time_key integer NOT NULL,
day_of_week integer NOT NULL,
day_of_month integer NOT NULL,
month integer NOT NULL,
quarter integer NOT NULL,
year integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
store_key integer NOT NULL,
amount_sold numeric(12,2) NOT NULL,
units_sold integer NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
--
-- Summary table - sales by time.
--
CREATE TABLE sales_summary_bytime (
time_key integer NOT NULL,
amount_sold numeric(15,2) NOT NULL,
units_sold numeric(12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold numeric(12);
delta_amount_cost numeric(15,2);
BEGIN
-- Work out the increment/decrement amount(s).
IF (TG_OP = 'DELETE') THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = 'UPDATE') THEN
-- forbid updates that change the time_key -
-- (probably not too onerous, as DELETE + INSERT is how most
-- changes will be made).
IF ( OLD.time_key != NEW.time_key) THEN
RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
ELSIF (TG_OP = 'INSERT') THEN
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-- Insert or update the summary row with the new values.
<<insert_update>>
LOOP
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
EXIT insert_update WHEN found;
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXIT insert_update;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
-- do nothing
END;
END LOOP insert_update;
RETURN NULL;
END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime();
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;
</pre></div></div><br class="example-break" /><p>
<code class="literal">AFTER</code> triggers can also make use of <em class="firstterm">transition
tables</em> to inspect the entire set of rows changed by the triggering
statement. The <code class="command">CREATE TRIGGER</code> command assigns names to one
or both transition tables, and then the function can refer to those names
as though they were read-only temporary tables.
<a class="xref" href="plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-TRANSITION-EXAMPLE" title="Example 43.7. Auditing with Transition Tables">Example 43.7</a> shows an example.
</p><div class="example" id="PLPGSQL-TRIGGER-AUDIT-TRANSITION-EXAMPLE"><p class="title"><strong>Example 43.7. Auditing with Transition Tables</strong></p><div class="example-contents"><p>
This example produces the same results as
<a class="xref" href="plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE" title="Example 43.4. A PL/pgSQL Trigger Function for Auditing">Example 43.4</a>, but instead of using a
trigger that fires for every row, it uses a trigger that fires once
per statement, after collecting the relevant information in a transition
table. This can be significantly faster than the row-trigger approach
when the invoking statement has modified many rows. Notice that we must
make a separate trigger declaration for each kind of event, since the
<code class="literal">REFERENCING</code> clauses must be different for each case. But
this does not stop us from using a single trigger function if we choose.
(In practice, it might be better to use three separate functions and
avoid the run-time tests on <code class="varname">TG_OP</code>.)
</p><pre class="programlisting">
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Create rows in emp_audit to reflect the operations performed on emp,
-- making use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit
SELECT 'D', now(), current_user, o.* FROM old_table o;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit
SELECT 'U', now(), current_user, n.* FROM new_table n;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit
SELECT 'I', now(), current_user, n.* FROM new_table n;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit_ins
AFTER INSERT ON emp
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_upd
AFTER UPDATE ON emp
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_del
AFTER DELETE ON emp
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
</pre></div></div><br class="example-break" /></div><div class="sect2" id="PLPGSQL-EVENT-TRIGGER"><div class="titlepage"><div><div><h3 class="title">43.10.2. Triggers on Events <a href="#PLPGSQL-EVENT-TRIGGER" class="id_link">#</a></h3></div></div></div><p>
<span class="application">PL/pgSQL</span> can be used to define
<a class="link" href="event-triggers.html" title="Chapter 40. Event Triggers">event triggers</a>.
<span class="productname">PostgreSQL</span> requires that a function that
is to be called as an event trigger must be declared as a function with
no arguments and a return type of <code class="literal">event_trigger</code>.
</p><p>
When a <span class="application">PL/pgSQL</span> function is called as an
event trigger, several special variables are created automatically
in the top-level block. They are:
</p><div class="variablelist"><dl class="variablelist"><dt id="PLPGSQL-EVENT-TRIGGER-TG-EVENT"><span class="term"><code class="varname">TG_EVENT</code> <code class="type">text</code></span> <a href="#PLPGSQL-EVENT-TRIGGER-TG-EVENT" class="id_link">#</a></dt><dd><p>
event the trigger is fired for.
</p></dd><dt id="PLPGSQL-EVENT-TRIGGER-TG-TAG"><span class="term"><code class="varname">TG_TAG</code> <code class="type">text</code></span> <a href="#PLPGSQL-EVENT-TRIGGER-TG-TAG" class="id_link">#</a></dt><dd><p>
command tag for which the trigger is fired.
</p></dd></dl></div><p>
</p><p>
<a class="xref" href="plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER-EXAMPLE" title="Example 43.8. A PL/pgSQL Event Trigger Function">Example 43.8</a> shows an example of an
event trigger function in <span class="application">PL/pgSQL</span>.
</p><div class="example" id="PLPGSQL-EVENT-TRIGGER-EXAMPLE"><p class="title"><strong>Example 43.8. A <span class="application">PL/pgSQL</span> Event Trigger Function</strong></p><div class="example-contents"><p>
This example trigger simply raises a <code class="literal">NOTICE</code> message
each time a supported command is executed.
</p><pre class="programlisting">
CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();
</pre></div></div><br class="example-break" /></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql-errors-and-messages.html" title="43.9. Errors and Messages">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpgsql-implementation.html" title="43.11. PL/pgSQL under the Hood">Next</a></td></tr><tr><td width="40%" align="left" valign="top">43.9. Errors and Messages </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 43.11. <span class="application">PL/pgSQL</span> under the Hood</td></tr></table></div></body></html>
|
