编写触发器,功能是:当删除院系表(dept)中的一条记录时,检查学生表(stu)中该院系对应的学生记录是否存在,如果不存在,则可以在院系表中直接删除;如果存在,则必须先删除该院系对应的所有学生记录,然后再删除院系表中的记录。 create or replace trigger ontest
before delete on dept
for each row
begin
delete from stu where deptid=:OLD.deptid;
end;
不加入条件的时候这样可行
加入判断条件就不可行了
create or replace trigger ontest
before delete on dept
for each row
declare num number(2);
begin
num:=0
select count(*) into num from stu where deptid=:OLD.deptid;
if num>0 then
delete from stu where deptid=:OLD.deptid;
end if;
end;求救啊
before delete on dept
for each row
begin
delete from stu where deptid=:OLD.deptid;
end;
不加入条件的时候这样可行
加入判断条件就不可行了
create or replace trigger ontest
before delete on dept
for each row
declare num number(2);
begin
num:=0
select count(*) into num from stu where deptid=:OLD.deptid;
if num>0 then
delete from stu where deptid=:OLD.deptid;
end if;
end;求救啊
create or replace trigger ontest
before delete on dept
for each row
declare
num number default 0;
begin
num:=0;
select count(*) into num from stu,dept where stu.deptid=dept.deptid;
if num>0 then
delete from stu where deptid=:OLD.deptid;
end if;
end;
/器已创建 delete from dept where deptid=11;
te from dept where deptid=11
*
R 位于第 1 行:
04091: 表 HR.DEPT 发生了变化,触发器/函数不能读
06512: 在"HR.ONTEST", line 5
04088: 触发器 'HR.ONTEST' 执行过程中出错
这个就是基于本表的触发器,触发之后修改本表的,所谓的变异表问题。你可以建立一个同构表,变相操作本表。
create or replace trigger ontest
before delete on dept
for each row
--declare num number(2);
begin
-- num:=0
--select count(*) into num from stu where deptid=:OLD.deptid;
-- if num>0 then
delete from stu where exits(select 1 from stu where deptid=:OLD.deptid);
end;
5/30 PL/SQL: ORA-00936: 缺少表达式