~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security : contents of doc/src/sgml/html/infoschema-triggers.html at revision 14

~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security : (revision 14)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>triggers</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.8 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="The Information Schema"
HREF="information-schema.html"><LINK
REL="PREVIOUS"
TITLE="triggered_update_columns"
HREF="infoschema-triggered-update-columns.html"><LINK
REL="NEXT"
TITLE="usage_privileges"
HREF="infoschema-usage-privileges.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2013-02-04T21:38:53"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.8 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="triggered_update_columns"
HREF="infoschema-triggered-update-columns.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="information-schema.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 34. The Information Schema</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="usage_privileges"
HREF="infoschema-usage-privileges.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="INFOSCHEMA-TRIGGERS"
>34.52. <TT
CLASS="LITERAL"
>triggers</TT
></A
></H1
><P
>   The view <TT
CLASS="LITERAL"
>triggers</TT
> contains all triggers defined
   in the current database on tables and views that the current user owns
   or has some non-SELECT privilege on.
  </P
><DIV
CLASS="TABLE"
><A
NAME="AEN48981"
></A
><P
><B
>Table 34-50. <TT
CLASS="LITERAL"
>triggers</TT
> Columns</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><THEAD
><TR
><TH
>Name</TH
><TH
>Data Type</TH
><TH
>Description</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
>trigger_catalog</TT
></TD
><TD
><TT
CLASS="TYPE"
>sql_identifier</TT
></TD
><TD
>Name of the database that contains the trigger (always the current database)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>trigger_schema</TT
></TD
><TD
><TT
CLASS="TYPE"
>sql_identifier</TT
></TD
><TD
>Name of the schema that contains the trigger</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>trigger_name</TT
></TD
><TD
><TT
CLASS="TYPE"
>sql_identifier</TT
></TD
><TD
>Name of the trigger</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>event_manipulation</TT
></TD
><TD
><TT
CLASS="TYPE"
>character_data</TT
></TD
><TD
>       Event that fires the trigger (<TT
CLASS="LITERAL"
>INSERT</TT
>,
       <TT
CLASS="LITERAL"
>UPDATE</TT
>, or <TT
CLASS="LITERAL"
>DELETE</TT
>)
      </TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>event_object_catalog</TT
></TD
><TD
><TT
CLASS="TYPE"
>sql_identifier</TT
></TD
><TD
>       Name of the database that contains the table that the trigger
       is defined on (always the current database)
      </TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>event_object_schema</TT
></TD
><TD
><TT
CLASS="TYPE"
>sql_identifier</TT
></TD
><TD
>Name of the schema that contains the table that the trigger is defined on</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>event_object_table</TT
></TD
><TD
><TT
CLASS="TYPE"
>sql_identifier</TT
></TD
><TD
>Name of the table that the trigger is defined on</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>action_order</TT
></TD
><TD
><TT
CLASS="TYPE"
>cardinal_number</TT
></TD
><TD
>Not yet implemented</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>action_condition</TT
></TD
><TD
><TT
CLASS="TYPE"
>character_data</TT
></TD
><TD
>       <TT
CLASS="LITERAL"
>WHEN</TT
> condition of the trigger, null if none
       (also null if the table is not owned by a currently enabled
       role)
      </TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>action_statement</TT
></TD
><TD
><TT
CLASS="TYPE"
>character_data</TT
></TD
><TD
>       Statement that is executed by the trigger (currently always
       <TT
CLASS="LITERAL"
>EXECUTE PROCEDURE
       <TT
CLASS="REPLACEABLE"
><I
>function</I
></TT
>(...)</TT
>)
      </TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>action_orientation</TT
></TD
><TD
><TT
CLASS="TYPE"
>character_data</TT
></TD
><TD
>       Identifies whether the trigger fires once for each processed
       row or once for each statement (<TT
CLASS="LITERAL"
>ROW</TT
> or
       <TT
CLASS="LITERAL"
>STATEMENT</TT
>)
      </TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>action_timing</TT
></TD
><TD
><TT
CLASS="TYPE"
>character_data</TT
></TD
><TD
>       Time at which the trigger fires (<TT
CLASS="LITERAL"
>BEFORE</TT
>,
       <TT
CLASS="LITERAL"
>AFTER</TT
>, or <TT
CLASS="LITERAL"
>INSTEAD OF</TT
>)
      </TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>action_reference_old_table</TT
></TD
><TD
><TT
CLASS="TYPE"
>sql_identifier</TT
></TD
><TD
>Applies to a feature not available in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>action_reference_new_table</TT
></TD
><TD
><TT
CLASS="TYPE"
>sql_identifier</TT
></TD
><TD
>Applies to a feature not available in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>action_reference_old_row</TT
></TD
><TD
><TT
CLASS="TYPE"
>sql_identifier</TT
></TD
><TD
>Applies to a feature not available in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>action_reference_new_row</TT
></TD
><TD
><TT
CLASS="TYPE"
>sql_identifier</TT
></TD
><TD
>Applies to a feature not available in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>created</TT
></TD
><TD
><TT
CLASS="TYPE"
>time_stamp</TT
></TD
><TD
>Applies to a feature not available in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
></TD
></TR
></TBODY
></TABLE
></DIV
><P
>   Triggers in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> have two
   incompatibilities with the SQL standard that affect the
   representation in the information schema.  First, trigger names are
   local to each table in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>, rather
   than being independent schema objects.  Therefore there can be duplicate
   trigger names defined in one schema, so long as they belong to
   different tables.  (<TT
CLASS="LITERAL"
>trigger_catalog</TT
> and
   <TT
CLASS="LITERAL"
>trigger_schema</TT
> are really the values pertaining
   to the table that the trigger is defined on.)  Second, triggers can
   be defined to fire on multiple events in
   <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> (e.g., <TT
CLASS="LITERAL"
>ON INSERT OR
   UPDATE</TT
>), whereas the SQL standard only allows one.  If a
   trigger is defined to fire on multiple events, it is represented as
   multiple rows in the information schema, one for each type of
   event.  As a consequence of these two issues, the primary key of
   the view <TT
CLASS="LITERAL"
>triggers</TT
> is really
   <TT
CLASS="LITERAL"
>(trigger_catalog, trigger_schema, event_object_table,
   trigger_name, event_manipulation)</TT
> instead of
   <TT
CLASS="LITERAL"
>(trigger_catalog, trigger_schema, trigger_name)</TT
>,
   which is what the SQL standard specifies.  Nonetheless, if you
   define your triggers in a manner that conforms with the SQL
   standard (trigger names unique in the schema and only one event
   type per trigger), this will not affect you.
  </P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>    Prior to <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> 9.1, this view's columns
    <TT
CLASS="STRUCTFIELD"
>action_timing</TT
>,
    <TT
CLASS="STRUCTFIELD"
>action_reference_old_table</TT
>,
    <TT
CLASS="STRUCTFIELD"
>action_reference_new_table</TT
>,
    <TT
CLASS="STRUCTFIELD"
>action_reference_old_row</TT
>, and
    <TT
CLASS="STRUCTFIELD"
>action_reference_new_row</TT
>
    were named
    <TT
CLASS="STRUCTFIELD"
>condition_timing</TT
>,
    <TT
CLASS="STRUCTFIELD"
>condition_reference_old_table</TT
>,
    <TT
CLASS="STRUCTFIELD"
>condition_reference_new_table</TT
>,
    <TT
CLASS="STRUCTFIELD"
>condition_reference_old_row</TT
>, and
    <TT
CLASS="STRUCTFIELD"
>condition_reference_new_row</TT
>
    respectively.
    That was how they were named in the SQL:1999 standard.
    The new naming conforms to SQL:2003 and later.
   </P
></BLOCKQUOTE
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="infoschema-triggered-update-columns.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="infoschema-usage-privileges.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><TT
CLASS="LITERAL"
>triggered_update_columns</TT
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="information-schema.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><TT
CLASS="LITERAL"
>usage_privileges</TT
></TD
></TR
></TABLE
></DIV
></BODY
></HTML
>