2
$PostgreSQL: pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.40 2004-11-27 21:27:07 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>
13
<refname>CREATE TRIGGER</refname>
14
<refpurpose>define a new trigger</refpurpose>
17
<indexterm zone="sql-createtrigger">
18
<primary>CREATE TRIGGER</primary>
23
CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER } { <replaceable class="PARAMETER">event</replaceable> [ OR ... ] }
24
ON <replaceable class="PARAMETER">table</replaceable> [ FOR [ EACH ] { ROW | STATEMENT } ]
25
EXECUTE PROCEDURE <replaceable class="PARAMETER">funcname</replaceable> ( <replaceable class="PARAMETER">arguments</replaceable> )
30
<title>Description</title>
33
<command>CREATE TRIGGER</command> creates a new trigger. The
34
trigger will be associated with the specified table and will
35
execute the specified function <replaceable
36
class="parameter">funcname</replaceable> when certain events occur.
40
The trigger can be specified to fire either before the
41
operation is attempted on a row (before constraints are checked and
42
the <command>INSERT</command>, <command>UPDATE</command>, or
43
<command>DELETE</command> is attempted) or after the operation has
44
completed (after constraints are checked and the
45
<command>INSERT</command>, <command>UPDATE</command>, or
46
<command>DELETE</command> has completed). If the trigger fires
47
before the event, the trigger may skip the operation for the
48
current row, or change the row being inserted (for
49
<command>INSERT</command> and <command>UPDATE</command> operations
50
only). If the trigger fires after the event, all changes, including
51
the last insertion, update, or deletion, are <quote>visible</quote>
56
A trigger that is marked <literal>FOR EACH ROW</literal> is called
57
once for every row that the operation modifies. For example, a
58
<command>DELETE</command> that affects 10 rows will cause any
59
<literal>ON DELETE</literal> triggers on the target relation to be
60
called 10 separate times, once for each deleted row. In contrast, a
61
trigger that is marked <literal>FOR EACH STATEMENT</literal> only
62
executes once for any given operation, regardless of how many rows
63
it modifies (in particular, an operation that modifies zero rows
64
will still result in the execution of any applicable <literal>FOR
65
EACH STATEMENT</literal> triggers).
69
If multiple triggers of the same kind are defined for the same event,
70
they will be fired in alphabetical order by name.
74
<command>SELECT</command> does not modify any rows so you can not
75
create <command>SELECT</command> triggers. Rules and views are more
76
appropriate in such cases.
80
Refer to <xref linkend="triggers"> for more information about triggers.
85
<title>Parameters</title>
89
<term><replaceable class="parameter">name</replaceable></term>
92
The name to give the new trigger. This must be distinct from
93
the name of any other trigger for the same table.
99
<term><literal>BEFORE</literal></term>
100
<term><literal>AFTER</literal></term>
103
Determines whether the function is called before or after the
110
<term><replaceable class="parameter">event</replaceable></term>
113
One of <command>INSERT</command>, <command>UPDATE</command>, or
114
<command>DELETE</command>; this specifies the event that will
115
fire the trigger. Multiple events can be specified using
116
<literal>OR</literal>.
122
<term><replaceable class="parameter">table</replaceable></term>
125
The name (optionally schema-qualified) of the table the trigger
132
<term><literal>FOR EACH ROW</literal></term>
133
<term><literal>FOR EACH STATEMENT</literal></term>
137
This specifies whether the trigger procedure should be fired
138
once for every row affected by the trigger event, or just once
139
per SQL statement. If neither is specified, <literal>FOR EACH
140
STATEMENT</literal> is the default.
146
<term><replaceable class="parameter">funcname</replaceable></term>
149
A user-supplied function that is declared as taking no arguments
150
and returning type <literal>trigger</>, which is executed when
157
<term><replaceable class="parameter">arguments</replaceable></term>
160
An optional comma-separated list of arguments to be provided to
161
the function when the trigger is executed. The arguments are
162
literal string constants. Simple names and numeric constants
163
may be written here, too, but they will all be converted to
164
strings. Please check the description of the implementation
165
language of the trigger function about how the trigger arguments
166
are accessible within the function; it may be different from
167
normal function arguments.
174
<refsect1 id="SQL-CREATETRIGGER-notes">
178
To create a trigger on a table, the user must have the
179
<literal>TRIGGER</literal> privilege on the table.
183
In <productname>PostgreSQL</productname> versions before 7.3, it was
184
necessary to declare trigger functions as returning the placeholder
185
type <type>opaque</>, rather than <type>trigger</>. To support loading
186
of old dump files, <command>CREATE TRIGGER</> will accept a function
187
declared as returning <type>opaque</>, but it will issue a notice and
188
change the function's declared return type to <type>trigger</>.
192
Use <xref linkend="sql-droptrigger"
193
endterm="sql-droptrigger-title"> to remove a trigger.
197
<refsect1 id="R1-SQL-CREATETRIGGER-2">
198
<title>Examples</title>
201
<xref linkend="trigger-example"> contains a complete example.
205
<refsect1 id="SQL-CREATETRIGGER-compatibility">
206
<title>Compatibility</title>
209
The <command>CREATE TRIGGER</command> statement in
210
<productname>PostgreSQL</productname> implements a subset of the
211
SQL:1999 standard. (There are no provisions for triggers in SQL-92.)
212
The following functionality is missing:
217
SQL:1999 allows triggers to fire on updates to specific columns
218
(e.g., <literal>AFTER UPDATE OF col1, col2</literal>).
224
SQL:1999 allows you to define aliases for the <quote>old</quote>
225
and <quote>new</quote> rows or tables for use in the definition
226
of the triggered action (e.g., <literal>CREATE TRIGGER ... ON
227
tablename REFERENCING OLD ROW AS somename NEW ROW AS othername
228
...</literal>). Since <productname>PostgreSQL</productname>
229
allows trigger procedures to be written in any number of
230
user-defined languages, access to the data is handled in a
231
language-specific way.
237
<productname>PostgreSQL</productname> only allows the execution
238
of a user-defined function for the triggered action. SQL:1999
239
allows the execution of a number of other SQL commands, such as
240
<command>CREATE TABLE</command> as triggered action. This
241
limitation is not hard to work around by creating a user-defined
242
function that executes the desired commands.
249
SQL:1999 specifies that multiple triggers should be fired in
250
time-of-creation order. <productname>PostgreSQL</productname> uses
251
name order, which was judged more convenient to work with.
255
The ability to specify multiple actions for a single trigger using
256
<literal>OR</literal> is a <productname>PostgreSQL</> extension of
262
<title>See Also</title>
264
<simplelist type="inline">
265
<member><xref linkend="sql-createfunction" endterm="sql-createfunction-title"></member>
266
<member><xref linkend="sql-altertrigger" endterm="sql-altertrigger-title"></member>
267
<member><xref linkend="sql-droptrigger" endterm="sql-droptrigger-title"></member>
272
<!-- Keep this comment at the end of the file
277
sgml-minimize-attributes:nil
278
sgml-always-quote-attributes:t
281
sgml-parent-document:nil
282
sgml-default-dtd-file:"../reference.ced"
283
sgml-exposed-tags:nil
284
sgml-local-catalogs:"/usr/lib/sgml/catalog"
285
sgml-local-ecat-files:nil