级联修改.txt

来自「介绍了在oracle里」· 文本 代码 · 共 34 行

--定义级联删除的外码约束
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;