数据库 表的约束有很多,其中外键是可以很好的表与表之间的关联的约束,然而在删除/修改是,用外键却比较麻烦,所以采用触发器替代外键的方案可以更好的解决这个问题。
/***创建班级表***/ 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; insert into class(className) values('aaa'); insert into class(className) values('bbb'); insert into class(className) values('vvv');
insert into student values(1,'first',1,20,4);/**插入失败,没有classId=4**/ insert into student values(1,2);
/***创建更新触发器 (更新学生时要触发更新触发器去判断是否存在更新班级,存在更新成功,反之更新失败)**/ create trigger fk_Update before update on student for each row begin select raise(rollback,'还没有该班级') where (select id from class where id = old.classId)is null; /**在update中,可以使用new/old;在new/old关键字后面的字段名是,on表里面的字段而不是当前查询class表的字段**/ end; update student set classId=4 where classId=2; /**Error: 还没有该班级**/ update student set classId=1 where classId=2;
/***创建删除触发器 (删除班级时,首先根据班级编号删除该班级学生)**/ create trigger fk_Delete before delete on class for each row begin delete from student where classId = OLD.id; end ;
delete from class where id=1;
/**删除触发 运行结果**/
sqlite> select * from class; 1|aaa 2|bbb 3|vvv sqlite> select * from student; 1|first|1|20|1 sqlite> delete from class where id=1; /**删除 class 表id=1 的记录,这样会同时删除student中classId=1的记录**/ sqlite> select * from class; 2|bbb 3|vvv sqlite> select * from student; /**会把student中classId=1 的数据删除**/ sqlite>
drop trigger fk_Delete;/**删除触发器**/ /***创建删除触发器 (删除班级时,首先根据班级编号删除该班级学生)**/ create trigger fk_Delete before delete on class for each row begin /** 第一种: 在删除class时,直接删除student相关的记录**/ /** delete from student where classId = OLD.id;**/ /**第二种:在删除class时,默认修改student相关的记录为默认值 **/ update student set classId=0 where classId = OLD.id; end ;
insert into student values(1,2); delete from class where id=2;
/**运行结果**/
sqlite> insert into student values(1,2); sqlite> select * from student; 1|first|1|20|2 sqlite> delete from class where id=2; sqlite> select * from student; 1|first|1|20|0 sqlite> select * from class; 3|vvv sqlite>
在触发器,可以按照我以上的步骤,一步步做下来,都可以实现,具体原理可以在参考例子的同时,多看sqlite官方文档,对应英文不懂的可以看看
http://www.cnblogs.com/txw1958/archive/2012/11/16/sqlite-basic.html 写的还算详细 (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|