【Sqlit数据库官方API学习—part02】触发器(Trigger)
来源:互联网 发布:java随机生成4位验证码 编辑:程序博客网 时间:2024/05/16 13:58
At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional.
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:
INSERT
NEW references are valid:插入时没有old数据
UPDATE
NEW and OLD references are valid:更新时有新的数据,也有旧的数据
DELETE
OLD references are valid:删除时没有新的数据
Triggers are automatically dropped when the table that they are associated with (the table-name table) is dropped. However if the trigger actions reference other tables, the trigger is not dropped or modified if those other tables are dropped or modified.
Triggers are removed using the DROP TRIGGER statement.
The UPDATE, DELETE, and INSERT statements within triggers do not support the full syntax for UPDATE, DELETE, and INSERT statements. The following restrictions apply:
· The name of the table to be modified in an UPDATE, DELETE, or INSERT 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.// UPDATE, DELETE, or INSERT语句中的表名必须是an unqualified table name(非完全限定的表名,即只有表名,而不用"database.tablename")
· The "INSERT INTO table DEFAULT VALUES" form of the INSERT statement is not supported.
· The INDEXED BY and NOT INDEXED clauses are not supported for UPDATE and DELETE statements.
· The ORDER BY and LIMIT clauses on UPDATE and DELETE statements are not supported. ORDER BY and LIMIT are not normally supported for UPDATE or DELETE in any context but can be enabled for top-level statements using the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option. However, that compile-time option only applies to top-level UPDATE and DELETE statements, not UPDATE and DELETE statements within triggers.
programmers are encouraged to prefer AFTER triggers over BEFORE triggers.最好不用BEFORE triggers
Assuming that customer records are stored in the "customers" table, and that order records are stored in the "orders" table, the following 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
BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
END;
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';
- 【Sqlit数据库官方API学习—part02】触发器(Trigger)
- 【Sqlit数据库官方API学习—part01】9个基本函数
- 触发器(trigger)学习
- oracle数据库触发器(trigger)用法总结
- 12.数据库对象----触发器(trigger)
- 数据库 触发器 trigger 语法
- MySQL数据库触发器(trigger)
- 数据库CREATE TRIGGER 触发器
- 【Oracle学习】之 触发器(Trigger)
- MySQL触发器 trigger学习
- oracle 数据库 触发器 trigger 语法
- oracle 数据库 触发器 trigger 语法
- sql 触发器(trigger)
- SQLite 触发器(Trigger)
- (17)触发器trigger
- 触发器(TRIGGER)对DB2数据库系统性能的影响
- Mysql Trigger触发器学习总结
- 触发器系列(1)Trigger
- centos7.0的几个新特性
- mongo mongoexport 导出数据为csv格式
- 运算符重载
- vs2012设置tab为4个space的方式
- POJ 3301 Texas Trip
- 【Sqlit数据库官方API学习—part02】触发器(Trigger)
- 如何解决Firefox26第三方插件无法工作,提示需要激活?
- Android实现推送方式解决方案
- Linux:从一台主机向多台主机发送命令的SHELL
- MySQl使用-------如何修改root密码&&解决本地无法登录问题
- Android 实现系统联系人软件的分组和字母表导航效果(优化版)
- nyoj 23 取石子(一)
- hadoop学习笔记1:零IT基础的准备
- QTreeview 选中后 branch区域背景为高亮的橙色.