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
|
<!-- doc/src/sgml/event-trigger.sgml -->
<chapter id="event-triggers">
<title>Event Triggers</title>
<indexterm zone="event-triggers">
<primary>event trigger</primary>
</indexterm>
<para>
To supplement the trigger mechanism discussed in <xref linkend="triggers">,
<productname>PostgreSQL</> also provides event triggers. Unlike regular
triggers, which are attached to a single table and capture only DML events,
event triggers are global to a particular database and are capable of
capturing DDL events.
</para>
<para>
Like regular triggers, event triggers can be written in any procedural
language that includes event trigger support, or in C, but not in plain
SQL.
</para>
<sect1 id="event-trigger-definition">
<title>Overview of Event Trigger Behavior</title>
<para>
An event trigger fires whenever the event with which it is associated
occurs in the database in which it is defined. Currently, the only
supported event is <literal>ddl_command_start</>. Support for
additional events may be added in future releases.
</para>
<para>
The <literal>ddl_command_start</> event occurs just before the
execution of a <literal>CREATE</>, <literal>ALTER</>, or <literal>DROP</>
commmand. As an exception, however, this event does not occur for
DDL commands targeting shared objects - databases, roles, and tablespaces
- or for command targeting event triggers themselves. The event trigger
mechanism does not support these object types.
<literal>ddl_command_start</> also occurs just before the execution of a
<literal>SELECT INTO</literal> command, since this is equivalent to
<literal>CREATE TABLE AS</literal>.
</para>
<para>
For a complete list of commands supported by the event trigger mechanism,
see <xref linkend="event-trigger-matrix">.
</para>
<para>
In order to create an event trigger, you must first create a function with
the special return type <literal>event_trigger</literal>. This function
need not (and may not) return a value; the return type serves merely as
a signal that the function is to be invoked as an event trigger.
</para>
<para>
If more than one event trigger is defined for a particular event, they will
fire in alphabetical order by trigger name.
</para>
<para>
A trigger definition can also specify a <literal>WHEN</literal>
condition so that, for example, a <literal>ddl_command_start</literal>
trigger can be fired only for particular commands which the user wishes
to intercept. A common use of such triggers is to restrict the range of
DDL operations which users may perform.
</para>
</sect1>
<sect1 id="event-trigger-matrix">
<title>Event Trigger Firing Matrix</title>
<para>
<xref linkend="event-trigger-by-command-tag"> lists all commands
for which event triggers are supported.
</para>
<table id="event-trigger-by-command-tag">
<title>Event Trigger Support by Command Tag</title>
<tgroup cols="2">
<thead>
<row>
<entry>command tag</entry>
<entry><literal>ddl_command_start</literal></entry>
</row>
</thead>
<tbody>
<row>
<entry align="left"><literal>ALTER AGGREGATE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER COLLATION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER CONVERSION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER DOMAIN</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER EXTENSION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER FOREIGN DATA WRAPPER</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER FOREIGN TABLE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER FUNCTION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER LANGUAGE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER OPERATOR</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER OPERATOR CLASS</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER OPERATOR FAMILY</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER SCHEMA</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER SEQUENCE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER SERVER</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER TABLE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER TEXT SEARCH CONFIGURATION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER TEXT SEARCH DICTIONARY</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER TEXT SEARCH PARSER</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER TEXT SEARCH TEMPLATE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER TRIGGER</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER TYPE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER USER MAPPING</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>ALTER VIEW</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE AGGREGATE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE CAST</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE COLLATION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE CONVERSION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE DOMAIN</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE EXTENSION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE FOREIGN DATA WRAPPER</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE FOREIGN TABLE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE FUNCTION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE INDEX</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE LANGUAGE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE OPERATOR</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE OPERATOR CLASS</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE OPERATOR FAMILY</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE RULE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE SCHEMA</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE SEQUENCE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE SERVER</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE TABLE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE TABLE AS</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE TEXT SEARCH CONFIGURATION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE TEXT SEARCH DICTIONARY</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE TEXT SEARCH PARSER</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE TEXT SEARCH TEMPLATE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE TRIGGER</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE TYPE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE USER MAPPING</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>CREATE VIEW</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP AGGREGATE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP CAST</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP COLLATION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP CONVERSION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP DOMAIN</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP EXTENSION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP FOREIGN DATA WRAPPER</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP FOREIGN TABLE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP FUNCTION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP INDEX</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP LANGUAGE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP OPERATOR</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP OPERATOR CLASS</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP OPERATOR FAMILY</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP RULE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP SCHEMA</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP SEQUENCE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP SERVER</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP TABLE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP TEXT SEARCH CONFIGURATION</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP TEXT SEARCH DICTIONARY</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP TEXT SEARCH PARSER</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP TEXT SEARCH TEMPLATE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP TRIGGER</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP TYPE</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP USER MAPPING</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>DROP VIEW</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
<row>
<entry align="left"><literal>SELECT INTO</literal></entry>
<entry align="center"><literal>X</literal></entry>
</row>
</tbody>
</tgroup>
</table>
</sect1>
</chapter>
|
