select * into row_dept from dept where deptno = :new.deptno;
这行如果没有记录会报错if sql%notfound then
部分可写成异常处理:
exception
when no_data_found then
.....
这行如果没有记录会报错if sql%notfound then
部分可写成异常处理:
exception
when no_data_found then
.....
楼主Trigger可调整为create or replace trigger tri_insert_view
instead of insert on view_emp_dept
for each row
declare
row_dept dept%rowtype;
begin
select * into row_dept from dept where deptno = :new.deptno;
insert into emp
(empno, ename, deptno, job, hiredate)
values
(:new.empno, :new.ename, :new.deptno, :new.job, :new.hiredate);
exception
when NO_DATA_FOUND then
insert into dept (deptno, dname) values (:new.deptno, :new.dname);
end;
--在未找到数据的异常处理中insert
select count(1) into v_cnt from dept where deptno = :new.deptno;
判断v_cnt的值是否大于0来判断其是否已经有数据。
此处不会出现NO_DATA_FOUND因为聚合函数始终会返回一条记录。