替代(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是一个通用的例程,并不仅限于在集合中使用。例如,如果想从EMP中将EMPNO列获取为VARCHAR2(20)而不是NUMBER(4),可以使用下面的查询:SELECT
可以看到,代码中使用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谓词在嵌套表替代触发器中具有值,指向包含嵌套表的视图的父项记录。