📄 lang_createtrigger.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"><html><head><title>SQLite Query Language: CREATE TRIGGER</title><style type="text/css">body { margin: auto; font-family: "Verdana" "sans-serif"; padding: 8px 1%;}a { color: #45735f }a:visited { color: #734559 }.logo { position:absolute; margin:3px; }.tagline { float:right; text-align:right; font-style:italic; width:240px; margin:12px; margin-top:58px;}.toolbar { font-variant: small-caps; text-align: center; line-height: 1.6em; margin: 0; padding:1px 8px;}.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }.toolbar a:visited { color: white; }.toolbar a:hover { color: #80a796; background: white; }.content { margin: 5%; }.content dt { font-weight:bold; }.content dd { margin-bottom: 25px; margin-left:20%; }.content ul { padding:0px; padding-left: 15px; margin:0px; }/* rounded corners */.se { background: url(images/se.png) 100% 100% no-repeat #80a796}.sw { background: url(images/sw.png) 0% 100% no-repeat }.ne { background: url(images/ne.png) 100% 0% no-repeat }.nw { background: url(images/nw.png) 0% 0% no-repeat }</style><meta http-equiv="content-type" content="text/html; charset=UTF-8"> </head><body><div><!-- container div to satisfy validator --><a href="index.html"><img class="logo" src="images/SQLite.gif" alt="SQLite Logo" border="0"></a><div><!-- IE hack to prevent disappearing logo--></div><div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div><table width=100% style="clear:both"><tr><td> <div class="se"><div class="sw"><div class="ne"><div class="nw"> <div class="toolbar"> <a href="about.html">About</a> <a href="sitemap.html">Sitemap</a> <a href="docs.html">Documentation</a> <a href="download.html">Download</a> <a href="copyright.html">License</a> <a href="news.html">News</a> <a href="http://www.sqlite.org/cvstrac/index">Developers</a> <a href="support.html">Support</a> </div></div></div></div></div></td></tr></table> <a href="lang.html"><h2>SQL As Understood By SQLite</h2></a><h3>CREATE TRIGGER</h3><table cellpadding="10"><tr><td align="right" valign="top"><i><font color="#ff3434">sql-statement</font></i> ::=</td><td><b><font color="#2c2cf0">CREATE </font></b>[<b><font color="#2c2cf0">TEMP </font></b><big>|</big><b><font color="#2c2cf0"> TEMPORARY</font></b>]<b><font color="#2c2cf0"> TRIGGER </font></b>[<b><font color="#2c2cf0">IF NOT EXISTS</font></b>]<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">trigger-name</font></i><b><font color="#2c2cf0"> </font></b>[<b><font color="#2c2cf0"> BEFORE </font></b><big>|</big><b><font color="#2c2cf0"> AFTER </font></b>]<b><font color="#2c2cf0"><br></font></b><i><font color="#ff3434">database-event</font></i><b><font color="#2c2cf0"> ON </font></b>[<b><font color="#2c2cf0"></font></b><i><font color="#ff3434">database-name</font></i><b><font color="#2c2cf0"> <big>.</big></font></b>]<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">table-name</font></i><b><font color="#2c2cf0"><br></font></b><i><font color="#ff3434">trigger-action</font></i><b><font color="#2c2cf0"></font></b></td></tr></table><table cellpadding="10"><tr><td align="right" valign="top"><i><font color="#ff3434">sql-statement</font></i> ::=</td><td><b><font color="#2c2cf0">CREATE </font></b>[<b><font color="#2c2cf0">TEMP </font></b><big>|</big><b><font color="#2c2cf0"> TEMPORARY</font></b>]<b><font color="#2c2cf0"> TRIGGER </font></b>[<b><font color="#2c2cf0">IF NOT EXISTS</font></b>]<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">trigger-name</font></i><b><font color="#2c2cf0"> INSTEAD OF<br></font></b><i><font color="#ff3434">database-event</font></i><b><font color="#2c2cf0"> ON </font></b>[<b><font color="#2c2cf0"></font></b><i><font color="#ff3434">database-name</font></i><b><font color="#2c2cf0"> <big>.</big></font></b>]<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">view-name</font></i><b><font color="#2c2cf0"><br></font></b><i><font color="#ff3434">trigger-action</font></i><b><font color="#2c2cf0"></font></b></td></tr></table><table cellpadding="10"><tr><td align="right" valign="top"><i><font color="#ff3434">database-event</font></i> ::=</td><td><b><font color="#2c2cf0">DELETE </font></b><big>|</big><b><font color="#2c2cf0"> <br>INSERT </font></b><big>|</big><b><font color="#2c2cf0"> <br>UPDATE </font></b><big>|</big><b><font color="#2c2cf0"> <br>UPDATE OF </font></b><i><font color="#ff3434">column-list</font></i><b><font color="#2c2cf0"></font></b></td></tr></table><table cellpadding="10"><tr><td align="right" valign="top"><i><font color="#ff3434">trigger-action</font></i> ::=</td><td><b><font color="#2c2cf0"></font></b>[<b><font color="#2c2cf0"> FOR EACH ROW </font></b>]<b><font color="#2c2cf0"> </font></b>[<b><font color="#2c2cf0"> WHEN </font></b><i><font color="#ff3434">expression</font></i><b><font color="#2c2cf0"> </font></b>]<b><font color="#2c2cf0"> <br>BEGIN <br> </font></b><i><font color="#ff3434">trigger-step</font></i><b><font color="#2c2cf0"> ; </font></b>[<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">trigger-step</font></i><b><font color="#2c2cf0"> ; </font></b>]<b><font color="#2c2cf0"></font></b><big>*</big><b><font color="#2c2cf0"><br>END</font></b></td></tr></table><table cellpadding="10"><tr><td align="right" valign="top"><i><font color="#ff3434">trigger-step</font></i> ::=</td><td><b><font color="#2c2cf0"></font></b><i><font color="#ff3434">update-statement</font></i><b><font color="#2c2cf0"> </font></b><big>|</big><b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">insert-statement</font></i><b><font color="#2c2cf0"> </font></b><big>|</big><b><font color="#2c2cf0"> <br></font></b><i><font color="#ff3434">delete-statement</font></i><b><font color="#2c2cf0"> </font></b><big>|</big><b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">select-statement</font></i><b><font color="#2c2cf0"></font></b></td></tr></table><p>The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers are database operations (the <i>trigger-action</i>) that are automatically performed when a specified database event (the<i>database-event</i>) occurs. </p><p>A trigger may be specified to fire whenever a <a href="lang_delete.html">DELETE</a>, <a href="lang_insert.html">INSERT</a>,or <a href="lang_update.html">UPDATE</a> of aparticular database table occurs, or whenever an <a href="lang_update.html">UPDATE</a> of one or morespecified columns of a table are updated.</p><p>At this time SQLite supports only FOR EACH ROW triggers, not FOR EACHSTATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional. FOREACH ROW implies that the SQL statements specified as <i>trigger-steps</i> may be executed (depending on the WHEN clause) for each database row beinginserted, updated or deleted by the statement causing the trigger to fire.</p><p>Both the WHEN clause and the <i>trigger-steps</i> may access elements of the row being inserted, deleted or updated using references of the form "NEW.<i>column-name</i>" and "OLD.<i>column-name</i>", where<i>column-name</i> is the name of a column from the table that the triggeris associated with. OLD and NEW references may only be used in triggers on<i>trigger-event</i>s for which they are relevant, as follows:</p><table border=0 cellpadding=10><tr><td valign="top" align="right" width=120><i>INSERT</i></td><td valign="top">NEW references are valid</td></tr><tr><td valign="top" align="right" width=120><i>UPDATE</i></td><td valign="top">NEW and OLD references are valid</td></tr><tr><td valign="top" align="right" width=120><i>DELETE</i></td><td valign="top">OLD references are valid</td></tr></table></p><p>If a WHEN clause is supplied, the SQL statements specified as <i>trigger-steps</i> are only executed for rows for which the WHENclause is true. If no WHEN clause is supplied, the SQL statementsare executed for all rows.</p><p>The specified <i>trigger-time</i> determines when the <i>trigger-steps</i>will be executed relative to the insertion, modification or removal of theassociated row.</p><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><i>trigger-step</i>. However if an <a href="lang_conflict.html">ON CONFLICT</a> clause is specified as part of the statement causing the trigger to fire, then this conflict handlingpolicy is used instead.</p><p>Triggers are automatically dropped when the table that they are associated with is dropped.</p><a name="instead_of_trigger"></a><p>Triggers may be created on <a href="lang_createview.html">views</a>, as well as ordinary tables, byspecifying INSTEAD OF in the CREATE TRIGGER statement. If one or more ON INSERT, ON DELETEor ON UPDATE triggers are defined on a view, then it is not anerror to execute an INSERT, DELETE or UPDATE statement on the view, respectively. Thereafter,executing an INSERT, DELETE or UPDATE on the view causes the associatedtriggers to fire. The real tables underlying the view are not modified(except possibly explicitly, by a trigger program).</p><p><b>Example:</b></p><p>Assuming that customer records are stored in the "customers" table, andthat order records are stored in the "orders" table, the following triggerensures that all associated orders are redirected when a customer changeshis or her address:</p><blockquote><pre>CREATE TRIGGER update_customer_address UPDATE OF address ON customers BEGIN UPDATE orders SET address = new.address WHERE customer_name = old.name; END;</pre></blockquote><p>With this trigger installed, executing the statement:</p><blockquote><pre>UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';</pre></blockquote><p>causes the following to be automatically executed:</p><blockquote><pre>UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';</pre></blockquote><p>Note that currently, triggers may behave oddly when created on tables with <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> fields. If a BEFORE trigger program modifies the <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> field of a row that will be subsequently updated by the statement that causes the trigger to fire, then the update may not occur. The workaround is to declare the table with a PRIMARY KEY column instead of an <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column.</p><p>A special SQL function RAISE() may be used within a trigger-program,with the following syntax</p> <table cellpadding="10"><tr><td align="right" valign="top"><i><font color="#ff3434">raise-function</font></i> ::=</td><td><b><font color="#2c2cf0">RAISE <big>(</big> ABORT<big>,</big> </font></b><i><font color="#ff3434">error-message</font></i><b><font color="#2c2cf0"> <big>)</big> </font></b><big>|</big><b><font color="#2c2cf0"> <br>RAISE <big>(</big> FAIL<big>,</big> </font></b><i><font color="#ff3434">error-message</font></i><b><font color="#2c2cf0"> <big>)</big> </font></b><big>|</big><b><font color="#2c2cf0"> <br>RAISE <big>(</big> ROLLBACK<big>,</big> </font></b><i><font color="#ff3434">error-message</font></i><b><font color="#2c2cf0"> <big>)</big> </font></b><big>|</big><b><font color="#2c2cf0"> <br>RAISE <big>(</big> IGNORE <big>)</big></font></b></td></tr></table><p>When one of the first three forms is called during trigger-programexecution, the specified <a href="lang_conflict.html">ON CONFLICT</a> processing is performed(either ABORT, FAIL or ROLLBACK) and the current query terminates.An error code of <a href="c3ref/c_abort.html">SQLITE_CONSTRAINT</a> is returned to the application,along with the specified error message.</p><p>When RAISE(IGNORE) is called, the remainder of the current trigger program,the statement that caused the trigger program to execute and any subsequenttrigger programs that would of been executed are abandoned. No databasechanges are rolled back. If the statement that caused the trigger programto execute is itself part of a trigger program, then that trigger programresumes execution at the beginning of the next step.</p><p>Triggers are removed using the <a href="lang_droptrigger.html">DROP TRIGGER</a> statement.</p><hr><small><i>This page last modified 2008/04/27 17:48:52 UTC</i></small></div></body></html>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -