1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>SQLite Query Language: CREATE TRIGGER</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
120
<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>CREATE TRIGGER</h2><h4><a href="syntaxdiagrams.html#create-trigger-stmt">create-trigger-stmt:</a></h4><blockquote> <img alt="syntax diagram create-trigger-stmt" src="images/syntax/create-trigger-stmt.gif"></img> </blockquote>
123
<p>The CREATE TRIGGER statement is used to add triggers to the
124
database schema. Triggers are database operations
125
that are automatically performed when a specified database event
128
<p>A trigger may be specified to fire whenever a <a href="lang_delete.html">DELETE</a>, <a href="lang_insert.html">INSERT</a>,
129
or <a href="lang_update.html">UPDATE</a> of a
130
particular database table occurs, or whenever an <a href="lang_update.html">UPDATE</a> occurs on
131
on one or more specified columns of a table.</p>
133
<p>At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH
134
STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional.
135
FOR EACH ROW implies that the SQL statements specified in the trigger
136
may be executed (depending on the WHEN clause) for each database row being
137
inserted, updated or deleted by the statement causing the trigger to fire.</p>
139
<p>Both the WHEN clause and the trigger actions may access elements of
140
the row being inserted, deleted or updated using references of the form
141
"NEW.<i>column-name</i>" and "OLD.<i>column-name</i>", where
142
<i>column-name</i> is the name of a column from the table that the trigger
143
is associated with. OLD and NEW references may only be used in triggers on
144
events for which they are relevant, as follows:</p>
146
<table border=0 cellpadding=10>
148
<td valign="top" align="right" width=120><i>INSERT</i></td>
149
<td valign="top">NEW references are valid</td>
152
<td valign="top" align="right" width=120><i>UPDATE</i></td>
153
<td valign="top">NEW and OLD references are valid</td>
156
<td valign="top" align="right" width=120><i>DELETE</i></td>
157
<td valign="top">OLD references are valid</td>
162
<p>If a WHEN clause is supplied, the SQL statements specified
163
are only executed for rows for which the WHEN
164
clause is true. If no WHEN clause is supplied, the SQL statements
165
are executed for all rows.</p>
167
<p>The BEFORE or AFTER keyword determines when the trigger actions
168
will be executed relative to the insertion, modification or removal of the
171
<p>An <a href="lang_conflict.html">ON CONFLICT</a> clause may be specified as part of an <a href="lang_update.html">UPDATE</a> or <a href="lang_insert.html">INSERT</a>
172
action within the body of the trigger.
173
However if an <a href="lang_conflict.html">ON CONFLICT</a> clause is specified as part of
174
the statement causing the trigger to fire, then conflict handling
175
policy of the outer statement is used instead.</p>
177
<p>Triggers are automatically <a href="lang_droptrigger.html">dropped</a>
178
when the table that they are
179
associated with (the <i>table-name</i> table) is
180
<a href="lang_droptable.html">dropped</a>. However if the trigger actions reference
181
other tables, the trigger is not dropped or modified if those other
182
tables are <a href="lang_droptable.html">dropped</a> or <a href="lang_altertable.html">modified</a>.</p>
184
<p>Triggers are removed using the <a href="lang_droptrigger.html">DROP TRIGGER</a> statement.</p>
186
<h3>Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within
189
<p>The <a href="lang_update.html">UPDATE</a>, <a href="lang_delete.html">DELETE</a>, and <a href="lang_insert.html">INSERT</a>
190
statements within triggers do not support
191
the full syntax for <a href="lang_update.html">UPDATE</a>, <a href="lang_delete.html">DELETE</a>, and <a href="lang_insert.html">INSERT</a> statements. The following
192
restrictions apply:</p>
196
The name of the table to be modified in an <a href="lang_update.html">UPDATE</a>, <a href="lang_delete.html">DELETE</a>, or <a href="lang_insert.html">INSERT</a>
197
statement must be an unqualified table name. In other words, one must
198
use just "<i>tablename</i>" not "<i>database</i><b>.</b><i>tablename</i>"
199
when specifying the table. The table to be modified must exist in the
200
same database as the table or view to which the trigger is attached.
204
The "INSERT INTO <i>table</i> DEFAULT VALUES" form of the <a href="lang_insert.html">INSERT</a> statement
209
The INDEXED BY and NOT INDEXED clauses are not supported for <a href="lang_update.html">UPDATE</a> and
210
<a href="lang_delete.html">DELETE</a> statements.
214
The ORDER BY and LIMIT clauses on <a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a> statements are not
215
supported. ORDER BY and LIMIT are not normally supported for <a href="lang_update.html">UPDATE</a> or
216
<a href="lang_delete.html">DELETE</a> in any context but can be enabled for top-level statements
217
using the <a href="compile.html#enable_update_delete_limit">SQLITE_ENABLE_UPDATE_DELETE_LIMIT</a> compile-time option. However,
218
that compile-time option only applies to top-level <a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a>
219
statements, not <a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a> statements within triggers.
223
<a name="instead_of_trigger"></a>
225
<h3>INSTEAD OF trigger</h3>
227
<p>Triggers may be created on <a href="lang_createview.html">views</a>, as well as ordinary tables, by
228
specifying INSTEAD OF in the CREATE TRIGGER statement.
229
If one or more ON INSERT, ON DELETE
230
or ON UPDATE triggers are defined on a view, then it is not an
231
error to execute an INSERT, DELETE or UPDATE statement on the view,
232
respectively. Instead,
233
executing an INSERT, DELETE or UPDATE on the view causes the associated
234
triggers to fire. The real tables underlying the view are not modified
235
(except possibly explicitly, by a trigger program).</p>
237
<p>Note that the <a href="c3ref/changes.html">sqlite3_changes()</a> and <a href="c3ref/total_changes.html">sqlite3_total_changes()</a> interfaces
238
do not count INSTEAD OF trigger firings, but the
239
<a href="pragma.html#pragma_count_changes">count_changes pragma</a> does count INSTEAD OF trigger firing.</p>
243
<p>Assuming that customer records are stored in the "customers" table, and
244
that order records are stored in the "orders" table, the following trigger
245
ensures that all associated orders are redirected when a customer changes
246
his or her address:</p>
249
CREATE TRIGGER update_customer_address UPDATE OF address ON customers
251
UPDATE orders SET address = new.address WHERE customer_name = old.name;
255
<p>With this trigger installed, executing the statement:</p>
258
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
261
<p>causes the following to be automatically executed:</p>
264
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
267
<a name="undef_before"></a>
269
<h3>Cautions On The Use Of BEFORE triggers</h3>
271
<p>If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row
272
that was to have been updated or deleted, then the result of the subsequent
273
update or delete operation is undefined. Furthermore, if a BEFORE trigger
274
modifies or deletes a row, then it is undefined whether or not AFTER triggers
275
that would have otherwise run on those rows will in fact run.
278
<p>The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which
279
the rowid is not explicitly set to an integer.</p>
281
<p>Because of the behaviors described above, programmers are encouraged to
282
prefer AFTER triggers over BEFORE triggers.</p>
284
<h3>The RAISE() function</h3>
286
<p>A special SQL function RAISE() may be used within a trigger-program,
287
with the following syntax</p>
289
<h4><a href="syntaxdiagrams.html#raise-function">raise-function:</a></h4><blockquote> <img alt="syntax diagram raise-function" src="images/syntax/raise-function.gif"></img> </blockquote>
292
<p>When one of the first three forms is called during trigger-program
293
execution, the specified <a href="lang_conflict.html">ON CONFLICT</a> processing is performed
294
(either ABORT, FAIL or ROLLBACK) and the current query terminates.
295
An error code of <a href="c3ref/c_abort.html">SQLITE_CONSTRAINT</a> is returned to the application,
296
along with the specified error message.</p>
298
<p>When RAISE(IGNORE) is called, the remainder of the current trigger program,
299
the statement that caused the trigger program to execute and any subsequent
300
trigger programs that would of been executed are abandoned. No database
301
changes are rolled back. If the statement that caused the trigger program
302
to execute is itself part of a trigger program, then that trigger program
303
resumes execution at the beginning of the next step.
added
removed
lang_createtrigger.html
