PL/SQL替代(INSTEAD OF)触发器、嵌套表替代触发器

---

替代(INSTEAD OF)触发器

DML触发器只能应用在表上。而替代触发器只能定义在视图上。当对一个不能进行修改的视图进行数据的修改时，或者要修改视图中的某个嵌套表时，可以使用替代触发器。

替代触发器的作用

替代原来的数据操作语句的执行，更改为使用在触发器中定义的语句来执行数据操作。

一些简单的单表视图，可以直接INSERT、UPDATE、DELETE，但如果要对复杂的视图进INSERT、UPDATE、DELETE，可以通过替代触发器，将这些DML语句对视图的更改替换为对基表的 DML 操作。需要注意的是，视图的更新操作也要遵循基表自身的约束机制。

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF [INSERT | UPDATE | UPDATE OF 列名称 [,列名称,...] | DELETE] 
ON 视图名称
[REFERENCING {OLD AS old} | {NEW AS new} | {PARENT AS parent}]
FOR EACH ROW    				   -- 一定是行级触发器
[FOLLOWS 其它触发器名]              -- 多个触发器执行的 前后顺序
plsql_block | call_procedure_statement

当建立替代触发器时，视图没有指定WITH CHECK OPTION选项。

另外，在创建替代触发器时，没有BEFORE和AFTER关键字，INSTEAD OF总是等同于使用AFTER关键字的行级触发器，它会为每个受到影响的行触发一次。并且，在替代触发器中用户总是不能修改其中的:OLD，:NEW值。

--创建视图emp_dept视图
CREATE OR REPLACE VIEW scott.emp_dept (empno, ename,job,mgr,hiredate,sal,comm,deptno,dname,loc)
AS
	SELECT emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal,
		   emp.comm, emp.deptno, dept.dname, dept.loc
	FROM dept, emp
	WHERE dept.deptno = emp.deptno;

CREATE OR REPLACE TRIGGER t_emp_dept
INSTEAD OF UPDATE OR INSERT OR DELETE ON emp_dept   
REFERENCING NEW AS n OLD AS o      --指定谓词别名
FOR EACH ROW                       --行级触发器
DECLARE
   v_counter   INT;                --计数器统计变量
BEGIN
   SELECT COUNT (*) INTO v_counter FROM dept WHERE deptno = :o.deptno;  --判断在dept表中是否存在相应的记录   
   IF v_counter > 0 THEN            --如果存在，则更新dept表  
      CASE 
      WHEN UPDATING THEN
         UPDATE dept SET dname=:n.dname,loc=:n.loc WHERE deptno=:o.deptno;
      WHEN INSERTING THEN
         INSERT INTO dept VALUES (:n.deptno, :n.dname, :n.loc); 
      WHEN DELETING THEN
         DELETE FROM dept WHERE deptno=:o.deptno;     --删除dept表      
      END CASE;
   END IF;
   SELECT COUNT (*) INTO v_counter FROM emp WHERE empno = :n.empno;    --判断emp表中是否存在员工记录
   IF v_counter > 0  THEN                  --如果存在，则更新emp表
      CASE 
      WHEN UPDATING THEN
         UPDATE emp SET ename=:n.ename,job=:n.job,mgr=:n.mgr, hiredate=:n.hiredate,sal=:n.sal,
                   comm=:n.comm, deptno=:n.deptno WHERE empno=:o.empno;    
      WHEN INSERTING THEN
         INSERT INTO emp(empno, ename, job, mgr, hiredate, sal,comm, deptno)
         VALUES (:n.empno, :n.ename, :n.job, :n.mgr, :n.hiredate, :n.sal, :n.comm, :n.deptno);
      WHEN DELETING THEN
         DELETE FROM emp WHERE empno=:o.empno;   
      END CASE;       
   END IF;
END; 

示例一、创建一个insert替代触发器用于执行复杂视图添加操作

create or replace trigger view_insert_tigger
instead of insert on v_emp20  
for each row
declare
    v_empCount NUMBER;
    v_deptCount NUMBER;
begin
	--判断要增加的员工是否存在
	select count(empno) into v_empCount from emp where empno =: NEW.empno;
	--判断要部门是否存在
	select count(deptno) into v_deptCount from dept where deptno = :new.deptno;
	--如果员工不存在
	if v_empCount = 0 then
		insert into emp(empno, ename, job, sal, deptno)
		values(:new.empno, :new.ename, :new.job, :new.sal, :new.deptno);
	end if;
	--如果部门不存在
	if v_deptCount = 0 then 
		insert into dept(deptno, dname, loc)
		values(:new.deptno, :new.dname, :new.loc);
	end if;
end view_insert_tigger;

--添加数据
begin 
	insert into v_emp20(empno,ename,job,sal,deptno,dname,loc)
	values(7777,'张三丰','CLERK',800,77,'活动部','深圳');
end;

示例二、创建一个update替代触发器用于执行视图更新操作

create or replace trigger view_update_tigger
instead of update on v_emp20  
for each row
begin
	update emp set ename = :new.ename, job = :new.job, sal = :new.sal where empno = :NEW.empno;
	update dept set dname = :new.dname, loc = :new.loc where deptno = :new.deptno;
end view_update_tigger;

--更新视图
begin
	update v_emp20 set ename = '任我行', sal = 2000, dname = '魔教' where empno = 7777;
end;

示例三、创建一个DELETE替代触发器用于执行视图的删除操作

create or replace trigger view_delete_tigger
instead of delete on v_emp20  
for each row
declare
	v_empCount NUMBER;
begin
	--判断员工是否存在
	select count(empno) into v_empCount from emp where empno = :old.empno;
	--如果员工存在
	if v_empCount > 0 then
		delete from emp where empno = :old.empno;
	end if;
end view_delete_tigger;

--执行删除
begin
	delete from v_emp20 where empno = 7777;
end;

嵌套表替代触发器

下面的代码创建了一个具有嵌套表数据的视图：

--创建用于嵌套表的对象类型
CREATE OR REPLACE TYPE emp_obj AS OBJECT(
   empno NUMBER(4),
   ename VARCHAR2(10),
   job VARCHAR2(10),
   mgr NUMBER(4),
   hiredate DATE,
   sal NUMBER(7,2),
   comm NUMBER(7,2),
   deptno NUMBER(2)
);

--创建嵌套表类型
CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_obj;

--创建嵌套表视图，MULTISET必须与CAST一起使用
CREATE OR REPLACE VIEW dept_emp_view AS
   SELECT deptno,dname,loc,
   CAST(MULTISET(SELECT * FROM emp WHERE deptno=dept.deptno) AS emp_tab_type) emplst
   FROM dept;

现在可以通过这条语句来理解CAST和MULTISET这个语法：

• MULTISET关键字用于告诉ORACLE：这个子查询返回的是多行（select列表中的子查询不加multiset限制为返回一行）。
• CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。语法：CAST (expression AS data_type)
  • CAST是一个通用的例程，并不仅限于在集合中使用。例如，如果想从EMP中将EMPNO列获取为VARCHAR2(20)而不是NUMBER(4)，可以使用下面的查询：SELECT
    CAST(EMPNO AS VARCHAR2(20)) E FROM EMP。

可以看到，代码中使用CAST AS语句和MULTISET将对emp表特定员工编号的查询转换成了emp_tab_typ类型的嵌套表，现在视图dept_emp_view具有了一个嵌套表。

为了在嵌套表视图中执行DML操作，我们来创建一个嵌套表替代触发器：

CREATE OR REPLACE TRIGGER dept_emp_innerview
   INSTEAD OF INSERT
   --*********注意这*********
   ON NESTED TABLE emplst OF dept_emp_view    --创建嵌套表替代触发器
BEGIN
	--插入子表记录
   INSERT INTO emp (deptno, empno, ename, job, mgr, hiredate, sal, comm)
   VALUES (:PARENT.deptno, :NEW.empno, :NEW.ename, :NEW.job, :NEW.mgr,    --这里使用了parent谓词，:parent.deptno获取嵌套表父行的deptno。
           :NEW.hiredate, :NEW.sal, :NEW.comm);
END;

这样，我们就可以用以下DML向dept_emp_view视图的嵌套表插入一行记录：

INSERT INTO TABLE (SELECT emplst FROM dept_emp_view WHERE deptno = 10)
VALUES (8003, '四爷', '皇上', NULL, SYSDATE, 5000, 500, 10);

嵌套表替代触发器 与 普通的替代触发器 的创建方式基本相同，但是有如下两个基本的区别：

• 嵌套表使用 ON NESTED TABLE 嵌套表列 OF 嵌套表视图 这种定义方式
• PARENT谓词在嵌套表替代触发器中具有值，指向包含嵌套表的视图的父项记录。