--定义级联删除的外码约束
alter table sc add constraint ref_sno foreign key(sno) references student(sno) on delete cascade;
--定义带级联删除的延迟执行的外码约束，实现级联修改
alter table sc add constraint ref_sno foreign key(sno) references student(sno) on delete cascade deferrable;
set constraint ref_sno deferred;
--测试延迟外码是否定义成功
update student set sno='055' where sno='001';
update sc set sno='055' where sno='001';
commit;
--使用触发器完成级联修改
create or replace trigger tr_update_student
after update on student
for each row
begin
update sc set sno=:new.sno where sno=:old.sno;
end tr_update_student;
--Raise_Application_Error实例
create or replace trigger tr_update_sc
before update on sc
for each row
declare
-- local variables here
begin
if :new.grade<:old.grade then
raise_application_error(-20000,'Eorr!!!');
end if;
end tr_update_sc;
评论0