2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.33 2003/03/25 16:15:39 petere Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATETRIGGER">
8 <refentrytitle id="SQL-CREATETRIGGER-TITLE">CREATE TRIGGER</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
21 <date>2000-03-25</date>
24 CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER } { <replaceable class="PARAMETER">event</replaceable> [ OR ... ] }
25 ON <replaceable class="PARAMETER">table</replaceable> [ FOR [ EACH ] { ROW | STATEMENT } ]
26 EXECUTE PROCEDURE <replaceable class="PARAMETER">func</replaceable> ( <replaceable class="PARAMETER">arguments</replaceable> )
29 <refsect2 id="R2-SQL-CREATETRIGGER-1">
31 <date>1998-09-21</date>
40 <term><replaceable class="parameter">name</replaceable></term>
43 The name to give the new trigger. This must be distinct from the name
44 of any other trigger for the same table.
54 Determines whether the function is called before or after the
61 <term><replaceable class="parameter">event</replaceable></term>
64 One of <command>INSERT</command>, <command>DELETE</command> or
65 <command>UPDATE</command>; this specifies the event that will
66 fire the trigger. Multiple events can be specified using
67 <literal>OR</literal>.
72 <term><replaceable class="parameter">table</replaceable></term>
75 The name (optionally schema-qualified) of the table the
82 <term>FOR EACH ROW</term>
83 <term>FOR EACH STATEMENT</term>
87 This specifies whether the trigger procedure should be fired
88 once for every row affected by the trigger event, or just once
89 per SQL statement. If neither is specified, <literal>FOR EACH
90 STATEMENT</literal> is the default.
96 <term><replaceable class="parameter">func</replaceable></term>
99 A user-supplied function that is declared as taking no arguments
100 and returning type <literal>trigger</>.
105 <term><replaceable class="parameter">arguments</replaceable></term>
108 An optional comma-separated list of arguments to be provided to
109 the function when the trigger is executed, along with the standard
110 trigger data such as old and new tuple contents. The arguments
111 are literal string constants. Simple names and numeric constants
112 may be written here too, but they will all be converted to
113 strings. Note that these arguments are not provided as normal
114 function parameters (since a trigger procedure must be declared to
115 take zero parameters), but are instead accessed through the
116 <literal>TG_ARGV</literal> array.
124 <refsect2 id="R2-SQL-CREATETRIGGER-2">
126 <date>1998-09-21</date>
135 <term><computeroutput>
137 </computeroutput></term>
140 This message is returned if the trigger is successfully created.
149 <refsect1 id="R1-SQL-CREATETRIGGER-1">
151 <date>1998-09-21</date>
158 <command>CREATE TRIGGER</command> will enter a new trigger into the current
159 database. The trigger will be associated with the relation
160 <replaceable class="parameter">table</replaceable> and will execute
161 the specified function <replaceable class="parameter">func</replaceable>.
165 The trigger can be specified to fire either before BEFORE the
166 operation is attempted on a tuple (before constraints are checked and
167 the <command>INSERT</command>, <command>UPDATE</command> or
168 <command>DELETE</command> is attempted) or AFTER the operation has
169 been attempted (e.g., after constraints are checked and the
170 <command>INSERT</command>, <command>UPDATE</command> or
171 <command>DELETE</command> has completed). If the trigger fires before
172 the event, the trigger may skip the operation for the current tuple,
173 or change the tuple being inserted (for <command>INSERT</command> and
174 <command>UPDATE</command> operations only). If the trigger fires
175 after the event, all changes, including the last insertion, update,
176 or deletion, are <quote>visible</quote> to the trigger.
180 A trigger that executes <literal>FOR EACH ROW</literal> of the
181 specified operation is called once for every row that the operation
182 modifies. For example, a <command>DELETE</command> that affects 10
183 rows will cause any <literal>ON DELETE</literal> triggers on the
184 target relation to be called 10 separate times, once for each
185 deleted tuple. In contrast, a trigger that executes <literal>FOR
186 EACH STATEMENT</literal> of the specified operation only executes
187 once for any given operation, regardless of how many rows it
188 modifies (in particular, an operation that modifies zero rows will
189 still result in the execution of any applicable <literal>FOR EACH
190 STATEMENT</literal> triggers).
194 If multiple triggers of the same kind are defined for the same event,
195 they will be fired in alphabetical order by name.
199 <command>SELECT</command> does not modify any rows so you can not
200 create <command>SELECT</command> triggers. Rules and views are more
201 appropriate in such cases.
205 Refer to <xref linkend="server-programming"> for more information.
209 <refsect1 id="SQL-CREATETRIGGER-notes">
213 To create a trigger on a table, the user must have the
214 <literal>TRIGGER</literal> privilege on the table.
218 In <productname>PostgreSQL</productname> versions before 7.3, it was
219 necessary to declare trigger functions as returning the placeholder
220 type <type>opaque</>, rather than <type>trigger</>. To support loading
221 of old dump files, <command>CREATE TRIGGER</> will accept a function
222 declared as returning <type>opaque</>, but it will issue a NOTICE and
223 change the function's declared return type to <type>trigger</>.
227 Refer to the <xref linkend="sql-droptrigger" endterm="sql-droptrigger-title"> command for
228 information on how to remove triggers.
232 <refsect1 id="R1-SQL-CREATETRIGGER-2">
233 <title>Examples</title>
236 Check if the specified distributor code exists in the distributors
237 table before appending or updating a row in the table films:
240 CREATE TRIGGER if_dist_exists
241 BEFORE INSERT OR UPDATE ON films FOR EACH ROW
242 EXECUTE PROCEDURE check_primary_key ('did', 'distributors', 'did');
247 Before cancelling a distributor or updating its code, remove every
248 reference to the table films:
250 CREATE TRIGGER if_film_exists
251 BEFORE DELETE OR UPDATE ON distributors FOR EACH ROW
252 EXECUTE PROCEDURE check_foreign_key (1, 'CASCADE', 'did', 'films', 'did');
257 The second example can also be done by using a foreign key,
261 CREATE TABLE distributors (
264 CONSTRAINT if_film_exists
265 FOREIGN KEY(did) REFERENCES films
266 ON UPDATE CASCADE ON DELETE CASCADE
272 <refsect1 id="SQL-CREATETRIGGER-compatibility">
273 <title>Compatibility</title>
280 There is no <command>CREATE TRIGGER</command> statement in <acronym>SQL92</acronym>.
289 The <command>CREATE TRIGGER</command> statement in
290 <productname>PostgreSQL</productname> implements a subset of the
291 SQL99 standard. The following functionality is missing:
295 SQL99 allows triggers to fire on updates to specific columns
296 (e.g., <literal>AFTER UPDATE OF col1, col2</literal>).
302 SQL99 allows you to define aliases for the <quote>old</quote>
303 and <quote>new</quote> rows or tables for use in the definition
304 of the triggered action (e.g., <literal>CREATE TRIGGER ... ON
305 tablename REFERENCING OLD ROW AS somename NEW ROW AS
306 othername ...</literal>). Since
307 <productname>PostgreSQL</productname> allows trigger
308 procedures to be written in any number of user-defined
309 languages, access to the data is handled in a
310 language-specific way.
316 <productname>PostgreSQL</productname> only allows the
317 execution of a stored procedure for the triggered action.
318 SQL99 allows the execution of a number of other SQL commands,
319 such as <command>CREATE TABLE</command> as triggered action.
320 This limitation is not hard to work around by creating a
321 stored procedure that executes these commands.
328 SQL99 specifies that multiple triggers should be fired in
329 time-of-creation order. <productname>PostgreSQL</productname>
330 uses name order, which was judged more convenient to work with.
334 The ability to specify multiple actions for a single trigger
335 using <literal>OR</literal> is a <productname>PostgreSQL</>
336 extension of the SQL standard.
344 <title>See Also</title>
346 <simplelist type="inline">
347 <member><xref linkend="sql-createfunction" endterm="sql-createfunction-title"></member>
348 <member><xref linkend="sql-altertrigger" endterm="sql-altertrigger-title"></member>
349 <member><xref linkend="sql-droptrigger" endterm="sql-droptrigger-title"></member>
354 <!-- Keep this comment at the end of the file
359 sgml-minimize-attributes:nil
360 sgml-always-quote-attributes:t
363 sgml-parent-document:nil
364 sgml-default-dtd-file:"../reference.ced"
365 sgml-exposed-tags:nil
366 sgml-local-catalogs:"/usr/lib/sgml/catalog"
367 sgml-local-ecat-files:nil