2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.23 2002/04/19 16:36:08 tgl Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATETRIGGER">
8 <date>2001-09-13</date>
12 <refentrytitle id="SQL-CREATETRIGGER-TITLE">CREATE TRIGGER</refentrytitle>
13 <refmiscinfo>SQL - Language Statements</refmiscinfo>
25 <date>2000-03-25</date>
28 CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER } { <replaceable class="PARAMETER">event</replaceable> [OR ...] }
29 ON <replaceable class="PARAMETER">table</replaceable> FOR EACH { ROW | STATEMENT }
30 EXECUTE PROCEDURE <replaceable class="PARAMETER">func</replaceable> ( <replaceable class="PARAMETER">arguments</replaceable> )
33 <refsect2 id="R2-SQL-CREATETRIGGER-1">
35 <date>1998-09-21</date>
44 <term><replaceable class="parameter">name</replaceable></term>
47 The name to give the new trigger. This must be distinct from the name
48 of any other trigger for the same table.
53 <term><replaceable class="parameter">event</replaceable></term>
56 One of INSERT, DELETE or UPDATE.
61 <term><replaceable class="parameter">table</replaceable></term>
64 The name (optionally schema-qualified) of the table the trigger is for.
69 <term><replaceable class="parameter">func</replaceable></term>
72 A user-supplied function that is declared as taking no arguments
73 and returning type <literal>opaque</>.
78 <term><replaceable class="parameter">arguments</replaceable></term>
81 An optional comma-separated list of arguments to be provided to the
82 function when the trigger is executed, along with the standard trigger
83 data such as old and new tuple contents. The arguments are literal
84 string constants. Simple names and numeric constants may be written
85 here too, but they will all be converted to strings.
93 <refsect2 id="R2-SQL-CREATETRIGGER-2">
95 <date>1998-09-21</date>
104 <term><computeroutput>
106 </computeroutput></term>
109 This message is returned if the trigger is successfully created.
118 <refsect1 id="R1-SQL-CREATETRIGGER-1">
120 <date>1998-09-21</date>
127 <command>CREATE TRIGGER</command> will enter a new trigger into the current
128 data base. The trigger will be associated with the relation
129 <replaceable class="parameter">table</replaceable> and will execute
130 the specified function <replaceable class="parameter">func</replaceable>.
134 The trigger can be specified to fire either before BEFORE the
135 operation is attempted on a tuple (before constraints are checked and
136 the <command>INSERT</command>, <command>UPDATE</command> or
137 <command>DELETE</command> is attempted) or AFTER the operation has
138 been attempted (e.g., after constraints are checked and the
139 <command>INSERT</command>, <command>UPDATE</command> or
140 <command>DELETE</command> has completed). If the trigger fires before
141 the event, the trigger may skip the operation for the current tuple,
142 or change the tuple being inserted (for <command>INSERT</command> and
143 <command>UPDATE</command> operations only). If the trigger fires
144 after the event, all changes, including the last insertion, update,
145 or deletion, are <quote>visible</quote> to the trigger.
149 If multiple triggers of the same kind are defined for the same event,
150 they will be fired in alphabetical order by name.
154 <command>SELECT</command> does not modify any rows so you can not
155 create <command>SELECT</command> triggers. Rules and views are more
156 appropriate in such cases.
160 Refer to the chapters on SPI and Triggers in the
161 <citetitle>PostgreSQL Programmer's Guide</citetitle> for more
166 <refsect1 id="SQL-CREATETRIGGER-notes">
170 To create a trigger on a table, the user must have the
171 <literal>TRIGGER</literal> privilege on the table.
175 As of the current release, <literal>STATEMENT</literal> triggers are not implemented.
179 Refer to the <xref linkend="sql-droptrigger"> command for
180 information on how to remove triggers.
184 <refsect1 id="R1-SQL-CREATETRIGGER-2">
185 <title>Examples</title>
188 Check if the specified distributor code exists in the distributors
189 table before appending or updating a row in the table films:
192 CREATE TRIGGER if_dist_exists
193 BEFORE INSERT OR UPDATE ON films FOR EACH ROW
194 EXECUTE PROCEDURE check_primary_key ('did', 'distributors', 'did');
199 Before cancelling a distributor or updating its code, remove every
200 reference to the table films:
202 CREATE TRIGGER if_film_exists
203 BEFORE DELETE OR UPDATE ON distributors FOR EACH ROW
204 EXECUTE PROCEDURE check_foreign_key (1, 'CASCADE', 'did', 'films', 'did');
209 The second example can also be done by using a foreign key,
213 CREATE TABLE distributors (
216 CONSTRAINT if_film_exists
217 FOREIGN KEY(did) REFERENCES films
218 ON UPDATE CASCADE ON DELETE CASCADE
224 <refsect1 id="SQL-CREATETRIGGER-compatibility">
225 <title>Compatibility</title>
232 There is no <command>CREATE TRIGGER</command> statement in <acronym>SQL92</acronym>.
241 The <command>CREATE TRIGGER</command> statement in
242 <productname>PostgreSQL</productname> implements a subset of the
243 SQL99 standard. The following functionality is missing:
247 SQL99 allows triggers to fire on updates to specific columns
248 (e.g., <literal>AFTER UPDATE OF col1, col2</literal>).
254 SQL99 allows you to define aliases for the <quote>old</quote>
255 and <quote>new</quote> rows or tables for use in the definition
256 of the triggered action (e.g., <literal>CREATE TRIGGER ... ON
257 tablename REFERENCING OLD ROW AS somename NEW ROW AS
258 othername ...</literal>). Since
259 <productname>PostgreSQL</productname> allows trigger
260 procedures to be written in any number of user-defined
261 languages, access to the data is handled in a
262 language-specific way.
268 <productname>PostgreSQL</productname> only has row-level
269 triggers, no statement-level triggers.
275 <productname>PostgreSQL</productname> only allows the
276 execution of a stored procedure for the triggered action.
277 SQL99 allows the execution of a number of other SQL commands,
278 such as <command>CREATE TABLE</command> as triggered action.
279 This limitation is not hard to work around by creating a
280 stored procedure that executes these commands.
287 SQL99 specifies that multiple triggers should be fired in
288 time-of-creation order. <productname>PostgreSQL</productname>
289 uses name order, which was judged more convenient to work with.
297 <title>See Also</title>
299 <simplelist type="inline">
300 <member><xref linkend="sql-createfunction"></member>
301 <member><xref linkend="sql-droptrigger"></member>
302 <member><citetitle>PostgreSQL Programmer's Guide</citetitle></member>
307 <!-- Keep this comment at the end of the file
312 sgml-minimize-attributes:nil
313 sgml-always-quote-attributes:t
316 sgml-parent-document:nil
317 sgml-default-dtd-file:"../reference.ced"
318 sgml-exposed-tags:nil
319 sgml-local-catalogs:"/usr/lib/sgml/catalog"
320 sgml-local-ecat-files:nil