SQL As Understood By SQLiteCREATE TRIGGER
The CREATE TRIGGER statement is used to add triggers to the
database schema. Triggers are database operations
that are automatically performed when a specified database event
A trigger may be specified to fire whenever a , ,
particular database table occurs, or whenever an
on one or more specified columns of a table.
At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH
STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional.
FOR EACH ROW implies that the SQL statements specified in the trigger
may be executed (depending on the WHEN clause) for each database row being
inserted, updated or deleted by the statement causing the trigger to fire.
Both the WHEN clause and the trigger actions may access elements of
the row being inserted, deleted or updated using references of the form
"NEW.column-name" and "OLD.column-name", where
column-name is the name of a column from the table that the trigger
is associated with. OLD and NEW references may only be used in triggers on
events for which they are relevant, as follows:
NEW references are valid
NEW and OLD references are valid
OLD references are valid
If a WHEN clause is supplied, the SQL statements specified
are only executed for rows for which the WHEN
clause is true. If no WHEN clause is supplied, the SQL statements
are executed for all rows.
The BEFORE or AFTER keyword determines when the trigger actions
will be executed relative to the insertion, modification or removal of the
associated row.
clause may be specified as part of an
action within the body of the trigger.
However if an
clause is specified as part of
the statement causing the trigger to fire, then conflict handling
policy of the outer statement is used instead.
Triggers are automatically
when the table that they are
associated with (the table-name table) is
However if the trigger actions reference
other tables, the trigger is not dropped or modified if those other
tables are
Triggers are removed using the
statement.
Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within
The , , and
statements within triggers do not support
the full syntax for , , and
statements.
The following
restrictions apply:
The name of the table to be modified in an , , or
statement must be an unqualified table name.
In other words, one must
use just "tablename" not "database.tablename"
when specifying the table.
The table to be modified must exist in the
same database as the table or view to which the trigger is attached.
The "INSERT INTO table DEFAULT VALUES" form of the
is not supported.
The INDEXED BY and NOT INDEXED clauses are not supported for
statements.
The ORDER BY and LIMIT clauses on
statements are not
supported.
ORDER BY and LIMIT are not normally supported for
in any context but can be enabled for top-level statements
compile-time option.
that compile-time option only applies to top-level
statements, not
statements within triggers.
are not supported for
statements inside of triggers.
INSTEAD OF triggers
Triggers may be created on , as well as ordinary tables, by
specifying INSTEAD OF in the CREATE TRIGGER statement.
If one or more ON INSERT, ON DELETE
or ON UPDATE triggers are defined on a view, then it is not an
error to execute an INSERT, DELETE or UPDATE statement on the view,
respectively.
executing an INSERT, DELETE or UPDATE on the view causes the associated
triggers to fire. The real tables underlying the view are not modified
(except possibly explicitly, by a trigger program).
Note that the
interfaces
do not count INSTEAD OF trigger firings, but the
does count INSTEAD OF trigger firing.
Some Example Triggers
Assuming that customer records are stored in the "customers" table, and
that order records are stored in the "orders" table, the following
UPDATE trigger
ensures that all associated orders are redirected when a customer changes
his or her address:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers
UPDATE orders SET address = new.address WHERE customer_name = old.
With this trigger installed, executing the statement:
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
causes the following to be automatically executed:
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
For an example of an INSTEAD OF trigger, consider the following schema:
CREATE TABLE customer(
cust_id INTEGER PRIMARY KEY,
cust_name TEXT,
cust_addr TEXT
CREATE VIEW customer_address AS
SELECT cust_id, cust_addr FROM
CREATE TRIGGER cust_addr_chng
INSTEAD OF UPDATE OF cust_addr ON customer_address
UPDATE customer SET cust_addr=NEW.cust_addr
WHERE cust_id=NEW.cust_
With the schema above, a statement of the form:
UPDATE customer_address SET cust_addr=$new_address WHERE cust_id=$cust_
Causes the customer.cust_addr field to be updated for a specific
customer entry that has customer.cust_id equal to the $cust_id parameter.
Note how the values assigned to the view are made available as field
in the special "NEW" table within the trigger body.
Cautions On The Use Of BEFORE triggers
If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row
that was to have been updated or deleted, then the result of the subsequent
update or delete operation is undefined.
Furthermore, if a BEFORE trigger
modifies or deletes a row, then it is undefined whether or not AFTER triggers
that would have otherwise run on those rows will in fact run.
The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which
the rowid is not explicitly set to an integer.
Because of the behaviors described above, programmers are encouraged to
prefer AFTER triggers over BEFORE triggers.
The RAISE() function
A special SQL function RAISE() may be used within a trigger-program,
with the following syntax
When one of RAISE(ROLLBACK,...), RAISE(ABORT,...) or RAISE(FAIL,...)
is called during trigger-program
execution, the specified
processing is performed
the current query terminates.
An error code of
is returned to the application,
along with the specified error message.
When RAISE(IGNORE) is called, the remainder of the current trigger program,
the statement that caused the trigger program to execute and any subsequent
trigger programs that would have been executed are abandoned. No database
changes are rolled back.
If the statement that caused the trigger program
to execute is itself part of a trigger program, then that trigger program
resumes execution at the beginning of the next step.
TEMP Triggers on Non-TEMP Tables
A trigger normally exists in the same database as the table named
after the "ON" keyword in the CREATE TRIGGER statement.
Except, it is
possible to create a TEMP TRIGGER on a table in another database.
Such a trigger will only fire when changes
are made to the target table by the application that defined the trigger.
Other applications that modify the database will not be able to see the
TEMP trigger and hence cannot run the trigger.
When defining a TEMP trigger on a non-TEMP table, it is important to
specify the database holding the non-TEMP table.
For example,
in the following statement, it is important to say "main.tab1" instead
of just "tab1":
CREATE TEMP TRIGGER ex1 AFTER INSERT ON main.tab1 BEGIN ...
Failure to specify the schema name on the target table could result
in the TEMP trigger being reattached to a table with the same name in
another database whenever any schema change occurs.【图文】第10章 销售管理数据库中触发器的应用_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
评价文档：
第10章 销售管理数据库中触发器的应用
上传于||暂无简介
大小：672.50KB
登录百度文库，专享文档复制特权，财富值每天免费拿！
你可能喜欢如何在oracle数据库表里创建触发器？
[问题点数：40分，结帖人TANGLIJUNASP]
如何在oracle数据库表里创建触发器？
[问题点数：40分，结帖人TANGLIJUNASP]
不显示删除回复
显示所有回复
显示星级回复
显示得分回复
只显示楼主
2010年9月 Oracle大版内专家分月排行榜第二
2011年4月 Oracle大版内专家分月排行榜第三2011年3月 Oracle大版内专家分月排行榜第三2011年1月 Oracle大版内专家分月排行榜第三
2010年9月 Oracle大版内专家分月排行榜第二
2011年4月 Oracle大版内专家分月排行榜第三2011年3月 Oracle大版内专家分月排行榜第三2011年1月 Oracle大版内专家分月排行榜第三
本帖子已过去太久远了，不再提供回复功能。<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
您的访问请求被拒绝 403 Forbidden - ITeye技术社区
您的访问请求被拒绝
亲爱的会员，您的IP地址所在网段被ITeye拒绝服务，这可能是以下两种情况导致：
一、您所在的网段内有网络爬虫大量抓取ITeye网页，为保证其他人流畅的访问ITeye，该网段被ITeye拒绝
二、您通过某个代理服务器访问ITeye网站，该代理服务器被网络爬虫利用，大量抓取ITeye网页
请您点击按钮解除封锁&