例如:
第一个表为图书信息表,第二个表为图书借阅表,两张表前七个字段可以不需要填写
exam_book_info_qc
exam_book_borrow_qc
对于new,after的定义
new在before触发器中赋值,取值;在after触发器中取值。
new是新插入的数据,old是原来的数据
insert只会有new,代表着要插入的新记录
delete只会有old,代表着要删除的记录
下来我们看一下主表中图书数量book_num和借阅数量borrow_num在这几种情况下的变化
对于子表信息增加以后主表相应的减少
create or replace trigger condelete
after delete
on exam_book_borrow_qc
for each row
declare
– local variables here
begin
update exam_book_info_qc set book_num=book_num+:old.borrow_num where id=:old.book_code;
end condelete;
对于子表信息修改以后主表信息相应的减少
create or replace trigger conedit
after update
on exam_book_borrow_qc
for each row
declare
– local variables here
begin
update exam_book_info_qc set book_num=book_num+:old.borrow_num-:new.borrow_num where id=:new.book_code;
end conedit;
对于子表信息相应的减少以后主表信息相应的增加
create or replace trigger condelete
after delete
on exam_book_borrow_qc
for each row
declare
– local variables here
begin
update exam_book_info_qc set book_num=book_num+:old.borrow_num where id=:old.book_code;
end condelete;