File: sql-createtrigger.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (410 lines) | stat: -rw-r--r-- 6,131 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>   CREATE TRIGGER
  </TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="   CREATE TABLE AS
  "
HREF="sql-createtableas.html"><LINK
REL="NEXT"
TITLE="   CREATE TYPE
  "
HREF="sql-createtype.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="sql-createtableas.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="sql-createtype.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
>CREATE TRIGGER</H1
><DIV
CLASS="REFNAMEDIV"
><H2
>Name</H2
>   CREATE TRIGGER
   &#8212;    Creates a new trigger
  </DIV
><DIV
CLASS="REFSYNOPSISDIV"
><PRE
CLASS="SYNOPSIS"
>CREATE TRIGGER <TT
CLASS="REPLACEABLE"
><I
>name</I
></TT
> { BEFORE | AFTER }
    { <TT
CLASS="REPLACEABLE"
><I
>event</I
></TT
> [OR ...] }
    ON <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
> FOR EACH { ROW | STATEMENT }
    EXECUTE PROCEDURE <TT
CLASS="REPLACEABLE"
><I
>funcname</I
></TT
> ( <TT
CLASS="REPLACEABLE"
><I
>arguments</I
></TT
> )
  </PRE
><DIV
CLASS="REFSECT2"
><H3
>    Inputs
   </H3
><P
>   </P
><P
></P
><DL
><DT
><SPAN
CLASS="RETURNVALUE"
><TT
CLASS="REPLACEABLE"
><I
>name</I
></TT
></SPAN
></DT
><DD
><P
>	   The name of an existing trigger.
	  </P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
><TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
></SPAN
></DT
><DD
><P
>	   The name of a table.
	  </P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
><TT
CLASS="REPLACEABLE"
><I
>event</I
></TT
></SPAN
></DT
><DD
><P
>	   One of INSERT, DELETE or UPDATE.
	  </P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
><TT
CLASS="REPLACEABLE"
><I
>funcname</I
></TT
></SPAN
></DT
><DD
><P
>	   A user-supplied function.
	  </P
></DD
></DL
></DIV
><DIV
CLASS="REFSECT2"
><H3
>    Outputs
   </H3
><P
>       <P
></P
></P><DL
><DT
><SPAN
CLASS="RETURNVALUE"
>CREATE</SPAN
></DT
><DD
><P
>	   This message is returned if the trigger is successfully created.

   </P
></DD
></DL
><P>
  </P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Description
  </H2
><P
>   <B
CLASS="COMMAND"
>CREATE TRIGGER</B
> will enter a new trigger into the current
   data base.  The trigger will be associated with the relation
   <TT
CLASS="REPLACEABLE"
><I
>relname</I
></TT
> and will execute
   the specified function <TT
CLASS="REPLACEABLE"
><I
>funcname</I
></TT
>.
  </P
><P
>   The trigger can be specified to  fire  either  before  the
   operation is attempted on a tuple (before constraints
   are checked and the INSERT, UPDATE or DELETE is attempted)  or
   after  the  operation  has been attempted (e.g. after constraints
   are checked and the INSERT, UPDATE or DELETE has completed).  If the
   trigger fires before the event, the trigger may
   skip the operation for the current tuple, or change the tuple
   being  inserted  (for  INSERT and UPDATE operations only).  If
   the trigger fires after the event,  all  changes,  including  the
   last insertion, update, or deletion, are "visible" to the trigger.
  </P
><P
>   Refer to the chapters on SPI and Triggers in the
<I
CLASS="CITETITLE"
>PostgreSQL Programmer's Guide</I
>  for  more
   information.
  </P
><DIV
CLASS="REFSECT2"
><H3
>    Notes
   </H3
><P
>    <B
CLASS="COMMAND"
>CREATE TRIGGER</B
> is a <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
 language extension.
   </P
><P
>   Only the relation owner may create a trigger on this relation.
  </P
><P
>   As of the current release (v6.4), STATEMENT triggers are not implemented.
  </P
><P
>    Refer to <B
CLASS="COMMAND"
>DROP TRIGGER</B
> for information on how to 
    remove triggers.
   </P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Usage
  </H2
><P
>  Check if the specified distributor code exists in the distributors
  table before appending or updating a row in the table films:
  </P
><PRE
CLASS="PROGRAMLISTING"
>CREATE TRIGGER if_dist_exists
    BEFORE INSERT OR UPDATE ON films FOR EACH ROW
    EXECUTE PROCEDURE check_primary_key ('did', 'distributors', 'did');
  </PRE
><P
>    Before cancelling a distributor or updating its code, remove every
    reference to the table films:
  </P
><PRE
CLASS="PROGRAMLISTING"
>CREATE TRIGGER if_film_exists 
    BEFORE DELETE OR UPDATE ON distributors FOR EACH ROW
    EXECUTE PROCEDURE check_foreign_key (1, 'CASCADE', 'did', 'films', 'did');
  </PRE
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Compatibility
  </H2
><P
>  </P
><DIV
CLASS="REFSECT2"
><H3
>    SQL92
   </H3
><P
>   There is no <B
CLASS="COMMAND"
>CREATE TRIGGER</B
> in <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
>.
   </P
><P
>    The second example above may also be done by using a FOREIGN KEY
    constraint as in:
   </P
><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE distributors (
    did      DECIMAL(3),
    name     VARCHAR(40),
    CONSTRAINT if_film_exists
        FOREIGN KEY(did) REFERENCES films
            ON UPDATE CASCADE ON DELETE CASCADE  
);
  </PRE
><P
>    However, foreign keys are not yet implemented (as of version 6.4) in
    <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>.
   </P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-createtableas.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-createtype.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>CREATE TABLE AS</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>CREATE TYPE</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>