数据库技术学习 https://www.itkc8.com

INSTEAD OF触发器

对于简单视图，可以直接执行INSERT，UPDATE和DELETE操作
但是对于复杂视图，不允许直接执行INSERT，UPDATE和DELETE操作。
为了在具有以上情况的复杂视图上执行DML操作需要征用触发器来完成

--创建复杂视图

复制代码

CREATE OR REPLACE VIEW v_emp20AS
SELECT e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno;
--查看视图
SELECT * FROM user_views;

复制代码

INSTEAD OF触发器可以实现更新视图时多个数据表一起更新的问题

复制代码

instead-of触发器创建语法
CREATE [OR REPLACE] TRIGGER 触发器名称INSTEAD OF [INSERT | UPDATE | UPDATE OF 列名称 [,列名称,...] | DELETE] ON 视图名称[FOR EACH ROW][WHEN 触发条件][DECLARE][程序声明部分 ;]BEGIN程序代码部分 ;END [触发器名称] ;

复制代码

替代触发器创建时不需要使用BEFORE或者AFTER，而将其替换为INSTEAD OF，同时操作的对象也有表替换为视图

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

复制代码

create or replace trigger view_insert_tiggerinstead of insert on v_emp20  for each row
declarev_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 THENINSERT INTO emp(empno,ename,job,sal,deptno)VALUES(:new.empno,:new.ename,:new.job,:new.sal,:new.deptno);END IF;--如果部门不存在IF v_deptCount=0 THENINSERT INTO dept(deptno,dname,loc)VALUES(:new.deptno,:new.dname,:new.loc);END IF;
end view_insert_tigger;
--添加数据
INSERT INTO v_emp20(empno,ename,job,sal,deptno,dname,loc)
VALUES(7777,'张三丰','CLERK',800,77,'活动部','深圳');

复制代码

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

复制代码

create or replace trigger view_update_tiggerINSTEAD OF update on v_emp20  for each row
declarebeginUPDATE 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;
UPDATE v_emp20 SET ename='任我行',sal=2000,dname='魔教' WHERE empno=7777;
COMMIT;
--查询
SELECT * FROM v_emp20;

复制代码

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

复制代码

create or replace trigger view_delete_tiggerinstead of delete on v_emp20  for each row
declarev_empCount          NUMBER;
BEGIN--判断员工是否存在SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:old.empno;--如果员工存在IF v_empCount>0 THENDELETE FROM emp WHERE empno=:old.empno;END IF;end view_delete_tigger;--执行删除
DELETE FROM v_emp20 WHERE empno=7777;
COMMIT;
--查询
SELECT * FROM v_emp20;
SELECT * FROM emp;

复制代码

示例四、将以上三个合为一个

复制代码

create or replace trigger view20emp_triggerinstead of INSERT OR UPDATE OR DELETE on v_emp20  for each row
declarev_empCount            NUMBER;v_deptCount            NUMBER;
BEGINIF inserting THEN--判断要增加的员工是否存在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 THENINSERT INTO dept(deptno,dname,loc)VALUES(:new.deptno,:new.dname,:new.loc);END IF;ELSIF updating THENUPDATE 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;ELSIF deleting THEN--判断员工是否存在SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:old.empno;--如果员工存在IF v_empCount>0 THENDELETE FROM emp WHERE empno=:old.empno;END IF;ELSENULL;END IF;end view20emp_trigger;

复制代码

执行增加、修改、删除

复制代码

--添加数据
INSERT INTO v_emp20(empno,ename,job,sal,deptno,dname,loc)
VALUES(7777,'张三丰','CLERK',800,77,'活动部','深圳');
--查询
SELECT * FROM v_emp20;
UPDATE v_emp20 SET ename='任我行',sal=2000,dname='魔教' WHERE empno=7777;
COMMIT;
--查询
SELECT * FROM v_emp20;
--执行删除
DELETE FROM v_emp20 WHERE empno=7777;
COMMIT;
--查询
SELECT * FROM v_emp20;
SELECT * FROM emp;

复制代码

当视图中包含以下结构之一，就表示为不可更新的视图,都不允许直接执行DML操作

1）具有集合操作符（UNION，UNION ALL，INTERSECT，MINUS）；
2）具有分组函数（MIN,MAX,SUM,AVG,COUNT等）统计函数；
3）具有GROUP BY，CONNECT BY或START WITH等子句,HAVING 子句；
4）具有DISTINCT关键字；
5）具有连接查询(集合运算连接)

6)CASE 或者DECODE 语句