sqlite 触发器 详解 demo
(2012-05-29 15:55:58)
今天 做工具的时候 碰到了这个问题 ,sqlite
是支持触发器的 ,但是并不是全部支持,我在网上搜了一下,还是有一些朋友比较细心,做了总结,我直接拿来主义了啊 ,哈哈,博客原地址
SQLite 触发器的SQL语法
CREATE TRIGGER
sql-statement ::=
CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [ BEFORE | AFTER ]
database-event ON [database-name .] table-name trigger-action
sql-statement ::=
CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF
database-event ON [database-name .] view-name trigger-action
database-event ::=
DELETE | INSERT | UPDATE |
UPDATE OF column-list
trigger-action ::=
[ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN
expression ]
BEGIN
trigger-step [ trigger-step ]* END
trigger-step ::=
update-statement | insert-statement |
阅读会员限时特惠 7大会员特权立即尝鲜
delete-statement | select-statement
CREATE TRIGGER语句用于向数据库schema中添加触发器。触发器是一些在特定的数据库事件(database-event) 发生时自动进行的数据库操作(trigger-action).
触发器可由在特殊表上执行的DELETE, INSERT, UPDATE等语句触发,或UPDATE表中特定的字段时触发。
现在SQLite仅支持FOR EACH ROW触发器,不支持FOR EACH STATEMENT触发。因此可以不用明确说明FOR EACH ROW .FOR EACH ROW的意思是由trigger-steps说明的SQL语句可能在(由WHEN子句决定的)数据库插入, 更改或删除的每一行触发trigger.
WHEN子句和trigger-steps可以使用"NEW.column-name"和"OLD.column-name" 的引用形式访问正在被插入,更改或 删除的行的元素,column-name是触发器关联的表中的字段名。OLD 和 NEW 引用只在触发器与之相关的trigger-event处可用,例如:
INSERT NEW可用 UPDATE NEW和OLD均可用 DELETE OLD可用
当使用WHEN子句,trigger-steps只在WHEN子句为真的行执行。不使用WHEN时则在所有行执行。
trigger-time决定了trigger-steps执行的时间,它是相对于关联行的插入,
删除和修改而言的。
作为的一部分trigger-step的UPDATE 或 INSERT可以使用ON CONFLICT子句。
但若触发trigger的语句使用了ON CONFLICT子句,则覆盖前述的ON CONFLICT子句所定义的冲突处理方法。 关联表被撤销时触发器被自动删除。
不仅在表上,在视图上一样可以创建触发器,在CREATE TRIGGER语句中使用INSTEAD OF即可。 若视图上定义了一个或多个ON INSERT, ON DELETE, ON UPDATE触发器,则相应地对视图执行INSERT,DELETE 或UPDATE语句 不会出错,而会触发关联的触发器。视图关联的表不会被修改。(除了由触发器进行的修改操作)。
Example:
假设"customers"表存储了客户信息,"orders"表存储了订单信息,下面的触发器确保当用户改变地址时所有的 关联订单地址均进行相应改变:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name; END;
定义了该触发器后执行如下语句:
UPDATE customers SET address = ’1 Main St.’ WHERE name = ’Jack Jones’; 会使下面的语句自动执行:
UPDATE orders SET address = ’1 Main St.’ WHERE customer_name = ’Jack Jones’;
注意,目前在有INTEGER PRIMARY KEY域的表上触发器可能工作不正常。若BEFORE触发器修改了一行的 INTEGER PRIMARY KEY域,而该域将由触发该触发器的语句进行修改,则可能根本不会修改该域。 可以用PRIMARY KEY字段代替INTEGER PRIMARY KEY字段来解决上述问题。
一个特殊的SQL函数RAISE()可用于触发器程序,使用如下语法:
raise-function ::=
RAISE ( ABORT, error-message ) | RAISE ( FAIL, error-message ) | RAISE ( ROLLBACK, error-message ) | RAISE ( IGNORE )
当触发器程序执行中调用了上述前三个之一的形式时,则执行指定的ON CONFLICT进程(ABORT, FAIL或者ROLLBACK) 且终止当前查询,返回一个SQLITE_CONSTRAINT错误并说明错误信息。
--创建班级表
create table class
(
id integer primary key autoincrement, --班级编号
className nvarchar(50) --班级名称
);
--创建学生表
create table student
(
id integer primary key autoincrement, --编号
stuName nvarchar(20), --学生名称
stuSex bit, --性别
stuAge integer , --年龄
classId --班级编号
);
--创建插入触发器 (创建学生时要触发插入触发器去判断是否存在该班级,存在插入成功,反之插入失败)
create trigger fk_Insert
before insert on student
for each row
begin
select raise(rollback,'还没有该班级')
where (select id from class where id = new.classId ) is null;
end;
--创建更新触发器 (更新学生时要触发更新触发器去判断是否存在更新班级,存在更新成功,反之更新失败)
create trigger fk_Update
before update on student
for each row
begin
select raise(rollback,'还没有该班级')
where (select id from class where id = new.classId)is null;
end;
--创建删除触发器 (删除班级时,首先根据班级编号删除该班级学生)
create trigger fk_Delete
before delete on class
for each row
begin
delete from student where classId = old.classId;
end ;
insert into class(className) values('s1t64');
insert into student(stuName,stuSex,stuAge,classId)values('zhangsan',1,23,1);
update student set stuName='lishi',classId=1 where id = 1;
select * from class ;
select * from student limit 0,100 ; -- 分页查询从索引0开始查找,100条数据
create table class
(
);
--创建学生表
create table student
(
);
--创建插入触发器 (创建学生时要触发插入触发器去判断是否存在该班级,存在插入成功,反之插入失败)
create trigger fk_Insert
before insert on student
for each row
begin
end;
--创建更新触发器 (更新学生时要触发更新触发器去判断是否存在更新班级,存在更新成功,反之更新失败)
create trigger fk_Update
before update on student
for each row
begin
end;
--创建删除触发器 (删除班级时,首先根据班级编号删除该班级学生)
create trigger fk_Delete
before delete on class
for each row
begin
end ;
insert into class(className) values('s1t64');
insert into student(stuName,stuSex,stuAge,classId)values('zhangsan',1,23,1);
update student set stuName='lishi',classId=1 where id = 1;
select * from class ;
select * from student limit 0,100 ; -- 分页查询从索引0开始查找,100条数据
附带上一本讲解的pdf哈 ,也是在网上找得,很简洁,但是可以让你少走弯路啦
SQLite 触发器的SQL语法
CREATE TRIGGER
sql-statement ::=
CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [ BEFORE | AFTER ]
database-event ON [database-name .] table-name trigger-action
sql-statement ::=
CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF
database-event ON [database-name .] view-name trigger-action
database-event ::=
DELETE | INSERT | UPDATE |
UPDATE OF column-list
trigger-action ::=
[ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN
expression ]
BEGIN
trigger-step [ trigger-step ]* END
trigger-step ::=
update-statement | insert-statement |
阅读会员限时特惠 7大会员特权立即尝鲜
delete-statement | select-statement
CREATE TRIGGER语句用于向数据库schema中添加触发器。触发器是一些在特定的数据库事件(database-event) 发生时自动进行的数据库操作(trigger-action).
触发器可由在特殊表上执行的DELETE, INSERT, UPDATE等语句触发,或UPDATE表中特定的字段时触发。
现在SQLite仅支持FOR EACH ROW触发器,不支持FOR EACH STATEMENT触发。因此可以不用明确说明FOR EACH ROW .FOR EACH ROW的意思是由trigger-steps说明的SQL语句可能在(由WHEN子句决定的)数据库插入, 更改或删除的每一行触发trigger.
WHEN子句和trigger-steps可以使用"NEW.column-name"和"OLD.column-name" 的引用形式访问正在被插入,更改或 删除的行的元素,column-name是触发器关联的表中的字段名。OLD 和 NEW 引用只在触发器与之相关的trigger-event处可用,例如:
INSERT NEW可用 UPDATE NEW和OLD均可用 DELETE OLD可用
当使用WHEN子句,trigger-steps只在WHEN子句为真的行执行。不使用WHEN时则在所有行执行。
trigger-time决定了trigger-steps执行的时间,它是相对于关联行的插入,
删除和修改而言的。
作为的一部分trigger-step的UPDATE 或 INSERT可以使用ON CONFLICT子句。
但若触发trigger的语句使用了ON CONFLICT子句,则覆盖前述的ON CONFLICT子句所定义的冲突处理方法。 关联表被撤销时触发器被自动删除。
不仅在表上,在视图上一样可以创建触发器,在CREATE TRIGGER语句中使用INSTEAD OF即可。 若视图上定义了一个或多个ON INSERT, ON DELETE, ON UPDATE触发器,则相应地对视图执行INSERT,DELETE 或UPDATE语句 不会出错,而会触发关联的触发器。视图关联的表不会被修改。(除了由触发器进行的修改操作)。
Example:
假设"customers"表存储了客户信息,"orders"表存储了订单信息,下面的触发器确保当用户改变地址时所有的 关联订单地址均进行相应改变:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name; END;
定义了该触发器后执行如下语句:
UPDATE customers SET address = ’1 Main St.’ WHERE name = ’Jack Jones’; 会使下面的语句自动执行:
UPDATE orders SET address = ’1 Main St.’ WHERE customer_name = ’Jack Jones’;
注意,目前在有INTEGER PRIMARY KEY域的表上触发器可能工作不正常。若BEFORE触发器修改了一行的 INTEGER PRIMARY KEY域,而该域将由触发该触发器的语句进行修改,则可能根本不会修改该域。 可以用PRIMARY KEY字段代替INTEGER PRIMARY KEY字段来解决上述问题。
一个特殊的SQL函数RAISE()可用于触发器程序,使用如下语法:
raise-function ::=
RAISE ( ABORT, error-message ) | RAISE ( FAIL, error-message ) | RAISE ( ROLLBACK, error-message ) | RAISE ( IGNORE )
当触发器程序执行中调用了上述前三个之一的形式时,则执行指定的ON CONFLICT进程(ABORT, FAIL或者ROLLBACK) 且终止当前查询,返回一个SQLITE_CONSTRAINT错误并说明错误信息。