1 <!-- doc/src/sgml/event-trigger.sgml -->
3 <chapter id="event-triggers">
4 <title>Event Triggers</title>
6 <indexterm zone="event-triggers">
7 <primary>event trigger</primary>
11 To supplement the trigger mechanism discussed in <xref linkend="triggers">,
12 <productname>PostgreSQL</> also provides event triggers. Unlike regular
13 triggers, which are attached to a single table and capture only DML events,
14 event triggers are global to a particular database and are capable of
19 Like regular triggers, event triggers can be written in any procedural
20 language that includes event trigger support, or in C, but not in plain
24 <sect1 id="event-trigger-definition">
25 <title>Overview of Event Trigger Behavior</title>
28 An event trigger fires whenever the event with which it is associated
29 occurs in the database in which it is defined. Currently, the only
31 <literal>ddl_command_start</>,
32 <literal>ddl_command_end</>
33 and <literal>sql_drop</>.
34 Support for additional events may be added in future releases.
38 The <literal>ddl_command_start</> event occurs just before the
39 execution of a <literal>CREATE</>, <literal>ALTER</>, or <literal>DROP</>
40 command. As an exception, however, this event does not occur for
41 DDL commands targeting shared objects — databases, roles, and tablespaces
42 — or for command targeting event triggers themselves. The event trigger
43 mechanism does not support these object types.
44 <literal>ddl_command_start</> also occurs just before the execution of a
45 <literal>SELECT INTO</literal> command, since this is equivalent to
46 <literal>CREATE TABLE AS</literal>. The <literal>ddl_command_end</>
47 event occurs just after the execution of this same set of commands.
51 The <literal>sql_drop</> event occurs just before the
52 <literal>ddl_command_end</> event trigger for any operation that drops
53 database objects. To list the objects that have been dropped, use the set
54 returning function <literal>pg_event_trigger_dropped_objects()</> from your
55 <literal>sql_drop</> event trigger code (see
56 <xref linkend="functions-event-triggers">). Note that
57 the trigger is executed after the objects have been deleted from the
58 system catalogs, so it's not possible to look them up anymore.
62 Event triggers (like other functions) cannot be executed in an aborted
63 transaction. Thus, if a DDL command fails with an error, any associated
64 <literal>ddl_command_end</> triggers will not be executed. Conversely,
65 if a <literal>ddl_command_start</> trigger fails with an error, no
66 further event triggers will fire, and no attempt will be made to execute
67 the command itself. Similarly, if a <literal>ddl_command_end</> trigger
68 fails with an error, the effects of the DDL statement will be rolled
69 back, just as they would be in any other case where the containing
74 For a complete list of commands supported by the event trigger mechanism,
75 see <xref linkend="event-trigger-matrix">.
79 In order to create an event trigger, you must first create a function with
80 the special return type <literal>event_trigger</literal>. This function
81 need not (and may not) return a value; the return type serves merely as
82 a signal that the function is to be invoked as an event trigger.
86 If more than one event trigger is defined for a particular event, they will
87 fire in alphabetical order by trigger name.
91 A trigger definition can also specify a <literal>WHEN</literal>
92 condition so that, for example, a <literal>ddl_command_start</literal>
93 trigger can be fired only for particular commands which the user wishes
94 to intercept. A common use of such triggers is to restrict the range of
95 DDL operations which users may perform.
99 <sect1 id="event-trigger-matrix">
100 <title>Event Trigger Firing Matrix</title>
103 <xref linkend="event-trigger-by-command-tag"> lists all commands
104 for which event triggers are supported.
107 <table id="event-trigger-by-command-tag">
108 <title>Event Trigger Support by Command Tag</title>
112 <entry>command tag</entry>
113 <entry><literal>ddl_command_start</literal></entry>
114 <entry><literal>ddl_command_end</literal></entry>
115 <entry><literal>sql_drop</literal></entry>
120 <entry align="left"><literal>ALTER AGGREGATE</literal></entry>
121 <entry align="center"><literal>X</literal></entry>
122 <entry align="center"><literal>X</literal></entry>
123 <entry align="center"><literal>-</literal></entry>
126 <entry align="left"><literal>ALTER COLLATION</literal></entry>
127 <entry align="center"><literal>X</literal></entry>
128 <entry align="center"><literal>X</literal></entry>
129 <entry align="center"><literal>-</literal></entry>
132 <entry align="left"><literal>ALTER CONVERSION</literal></entry>
133 <entry align="center"><literal>X</literal></entry>
134 <entry align="center"><literal>X</literal></entry>
135 <entry align="center"><literal>-</literal></entry>
138 <entry align="left"><literal>ALTER DOMAIN</literal></entry>
139 <entry align="center"><literal>X</literal></entry>
140 <entry align="center"><literal>X</literal></entry>
141 <entry align="center"><literal>-</literal></entry>
144 <entry align="left"><literal>ALTER EXTENSION</literal></entry>
145 <entry align="center"><literal>X</literal></entry>
146 <entry align="center"><literal>X</literal></entry>
147 <entry align="center"><literal>-</literal></entry>
150 <entry align="left"><literal>ALTER FOREIGN DATA WRAPPER</literal></entry>
151 <entry align="center"><literal>X</literal></entry>
152 <entry align="center"><literal>X</literal></entry>
153 <entry align="center"><literal>-</literal></entry>
156 <entry align="left"><literal>ALTER FOREIGN TABLE</literal></entry>
157 <entry align="center"><literal>X</literal></entry>
158 <entry align="center"><literal>X</literal></entry>
159 <entry align="center"><literal>X</literal></entry>
162 <entry align="left"><literal>ALTER FUNCTION</literal></entry>
163 <entry align="center"><literal>X</literal></entry>
164 <entry align="center"><literal>X</literal></entry>
165 <entry align="center"><literal>-</literal></entry>
168 <entry align="left"><literal>ALTER LANGUAGE</literal></entry>
169 <entry align="center"><literal>X</literal></entry>
170 <entry align="center"><literal>X</literal></entry>
171 <entry align="center"><literal>-</literal></entry>
174 <entry align="left"><literal>ALTER OPERATOR</literal></entry>
175 <entry align="center"><literal>X</literal></entry>
176 <entry align="center"><literal>X</literal></entry>
177 <entry align="center"><literal>-</literal></entry>
180 <entry align="left"><literal>ALTER OPERATOR CLASS</literal></entry>
181 <entry align="center"><literal>X</literal></entry>
182 <entry align="center"><literal>X</literal></entry>
183 <entry align="center"><literal>-</literal></entry>
186 <entry align="left"><literal>ALTER OPERATOR FAMILY</literal></entry>
187 <entry align="center"><literal>X</literal></entry>
188 <entry align="center"><literal>X</literal></entry>
189 <entry align="center"><literal>-</literal></entry>
192 <entry align="left"><literal>ALTER SCHEMA</literal></entry>
193 <entry align="center"><literal>X</literal></entry>
194 <entry align="center"><literal>X</literal></entry>
195 <entry align="center"><literal>-</literal></entry>
198 <entry align="left"><literal>ALTER SEQUENCE</literal></entry>
199 <entry align="center"><literal>X</literal></entry>
200 <entry align="center"><literal>X</literal></entry>
201 <entry align="center"><literal>-</literal></entry>
204 <entry align="left"><literal>ALTER SERVER</literal></entry>
205 <entry align="center"><literal>X</literal></entry>
206 <entry align="center"><literal>X</literal></entry>
207 <entry align="center"><literal>-</literal></entry>
210 <entry align="left"><literal>ALTER TABLE</literal></entry>
211 <entry align="center"><literal>X</literal></entry>
212 <entry align="center"><literal>X</literal></entry>
213 <entry align="center"><literal>X</literal></entry>
216 <entry align="left"><literal>ALTER TEXT SEARCH CONFIGURATION</literal></entry>
217 <entry align="center"><literal>X</literal></entry>
218 <entry align="center"><literal>X</literal></entry>
219 <entry align="center"><literal>-</literal></entry>
222 <entry align="left"><literal>ALTER TEXT SEARCH DICTIONARY</literal></entry>
223 <entry align="center"><literal>X</literal></entry>
224 <entry align="center"><literal>X</literal></entry>
225 <entry align="center"><literal>-</literal></entry>
228 <entry align="left"><literal>ALTER TEXT SEARCH PARSER</literal></entry>
229 <entry align="center"><literal>X</literal></entry>
230 <entry align="center"><literal>X</literal></entry>
231 <entry align="center"><literal>-</literal></entry>
234 <entry align="left"><literal>ALTER TEXT SEARCH TEMPLATE</literal></entry>
235 <entry align="center"><literal>X</literal></entry>
236 <entry align="center"><literal>X</literal></entry>
237 <entry align="center"><literal>-</literal></entry>
240 <entry align="left"><literal>ALTER TRIGGER</literal></entry>
241 <entry align="center"><literal>X</literal></entry>
242 <entry align="center"><literal>X</literal></entry>
243 <entry align="center"><literal>-</literal></entry>
246 <entry align="left"><literal>ALTER TYPE</literal></entry>
247 <entry align="center"><literal>X</literal></entry>
248 <entry align="center"><literal>X</literal></entry>
249 <entry align="center"><literal>-</literal></entry>
252 <entry align="left"><literal>ALTER USER MAPPING</literal></entry>
253 <entry align="center"><literal>X</literal></entry>
254 <entry align="center"><literal>X</literal></entry>
255 <entry align="center"><literal>-</literal></entry>
258 <entry align="left"><literal>ALTER VIEW</literal></entry>
259 <entry align="center"><literal>X</literal></entry>
260 <entry align="center"><literal>X</literal></entry>
261 <entry align="center"><literal>-</literal></entry>
264 <entry align="left"><literal>CREATE AGGREGATE</literal></entry>
265 <entry align="center"><literal>X</literal></entry>
266 <entry align="center"><literal>X</literal></entry>
267 <entry align="center"><literal>-</literal></entry>
270 <entry align="left"><literal>CREATE CAST</literal></entry>
271 <entry align="center"><literal>X</literal></entry>
272 <entry align="center"><literal>X</literal></entry>
273 <entry align="center"><literal>-</literal></entry>
276 <entry align="left"><literal>CREATE COLLATION</literal></entry>
277 <entry align="center"><literal>X</literal></entry>
278 <entry align="center"><literal>X</literal></entry>
279 <entry align="center"><literal>-</literal></entry>
282 <entry align="left"><literal>CREATE CONVERSION</literal></entry>
283 <entry align="center"><literal>X</literal></entry>
284 <entry align="center"><literal>X</literal></entry>
285 <entry align="center"><literal>-</literal></entry>
288 <entry align="left"><literal>CREATE DOMAIN</literal></entry>
289 <entry align="center"><literal>X</literal></entry>
290 <entry align="center"><literal>X</literal></entry>
291 <entry align="center"><literal>-</literal></entry>
294 <entry align="left"><literal>CREATE EXTENSION</literal></entry>
295 <entry align="center"><literal>X</literal></entry>
296 <entry align="center"><literal>X</literal></entry>
297 <entry align="center"><literal>-</literal></entry>
300 <entry align="left"><literal>CREATE FOREIGN DATA WRAPPER</literal></entry>
301 <entry align="center"><literal>X</literal></entry>
302 <entry align="center"><literal>X</literal></entry>
303 <entry align="center"><literal>-</literal></entry>
306 <entry align="left"><literal>CREATE FOREIGN TABLE</literal></entry>
307 <entry align="center"><literal>X</literal></entry>
308 <entry align="center"><literal>X</literal></entry>
309 <entry align="center"><literal>-</literal></entry>
312 <entry align="left"><literal>CREATE FUNCTION</literal></entry>
313 <entry align="center"><literal>X</literal></entry>
314 <entry align="center"><literal>X</literal></entry>
315 <entry align="center"><literal>-</literal></entry>
318 <entry align="left"><literal>CREATE INDEX</literal></entry>
319 <entry align="center"><literal>X</literal></entry>
320 <entry align="center"><literal>X</literal></entry>
321 <entry align="center"><literal>-</literal></entry>
324 <entry align="left"><literal>CREATE LANGUAGE</literal></entry>
325 <entry align="center"><literal>X</literal></entry>
326 <entry align="center"><literal>X</literal></entry>
327 <entry align="center"><literal>-</literal></entry>
330 <entry align="left"><literal>CREATE OPERATOR</literal></entry>
331 <entry align="center"><literal>X</literal></entry>
332 <entry align="center"><literal>X</literal></entry>
333 <entry align="center"><literal>-</literal></entry>
336 <entry align="left"><literal>CREATE OPERATOR CLASS</literal></entry>
337 <entry align="center"><literal>X</literal></entry>
338 <entry align="center"><literal>X</literal></entry>
339 <entry align="center"><literal>-</literal></entry>
342 <entry align="left"><literal>CREATE OPERATOR FAMILY</literal></entry>
343 <entry align="center"><literal>X</literal></entry>
344 <entry align="center"><literal>X</literal></entry>
345 <entry align="center"><literal>-</literal></entry>
348 <entry align="left"><literal>CREATE RULE</literal></entry>
349 <entry align="center"><literal>X</literal></entry>
350 <entry align="center"><literal>X</literal></entry>
351 <entry align="center"><literal>-</literal></entry>
354 <entry align="left"><literal>CREATE SCHEMA</literal></entry>
355 <entry align="center"><literal>X</literal></entry>
356 <entry align="center"><literal>X</literal></entry>
357 <entry align="center"><literal>-</literal></entry>
360 <entry align="left"><literal>CREATE SEQUENCE</literal></entry>
361 <entry align="center"><literal>X</literal></entry>
362 <entry align="center"><literal>X</literal></entry>
363 <entry align="center"><literal>-</literal></entry>
366 <entry align="left"><literal>CREATE SERVER</literal></entry>
367 <entry align="center"><literal>X</literal></entry>
368 <entry align="center"><literal>X</literal></entry>
369 <entry align="center"><literal>-</literal></entry>
372 <entry align="left"><literal>CREATE TABLE</literal></entry>
373 <entry align="center"><literal>X</literal></entry>
374 <entry align="center"><literal>X</literal></entry>
375 <entry align="center"><literal>-</literal></entry>
378 <entry align="left"><literal>CREATE TABLE AS</literal></entry>
379 <entry align="center"><literal>X</literal></entry>
380 <entry align="center"><literal>X</literal></entry>
381 <entry align="center"><literal>-</literal></entry>
384 <entry align="left"><literal>CREATE TEXT SEARCH CONFIGURATION</literal></entry>
385 <entry align="center"><literal>X</literal></entry>
386 <entry align="center"><literal>X</literal></entry>
387 <entry align="center"><literal>-</literal></entry>
390 <entry align="left"><literal>CREATE TEXT SEARCH DICTIONARY</literal></entry>
391 <entry align="center"><literal>X</literal></entry>
392 <entry align="center"><literal>X</literal></entry>
393 <entry align="center"><literal>-</literal></entry>
396 <entry align="left"><literal>CREATE TEXT SEARCH PARSER</literal></entry>
397 <entry align="center"><literal>X</literal></entry>
398 <entry align="center"><literal>X</literal></entry>
399 <entry align="center"><literal>-</literal></entry>
402 <entry align="left"><literal>CREATE TEXT SEARCH TEMPLATE</literal></entry>
403 <entry align="center"><literal>X</literal></entry>
404 <entry align="center"><literal>X</literal></entry>
405 <entry align="center"><literal>-</literal></entry>
408 <entry align="left"><literal>CREATE TRIGGER</literal></entry>
409 <entry align="center"><literal>X</literal></entry>
410 <entry align="center"><literal>X</literal></entry>
411 <entry align="center"><literal>-</literal></entry>
414 <entry align="left"><literal>CREATE TYPE</literal></entry>
415 <entry align="center"><literal>X</literal></entry>
416 <entry align="center"><literal>X</literal></entry>
417 <entry align="center"><literal>-</literal></entry>
420 <entry align="left"><literal>CREATE USER MAPPING</literal></entry>
421 <entry align="center"><literal>X</literal></entry>
422 <entry align="center"><literal>X</literal></entry>
423 <entry align="center"><literal>-</literal></entry>
426 <entry align="left"><literal>CREATE VIEW</literal></entry>
427 <entry align="center"><literal>X</literal></entry>
428 <entry align="center"><literal>X</literal></entry>
429 <entry align="center"><literal>-</literal></entry>
432 <entry align="left"><literal>DROP AGGREGATE</literal></entry>
433 <entry align="center"><literal>X</literal></entry>
434 <entry align="center"><literal>X</literal></entry>
435 <entry align="center"><literal>X</literal></entry>
438 <entry align="left"><literal>DROP CAST</literal></entry>
439 <entry align="center"><literal>X</literal></entry>
440 <entry align="center"><literal>X</literal></entry>
441 <entry align="center"><literal>X</literal></entry>
444 <entry align="left"><literal>DROP COLLATION</literal></entry>
445 <entry align="center"><literal>X</literal></entry>
446 <entry align="center"><literal>X</literal></entry>
447 <entry align="center"><literal>X</literal></entry>
450 <entry align="left"><literal>DROP CONVERSION</literal></entry>
451 <entry align="center"><literal>X</literal></entry>
452 <entry align="center"><literal>X</literal></entry>
453 <entry align="center"><literal>X</literal></entry>
456 <entry align="left"><literal>DROP DOMAIN</literal></entry>
457 <entry align="center"><literal>X</literal></entry>
458 <entry align="center"><literal>X</literal></entry>
459 <entry align="center"><literal>X</literal></entry>
462 <entry align="left"><literal>DROP EXTENSION</literal></entry>
463 <entry align="center"><literal>X</literal></entry>
464 <entry align="center"><literal>X</literal></entry>
465 <entry align="center"><literal>X</literal></entry>
468 <entry align="left"><literal>DROP FOREIGN DATA WRAPPER</literal></entry>
469 <entry align="center"><literal>X</literal></entry>
470 <entry align="center"><literal>X</literal></entry>
471 <entry align="center"><literal>X</literal></entry>
474 <entry align="left"><literal>DROP FOREIGN TABLE</literal></entry>
475 <entry align="center"><literal>X</literal></entry>
476 <entry align="center"><literal>X</literal></entry>
477 <entry align="center"><literal>X</literal></entry>
480 <entry align="left"><literal>DROP FUNCTION</literal></entry>
481 <entry align="center"><literal>X</literal></entry>
482 <entry align="center"><literal>X</literal></entry>
483 <entry align="center"><literal>X</literal></entry>
486 <entry align="left"><literal>DROP INDEX</literal></entry>
487 <entry align="center"><literal>X</literal></entry>
488 <entry align="center"><literal>X</literal></entry>
489 <entry align="center"><literal>X</literal></entry>
492 <entry align="left"><literal>DROP LANGUAGE</literal></entry>
493 <entry align="center"><literal>X</literal></entry>
494 <entry align="center"><literal>X</literal></entry>
495 <entry align="center"><literal>X</literal></entry>
498 <entry align="left"><literal>DROP OPERATOR</literal></entry>
499 <entry align="center"><literal>X</literal></entry>
500 <entry align="center"><literal>X</literal></entry>
501 <entry align="center"><literal>X</literal></entry>
504 <entry align="left"><literal>DROP OPERATOR CLASS</literal></entry>
505 <entry align="center"><literal>X</literal></entry>
506 <entry align="center"><literal>X</literal></entry>
507 <entry align="center"><literal>X</literal></entry>
510 <entry align="left"><literal>DROP OPERATOR FAMILY</literal></entry>
511 <entry align="center"><literal>X</literal></entry>
512 <entry align="center"><literal>X</literal></entry>
513 <entry align="center"><literal>X</literal></entry>
516 <entry align="left"><literal>DROP OWNED</literal></entry>
517 <entry align="center"><literal>X</literal></entry>
518 <entry align="center"><literal>X</literal></entry>
519 <entry align="center"><literal>X</literal></entry>
522 <entry align="left"><literal>DROP RULE</literal></entry>
523 <entry align="center"><literal>X</literal></entry>
524 <entry align="center"><literal>X</literal></entry>
525 <entry align="center"><literal>X</literal></entry>
528 <entry align="left"><literal>DROP SCHEMA</literal></entry>
529 <entry align="center"><literal>X</literal></entry>
530 <entry align="center"><literal>X</literal></entry>
531 <entry align="center"><literal>X</literal></entry>
534 <entry align="left"><literal>DROP SEQUENCE</literal></entry>
535 <entry align="center"><literal>X</literal></entry>
536 <entry align="center"><literal>X</literal></entry>
537 <entry align="center"><literal>X</literal></entry>
540 <entry align="left"><literal>DROP SERVER</literal></entry>
541 <entry align="center"><literal>X</literal></entry>
542 <entry align="center"><literal>X</literal></entry>
543 <entry align="center"><literal>X</literal></entry>
546 <entry align="left"><literal>DROP TABLE</literal></entry>
547 <entry align="center"><literal>X</literal></entry>
548 <entry align="center"><literal>X</literal></entry>
549 <entry align="center"><literal>X</literal></entry>
552 <entry align="left"><literal>DROP TEXT SEARCH CONFIGURATION</literal></entry>
553 <entry align="center"><literal>X</literal></entry>
554 <entry align="center"><literal>X</literal></entry>
555 <entry align="center"><literal>X</literal></entry>
558 <entry align="left"><literal>DROP TEXT SEARCH DICTIONARY</literal></entry>
559 <entry align="center"><literal>X</literal></entry>
560 <entry align="center"><literal>X</literal></entry>
561 <entry align="center"><literal>X</literal></entry>
564 <entry align="left"><literal>DROP TEXT SEARCH PARSER</literal></entry>
565 <entry align="center"><literal>X</literal></entry>
566 <entry align="center"><literal>X</literal></entry>
567 <entry align="center"><literal>X</literal></entry>
570 <entry align="left"><literal>DROP TEXT SEARCH TEMPLATE</literal></entry>
571 <entry align="center"><literal>X</literal></entry>
572 <entry align="center"><literal>X</literal></entry>
573 <entry align="center"><literal>X</literal></entry>
576 <entry align="left"><literal>DROP TRIGGER</literal></entry>
577 <entry align="center"><literal>X</literal></entry>
578 <entry align="center"><literal>X</literal></entry>
579 <entry align="center"><literal>X</literal></entry>
582 <entry align="left"><literal>DROP TYPE</literal></entry>
583 <entry align="center"><literal>X</literal></entry>
584 <entry align="center"><literal>X</literal></entry>
585 <entry align="center"><literal>X</literal></entry>
588 <entry align="left"><literal>DROP USER MAPPING</literal></entry>
589 <entry align="center"><literal>X</literal></entry>
590 <entry align="center"><literal>X</literal></entry>
591 <entry align="center"><literal>X</literal></entry>
594 <entry align="left"><literal>DROP VIEW</literal></entry>
595 <entry align="center"><literal>X</literal></entry>
596 <entry align="center"><literal>X</literal></entry>
597 <entry align="center"><literal>X</literal></entry>
600 <entry align="left"><literal>SELECT INTO</literal></entry>
601 <entry align="center"><literal>X</literal></entry>
602 <entry align="center"><literal>X</literal></entry>
603 <entry align="center"><literal>-</literal></entry>