granicus.if.org Git - postgresql/blob - doc/src/sgml/event-trigger.sgml

   1 <!-- doc/src/sgml/event-trigger.sgml -->
   2
   3  <chapter id="event-triggers">
   4   <title>Event Triggers</title>
   5
   6   <indexterm zone="event-triggers">
   7    <primary>event trigger</primary>
   8   </indexterm>
   9
  10   <para>
  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
  15    capturing DDL events.
  16   </para>
  17
  18   <para>
  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
  21    SQL.
  22   </para>
  23
  24   <sect1 id="event-trigger-definition">
  25    <title>Overview of Event Trigger Behavior</title>
  26
  27    <para>
  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
  30      supported events are <literal>ddl_command_start</>
  31      and <literal>ddl_command_end</>. Support for additional events may be
  32      added in future releases.
  33    </para>
  34
  35    <para>
  36      The <literal>ddl_command_start</> event occurs just before the
  37      execution of a <literal>CREATE</>, <literal>ALTER</>, or <literal>DROP</>
  38      commmand.  As an exception, however, this event does not occur for
  39      DDL commands targeting shared objects - databases, roles, and tablespaces
  40      - or for command targeting event triggers themselves.  The event trigger
  41      mechanism does not support these object types.
  42      <literal>ddl_command_start</> also occurs just before the execution of a
  43      <literal>SELECT INTO</literal> command, since this is equivalent to
  44      <literal>CREATE TABLE AS</literal>.
  45    </para>
  46
  47    <para>
  48     The <literal>ddl_command_end</> event occurs just before returning
  49     control from the execution of a <literal>CREATE</>, <literal>ALTER</>,
  50     or <literal>DROP</> commmand.  It shares the same exceptions as
  51     the <literal>ddl_command_start</> event.
  52    </para>
  53
  54    <para>
  55      For a complete list of commands supported by the event trigger mechanism,
  56      see <xref linkend="event-trigger-matrix">.
  57    </para>
  58
  59    <para>
  60      In order to create an event trigger, you must first create a function with
  61      the special return type <literal>event_trigger</literal>.  This function
  62      need not (and may not) return a value; the return type serves merely as
  63      a signal that the function is to be invoked as an event trigger.
  64    </para>
  65
  66    <para>
  67      If more than one event trigger is defined for a particular event, they will
  68      fire in alphabetical order by trigger name.
  69    </para>
  70
  71    <para>
  72      A trigger definition can also specify a <literal>WHEN</literal>
  73      condition so that, for example, a <literal>ddl_command_start</literal>
  74      trigger can be fired only for particular commands which the user wishes
  75      to intercept. A common use of such triggers is to restrict the range of
  76      DDL operations which users may perform.
  77    </para>
  78   </sect1>
  79
  80   <sect1 id="event-trigger-matrix">
  81    <title>Event Trigger Firing Matrix</title>
  82
  83    <para>
  84      <xref linkend="event-trigger-by-command-tag"> lists all commands
  85      for which event triggers are supported.
  86    </para>
  87
  88    <table id="event-trigger-by-command-tag">
  89      <title>Event Trigger Support by Command Tag</title>
  90      <tgroup cols="2">
  91       <thead>
  92        <row>
  93         <entry>command tag</entry>
  94         <entry><literal>ddl_command_start</literal></entry>
  95         <entry><literal>ddl_command_end</literal></entry>
  96        </row>
  97       </thead>
  98       <tbody>
  99        <row>
 100         <entry align="left"><literal>ALTER AGGREGATE</literal></entry>
 101         <entry align="center"><literal>X</literal></entry>
 102         <entry align="center"><literal>X</literal></entry>
 103        </row>
 104        <row>
 105         <entry align="left"><literal>ALTER COLLATION</literal></entry>
 106         <entry align="center"><literal>X</literal></entry>
 107         <entry align="center"><literal>X</literal></entry>
 108        </row>
 109        <row>
 110         <entry align="left"><literal>ALTER CONVERSION</literal></entry>
 111         <entry align="center"><literal>X</literal></entry>
 112         <entry align="center"><literal>X</literal></entry>
 113        </row>
 114        <row>
 115         <entry align="left"><literal>ALTER DOMAIN</literal></entry>
 116         <entry align="center"><literal>X</literal></entry>
 117         <entry align="center"><literal>X</literal></entry>
 118        </row>
 119        <row>
 120         <entry align="left"><literal>ALTER EXTENSION</literal></entry>
 121         <entry align="center"><literal>X</literal></entry>
 122         <entry align="center"><literal>X</literal></entry>
 123        </row>
 124        <row>
 125         <entry align="left"><literal>ALTER FOREIGN DATA WRAPPER</literal></entry>
 126         <entry align="center"><literal>X</literal></entry>
 127         <entry align="center"><literal>X</literal></entry>
 128        </row>
 129        <row>
 130         <entry align="left"><literal>ALTER FOREIGN TABLE</literal></entry>
 131         <entry align="center"><literal>X</literal></entry>
 132         <entry align="center"><literal>X</literal></entry>
 133        </row>
 134        <row>
 135         <entry align="left"><literal>ALTER FUNCTION</literal></entry>
 136         <entry align="center"><literal>X</literal></entry>
 137         <entry align="center"><literal>X</literal></entry>
 138        </row>
 139        <row>
 140         <entry align="left"><literal>ALTER LANGUAGE</literal></entry>
 141         <entry align="center"><literal>X</literal></entry>
 142         <entry align="center"><literal>X</literal></entry>
 143        </row>
 144        <row>
 145         <entry align="left"><literal>ALTER OPERATOR</literal></entry>
 146         <entry align="center"><literal>X</literal></entry>
 147         <entry align="center"><literal>X</literal></entry>
 148        </row>
 149        <row>
 150         <entry align="left"><literal>ALTER OPERATOR CLASS</literal></entry>
 151         <entry align="center"><literal>X</literal></entry>
 152         <entry align="center"><literal>X</literal></entry>
 153        </row>
 154        <row>
 155         <entry align="left"><literal>ALTER OPERATOR FAMILY</literal></entry>
 156         <entry align="center"><literal>X</literal></entry>
 157         <entry align="center"><literal>X</literal></entry>
 158        </row>
 159        <row>
 160         <entry align="left"><literal>ALTER SCHEMA</literal></entry>
 161         <entry align="center"><literal>X</literal></entry>
 162         <entry align="center"><literal>X</literal></entry>
 163        </row>
 164        <row>
 165         <entry align="left"><literal>ALTER SEQUENCE</literal></entry>
 166         <entry align="center"><literal>X</literal></entry>
 167         <entry align="center"><literal>X</literal></entry>
 168        </row>
 169        <row>
 170         <entry align="left"><literal>ALTER SERVER</literal></entry>
 171         <entry align="center"><literal>X</literal></entry>
 172         <entry align="center"><literal>X</literal></entry>
 173        </row>
 174        <row>
 175         <entry align="left"><literal>ALTER TABLE</literal></entry>
 176         <entry align="center"><literal>X</literal></entry>
 177         <entry align="center"><literal>X</literal></entry>
 178        </row>
 179        <row>
 180         <entry align="left"><literal>ALTER TEXT SEARCH CONFIGURATION</literal></entry>
 181         <entry align="center"><literal>X</literal></entry>
 182         <entry align="center"><literal>X</literal></entry>
 183        </row>
 184        <row>
 185         <entry align="left"><literal>ALTER TEXT SEARCH DICTIONARY</literal></entry>
 186         <entry align="center"><literal>X</literal></entry>
 187         <entry align="center"><literal>X</literal></entry>
 188        </row>
 189        <row>
 190         <entry align="left"><literal>ALTER TEXT SEARCH PARSER</literal></entry>
 191         <entry align="center"><literal>X</literal></entry>
 192         <entry align="center"><literal>X</literal></entry>
 193        </row>
 194        <row>
 195         <entry align="left"><literal>ALTER TEXT SEARCH TEMPLATE</literal></entry>
 196         <entry align="center"><literal>X</literal></entry>
 197         <entry align="center"><literal>X</literal></entry>
 198        </row>
 199        <row>
 200         <entry align="left"><literal>ALTER TRIGGER</literal></entry>
 201         <entry align="center"><literal>X</literal></entry>
 202         <entry align="center"><literal>X</literal></entry>
 203        </row>
 204        <row>
 205         <entry align="left"><literal>ALTER TYPE</literal></entry>
 206         <entry align="center"><literal>X</literal></entry>
 207         <entry align="center"><literal>X</literal></entry>
 208        </row>
 209        <row>
 210         <entry align="left"><literal>ALTER USER MAPPING</literal></entry>
 211         <entry align="center"><literal>X</literal></entry>
 212         <entry align="center"><literal>X</literal></entry>
 213        </row>
 214        <row>
 215         <entry align="left"><literal>ALTER VIEW</literal></entry>
 216         <entry align="center"><literal>X</literal></entry>
 217         <entry align="center"><literal>X</literal></entry>
 218        </row>
 219        <row>
 220         <entry align="left"><literal>CREATE AGGREGATE</literal></entry>
 221         <entry align="center"><literal>X</literal></entry>
 222         <entry align="center"><literal>X</literal></entry>
 223        </row>
 224        <row>
 225         <entry align="left"><literal>CREATE CAST</literal></entry>
 226         <entry align="center"><literal>X</literal></entry>
 227         <entry align="center"><literal>X</literal></entry>
 228        </row>
 229        <row>
 230         <entry align="left"><literal>CREATE COLLATION</literal></entry>
 231         <entry align="center"><literal>X</literal></entry>
 232         <entry align="center"><literal>X</literal></entry>
 233        </row>
 234        <row>
 235         <entry align="left"><literal>CREATE CONVERSION</literal></entry>
 236         <entry align="center"><literal>X</literal></entry>
 237         <entry align="center"><literal>X</literal></entry>
 238        </row>
 239        <row>
 240         <entry align="left"><literal>CREATE DOMAIN</literal></entry>
 241         <entry align="center"><literal>X</literal></entry>
 242         <entry align="center"><literal>X</literal></entry>
 243        </row>
 244        <row>
 245         <entry align="left"><literal>CREATE EXTENSION</literal></entry>
 246         <entry align="center"><literal>X</literal></entry>
 247         <entry align="center"><literal>X</literal></entry>
 248        </row>
 249        <row>
 250         <entry align="left"><literal>CREATE FOREIGN DATA WRAPPER</literal></entry>
 251         <entry align="center"><literal>X</literal></entry>
 252         <entry align="center"><literal>X</literal></entry>
 253        </row>
 254        <row>
 255         <entry align="left"><literal>CREATE FOREIGN TABLE</literal></entry>
 256         <entry align="center"><literal>X</literal></entry>
 257         <entry align="center"><literal>X</literal></entry>
 258        </row>
 259        <row>
 260         <entry align="left"><literal>CREATE FUNCTION</literal></entry>
 261         <entry align="center"><literal>X</literal></entry>
 262         <entry align="center"><literal>X</literal></entry>
 263        </row>
 264        <row>
 265         <entry align="left"><literal>CREATE INDEX</literal></entry>
 266         <entry align="center"><literal>X</literal></entry>
 267         <entry align="center"><literal>X</literal></entry>
 268        </row>
 269        <row>
 270         <entry align="left"><literal>CREATE LANGUAGE</literal></entry>
 271         <entry align="center"><literal>X</literal></entry>
 272         <entry align="center"><literal>X</literal></entry>
 273        </row>
 274        <row>
 275         <entry align="left"><literal>CREATE OPERATOR</literal></entry>
 276         <entry align="center"><literal>X</literal></entry>
 277         <entry align="center"><literal>X</literal></entry>
 278        </row>
 279        <row>
 280         <entry align="left"><literal>CREATE OPERATOR CLASS</literal></entry>
 281         <entry align="center"><literal>X</literal></entry>
 282         <entry align="center"><literal>X</literal></entry>
 283        </row>
 284        <row>
 285         <entry align="left"><literal>CREATE OPERATOR FAMILY</literal></entry>
 286         <entry align="center"><literal>X</literal></entry>
 287         <entry align="center"><literal>X</literal></entry>
 288        </row>
 289        <row>
 290         <entry align="left"><literal>CREATE RULE</literal></entry>
 291         <entry align="center"><literal>X</literal></entry>
 292         <entry align="center"><literal>X</literal></entry>
 293        </row>
 294        <row>
 295         <entry align="left"><literal>CREATE SCHEMA</literal></entry>
 296         <entry align="center"><literal>X</literal></entry>
 297         <entry align="center"><literal>X</literal></entry>
 298        </row>
 299        <row>
 300         <entry align="left"><literal>CREATE SEQUENCE</literal></entry>
 301         <entry align="center"><literal>X</literal></entry>
 302         <entry align="center"><literal>X</literal></entry>
 303        </row>
 304        <row>
 305         <entry align="left"><literal>CREATE SERVER</literal></entry>
 306         <entry align="center"><literal>X</literal></entry>
 307         <entry align="center"><literal>X</literal></entry>
 308        </row>
 309        <row>
 310         <entry align="left"><literal>CREATE TABLE</literal></entry>
 311         <entry align="center"><literal>X</literal></entry>
 312         <entry align="center"><literal>X</literal></entry>
 313        </row>
 314        <row>
 315         <entry align="left"><literal>CREATE TABLE AS</literal></entry>
 316         <entry align="center"><literal>X</literal></entry>
 317         <entry align="center"><literal>X</literal></entry>
 318        </row>
 319        <row>
 320         <entry align="left"><literal>CREATE TEXT SEARCH CONFIGURATION</literal></entry>
 321         <entry align="center"><literal>X</literal></entry>
 322         <entry align="center"><literal>X</literal></entry>
 323        </row>
 324        <row>
 325         <entry align="left"><literal>CREATE TEXT SEARCH DICTIONARY</literal></entry>
 326         <entry align="center"><literal>X</literal></entry>
 327         <entry align="center"><literal>X</literal></entry>
 328        </row>
 329        <row>
 330         <entry align="left"><literal>CREATE TEXT SEARCH PARSER</literal></entry>
 331         <entry align="center"><literal>X</literal></entry>
 332         <entry align="center"><literal>X</literal></entry>
 333        </row>
 334        <row>
 335         <entry align="left"><literal>CREATE TEXT SEARCH TEMPLATE</literal></entry>
 336         <entry align="center"><literal>X</literal></entry>
 337         <entry align="center"><literal>X</literal></entry>
 338        </row>
 339        <row>
 340         <entry align="left"><literal>CREATE TRIGGER</literal></entry>
 341         <entry align="center"><literal>X</literal></entry>
 342         <entry align="center"><literal>X</literal></entry>
 343        </row>
 344        <row>
 345         <entry align="left"><literal>CREATE TYPE</literal></entry>
 346         <entry align="center"><literal>X</literal></entry>
 347         <entry align="center"><literal>X</literal></entry>
 348        </row>
 349        <row>
 350         <entry align="left"><literal>CREATE USER MAPPING</literal></entry>
 351         <entry align="center"><literal>X</literal></entry>
 352         <entry align="center"><literal>X</literal></entry>
 353        </row>
 354        <row>
 355         <entry align="left"><literal>CREATE VIEW</literal></entry>
 356         <entry align="center"><literal>X</literal></entry>
 357         <entry align="center"><literal>X</literal></entry>
 358        </row>
 359        <row>
 360         <entry align="left"><literal>DROP AGGREGATE</literal></entry>
 361         <entry align="center"><literal>X</literal></entry>
 362         <entry align="center"><literal>X</literal></entry>
 363        </row>
 364        <row>
 365         <entry align="left"><literal>DROP CAST</literal></entry>
 366         <entry align="center"><literal>X</literal></entry>
 367         <entry align="center"><literal>X</literal></entry>
 368        </row>
 369        <row>
 370         <entry align="left"><literal>DROP COLLATION</literal></entry>
 371         <entry align="center"><literal>X</literal></entry>
 372         <entry align="center"><literal>X</literal></entry>
 373        </row>
 374        <row>
 375         <entry align="left"><literal>DROP CONVERSION</literal></entry>
 376         <entry align="center"><literal>X</literal></entry>
 377         <entry align="center"><literal>X</literal></entry>
 378        </row>
 379        <row>
 380         <entry align="left"><literal>DROP DOMAIN</literal></entry>
 381         <entry align="center"><literal>X</literal></entry>
 382         <entry align="center"><literal>X</literal></entry>
 383        </row>
 384        <row>
 385         <entry align="left"><literal>DROP EXTENSION</literal></entry>
 386         <entry align="center"><literal>X</literal></entry>
 387         <entry align="center"><literal>X</literal></entry>
 388        </row>
 389        <row>
 390         <entry align="left"><literal>DROP FOREIGN DATA WRAPPER</literal></entry>
 391         <entry align="center"><literal>X</literal></entry>
 392         <entry align="center"><literal>X</literal></entry>
 393        </row>
 394        <row>
 395         <entry align="left"><literal>DROP FOREIGN TABLE</literal></entry>
 396         <entry align="center"><literal>X</literal></entry>
 397         <entry align="center"><literal>X</literal></entry>
 398        </row>
 399        <row>
 400         <entry align="left"><literal>DROP FUNCTION</literal></entry>
 401         <entry align="center"><literal>X</literal></entry>
 402         <entry align="center"><literal>X</literal></entry>
 403        </row>
 404        <row>
 405         <entry align="left"><literal>DROP INDEX</literal></entry>
 406         <entry align="center"><literal>X</literal></entry>
 407         <entry align="center"><literal>X</literal></entry>
 408        </row>
 409        <row>
 410         <entry align="left"><literal>DROP LANGUAGE</literal></entry>
 411         <entry align="center"><literal>X</literal></entry>
 412         <entry align="center"><literal>X</literal></entry>
 413        </row>
 414        <row>
 415         <entry align="left"><literal>DROP OPERATOR</literal></entry>
 416         <entry align="center"><literal>X</literal></entry>
 417         <entry align="center"><literal>X</literal></entry>
 418        </row>
 419        <row>
 420         <entry align="left"><literal>DROP OPERATOR CLASS</literal></entry>
 421         <entry align="center"><literal>X</literal></entry>
 422         <entry align="center"><literal>X</literal></entry>
 423        </row>
 424        <row>
 425         <entry align="left"><literal>DROP OPERATOR FAMILY</literal></entry>
 426         <entry align="center"><literal>X</literal></entry>
 427         <entry align="center"><literal>X</literal></entry>
 428        </row>
 429        <row>
 430         <entry align="left"><literal>DROP RULE</literal></entry>
 431         <entry align="center"><literal>X</literal></entry>
 432         <entry align="center"><literal>X</literal></entry>
 433        </row>
 434        <row>
 435         <entry align="left"><literal>DROP SCHEMA</literal></entry>
 436         <entry align="center"><literal>X</literal></entry>
 437         <entry align="center"><literal>X</literal></entry>
 438        </row>
 439        <row>
 440         <entry align="left"><literal>DROP SEQUENCE</literal></entry>
 441         <entry align="center"><literal>X</literal></entry>
 442         <entry align="center"><literal>X</literal></entry>
 443        </row>
 444        <row>
 445         <entry align="left"><literal>DROP SERVER</literal></entry>
 446         <entry align="center"><literal>X</literal></entry>
 447         <entry align="center"><literal>X</literal></entry>
 448        </row>
 449        <row>
 450         <entry align="left"><literal>DROP TABLE</literal></entry>
 451         <entry align="center"><literal>X</literal></entry>
 452         <entry align="center"><literal>X</literal></entry>
 453        </row>
 454        <row>
 455         <entry align="left"><literal>DROP TEXT SEARCH CONFIGURATION</literal></entry>
 456         <entry align="center"><literal>X</literal></entry>
 457         <entry align="center"><literal>X</literal></entry>
 458        </row>
 459        <row>
 460         <entry align="left"><literal>DROP TEXT SEARCH DICTIONARY</literal></entry>
 461         <entry align="center"><literal>X</literal></entry>
 462         <entry align="center"><literal>X</literal></entry>
 463        </row>
 464        <row>
 465         <entry align="left"><literal>DROP TEXT SEARCH PARSER</literal></entry>
 466         <entry align="center"><literal>X</literal></entry>
 467         <entry align="center"><literal>X</literal></entry>
 468        </row>
 469        <row>
 470         <entry align="left"><literal>DROP TEXT SEARCH TEMPLATE</literal></entry>
 471         <entry align="center"><literal>X</literal></entry>
 472         <entry align="center"><literal>X</literal></entry>
 473        </row>
 474        <row>
 475         <entry align="left"><literal>DROP TRIGGER</literal></entry>
 476         <entry align="center"><literal>X</literal></entry>
 477         <entry align="center"><literal>X</literal></entry>
 478        </row>
 479        <row>
 480         <entry align="left"><literal>DROP TYPE</literal></entry>
 481         <entry align="center"><literal>X</literal></entry>
 482         <entry align="center"><literal>X</literal></entry>
 483        </row>
 484        <row>
 485         <entry align="left"><literal>DROP USER MAPPING</literal></entry>
 486         <entry align="center"><literal>X</literal></entry>
 487         <entry align="center"><literal>X</literal></entry>
 488        </row>
 489        <row>
 490         <entry align="left"><literal>DROP VIEW</literal></entry>
 491         <entry align="center"><literal>X</literal></entry>
 492         <entry align="center"><literal>X</literal></entry>
 493        </row>
 494        <row>
 495         <entry align="left"><literal>SELECT INTO</literal></entry>
 496         <entry align="center"><literal>X</literal></entry>
 497         <entry align="center"><literal>X</literal></entry>
 498        </row>
 499       </tbody>
 500      </tgroup>
 501    </table>
 502   </sect1>
 503
 504 </chapter>