觸發器五(建立INSTEAD OF觸發器)(學習筆記)，instead觸發器

觸發器五(建立INSTEAD OF觸發器)(學習筆記)，instead觸發器

INSTEAD OF觸發器

對於簡單視圖，可以直接執行INSERT，UPDATE和DELETE操作
但是對於復雜視圖，不允許直接執行INSERT，UPDATE和DELETE操作。
為了在具有以上情況的復雜視圖上執行DML操作需要征用觸發器來完成

--創建復雜視圖

CREATE OR REPLACE VIEW v_emp20
 AS
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_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;
--添加數據
INSERT INTO v_emp20(empno,ename,job,sal,deptno,dname,loc)
VALUES(7777,'張三豐','CLERK',800,77,'活動部','深圳');

示例二、創建一個update替代觸發器用於執行視圖更新操作

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

示例三、創建一個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;

--執行刪除
DELETE FROM v_emp20 WHERE empno=7777;
COMMIT;
--查詢
SELECT * FROM v_emp20;
SELECT * FROM emp;

示例四、將以上三個合為一個

create or replace trigger view20emp_trigger
  instead of INSERT OR UPDATE OR DELETE on v_emp20  
  for each row
declare
  v_empCount            NUMBER;
  v_deptCount            NUMBER;
BEGIN
  IF 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 THEN
      INSERT INTO dept(deptno,dname,loc)VALUES(:new.deptno,:new.dname,:new.loc);
    END IF;
      
  ELSIF updating THEN
     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;
  
  ELSIF deleting THEN
        --判斷員工是否存在
 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;
  ELSE
    NULL;
  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 語句