codekingpro/portable-devtools
114k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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>37.57. triggers</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="infoschema-triggered-update-columns.html" title="37.56. triggered_update_columns" /><link rel="next" href="infoschema-udt-privileges.html" title="37.58. udt_privileges" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">37.57. <code class="literal">triggers</code></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="infoschema-triggered-update-columns.html" title="37.56. triggered_update_columns">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="information-schema.html" title="Chapter 37. The Information Schema">Up</a></td><th width="60%" align="center">Chapter 37. The Information Schema</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="infoschema-udt-privileges.html" title="37.58. udt_privileges">Next</a></td></tr></table><hr /></div><div class="sect1" id="INFOSCHEMA-TRIGGERS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">37.57. <code class="literal">triggers</code> <a href="#INFOSCHEMA-TRIGGERS" class="id_link">#</a></h2></div></div></div><p>3 The view <code class="literal">triggers</code> contains all triggers defined4 in the current database on tables and views that the current user owns5 or has some privilege other than <code class="literal">SELECT</code> on.6 </p><div class="table" id="id-1.7.6.61.3"><p class="title"><strong>Table 37.55. <code class="structname">triggers</code> Columns</strong></p><div class="table-contents"><table class="table" summary="triggers Columns" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">7 Column Type8 </p>9 <p>10 Description11 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">12 <code class="structfield">trigger_catalog</code> <code class="type">sql_identifier</code>13 </p>14 <p>15 Name of the database that contains the trigger (always the current database)16 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">17 <code class="structfield">trigger_schema</code> <code class="type">sql_identifier</code>18 </p>19 <p>20 Name of the schema that contains the trigger21 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">22 <code class="structfield">trigger_name</code> <code class="type">sql_identifier</code>23 </p>24 <p>25 Name of the trigger26 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">27 <code class="structfield">event_manipulation</code> <code class="type">character_data</code>28 </p>29 <p>30 Event that fires the trigger (<code class="literal">INSERT</code>,31 <code class="literal">UPDATE</code>, or <code class="literal">DELETE</code>)32 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">33 <code class="structfield">event_object_catalog</code> <code class="type">sql_identifier</code>34 </p>35 <p>36 Name of the database that contains the table that the trigger37 is defined on (always the current database)38 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">39 <code class="structfield">event_object_schema</code> <code class="type">sql_identifier</code>40 </p>41 <p>42 Name of the schema that contains the table that the trigger is defined on43 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">44 <code class="structfield">event_object_table</code> <code class="type">sql_identifier</code>45 </p>46 <p>47 Name of the table that the trigger is defined on48 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">49 <code class="structfield">action_order</code> <code class="type">cardinal_number</code>50 </p>51 <p>52 Firing order among triggers on the same table having the same53 <code class="literal">event_manipulation</code>,54 <code class="literal">action_timing</code>, and55 <code class="literal">action_orientation</code>. In56 <span class="productname">PostgreSQL</span>, triggers are fired in name57 order, so this column reflects that.58 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">59 <code class="structfield">action_condition</code> <code class="type">character_data</code>60 </p>61 <p>62 <code class="literal">WHEN</code> condition of the trigger, null if none63 (also null if the table is not owned by a currently enabled64 role)65 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">66 <code class="structfield">action_statement</code> <code class="type">character_data</code>67 </p>68 <p>69 Statement that is executed by the trigger (currently always70 <code class="literal">EXECUTE FUNCTION71 <em class="replaceable"><code>function</code></em>(...)</code>)72 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">73 <code class="structfield">action_orientation</code> <code class="type">character_data</code>74 </p>75 <p>76 Identifies whether the trigger fires once for each processed77 row or once for each statement (<code class="literal">ROW</code> or78 <code class="literal">STATEMENT</code>)79 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">80 <code class="structfield">action_timing</code> <code class="type">character_data</code>81 </p>82 <p>83 Time at which the trigger fires (<code class="literal">BEFORE</code>,84 <code class="literal">AFTER</code>, or <code class="literal">INSTEAD OF</code>)85 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">86 <code class="structfield">action_reference_old_table</code> <code class="type">sql_identifier</code>87 </p>88 <p>89 Name of the <span class="quote">“<span class="quote">old</span>”</span> transition table, or null if none90 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">91 <code class="structfield">action_reference_new_table</code> <code class="type">sql_identifier</code>92 </p>93 <p>94 Name of the <span class="quote">“<span class="quote">new</span>”</span> transition table, or null if none95 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">96 <code class="structfield">action_reference_old_row</code> <code class="type">sql_identifier</code>97 </p>98 <p>99 Applies to a feature not available in <span class="productname">PostgreSQL</span>100 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">101 <code class="structfield">action_reference_new_row</code> <code class="type">sql_identifier</code>102 </p>103 <p>104 Applies to a feature not available in <span class="productname">PostgreSQL</span>105 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">106 <code class="structfield">created</code> <code class="type">time_stamp</code>107 </p>108 <p>109 Applies to a feature not available in <span class="productname">PostgreSQL</span>110 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>111 Triggers in <span class="productname">PostgreSQL</span> have two112 incompatibilities with the SQL standard that affect the113 representation in the information schema. First, trigger names are114 local to each table in <span class="productname">PostgreSQL</span>, rather115 than being independent schema objects. Therefore there can be duplicate116 trigger names defined in one schema, so long as they belong to117 different tables. (<code class="literal">trigger_catalog</code> and118 <code class="literal">trigger_schema</code> are really the values pertaining119 to the table that the trigger is defined on.) Second, triggers can120 be defined to fire on multiple events in121 <span class="productname">PostgreSQL</span> (e.g., <code class="literal">ON INSERT OR122 UPDATE</code>), whereas the SQL standard only allows one. If a123 trigger is defined to fire on multiple events, it is represented as124 multiple rows in the information schema, one for each type of125 event. As a consequence of these two issues, the primary key of126 the view <code class="literal">triggers</code> is really127 <code class="literal">(trigger_catalog, trigger_schema, event_object_table,128 trigger_name, event_manipulation)</code> instead of129 <code class="literal">(trigger_catalog, trigger_schema, trigger_name)</code>,130 which is what the SQL standard specifies. Nonetheless, if you131 define your triggers in a manner that conforms with the SQL132 standard (trigger names unique in the schema and only one event133 type per trigger), this will not affect you.134 </p><div class="note"><h3 class="title">Note</h3><p>135 Prior to <span class="productname">PostgreSQL</span> 9.1, this view's columns136 <code class="structfield">action_timing</code>,137 <code class="structfield">action_reference_old_table</code>,138 <code class="structfield">action_reference_new_table</code>,139 <code class="structfield">action_reference_old_row</code>, and140 <code class="structfield">action_reference_new_row</code>141 were named142 <code class="structfield">condition_timing</code>,143 <code class="structfield">condition_reference_old_table</code>,144 <code class="structfield">condition_reference_new_table</code>,145 <code class="structfield">condition_reference_old_row</code>, and146 <code class="structfield">condition_reference_new_row</code>147 respectively.148 That was how they were named in the SQL:1999 standard.149 The new naming conforms to SQL:2003 and later.150 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="infoschema-triggered-update-columns.html" title="37.56. triggered_update_columns">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="information-schema.html" title="Chapter 37. The Information Schema">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="infoschema-udt-privileges.html" title="37.58. udt_privileges">Next</a></td></tr><tr><td width="40%" align="left" valign="top">37.56. <code class="literal">triggered_update_columns</code> </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"> 37.58. <code class="literal">udt_privileges</code></td></tr></table></div></body></html>