Trigger四(DML trigger四 view) (PL/SQL 二十一)

針對view的trigger，主要是因為view有多表的時候不能增刪改，所以使用view trigger可以達到，但如果有彙總函數、CASE、DECODE、GROUP BY、DISTINCT還是不行，稍微想一下就知道為什麼不行了
官網連結

※新增一個view來練習

CREATE OR REPLACE VIEW xxx_view AS
SELECT
    e.EMPNO, e.ENAME, d.DEPTNO, d.DNAME
FROM EMP e, DEPT d
WHERE
    e.DEPTNO = d.DEPTNO
AND d.DEPTNO = 30;

※Oracle11g和12c預設是沒有權限可以新增view的，用有權限的帳號進入後，打上GRANT CREATE VIEW TO 使用者名稱;才有權限

※如果記不起來可以用視窗的方式來新增

 使用畫面可以輕鬆完成，完成後編譯，如下圖可以看見

※一執行「INSERT INTO xxx_view VALUES(7777, 'aaa', 50, 'xxx');」
ORA-01776: cannot modify more than one base table through a join view就會出錯，所以才要有view trigger

※新增

CREATE OR REPLACE TRIGGER update_view_trigger
    INSTEAD OF INSERT ON xxx_view 
    FOR EACH ROW
DECLARE
    xxx_emp_count NUMBER;
    xxx_dept_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO xxx_emp_count FROM EMP
    WHERE empno = :new.empno;
    
    SELECT COUNT(*) INTO xxx_dept_count FROM DEPT
    WHERE deptno = :new.deptno;
    
    IF xxx_emp_count = 0 THEN
        INSERT INTO emp(empno, ename) VALUES(:new.empno, :new.ename);
    END IF;
    
    IF xxx_dept_count = 0 THEN
        INSERT INTO dept(deptno, dname) VALUES(:new.deptno, :new.dname);
    END IF;
END update_view_trigger;

※再次執行「INSERT INTO xxx_view VALUES(7777, 'aaa', 50, 'xxx');」，看xxx_view、emp、dept可以發現成功了

※其實很多都有畫面操作，在Trigger按右鍵新增，如下

※修改

CREATE OR REPLACE TRIGGER update_view_trigger
    INSTEAD OF UPDATE ON xxx_view 
    FOR EACH ROW
BEGIN
    UPDATE emp SET ename = :new.ename WHERE empno = :new.empno;
    UPDATE dept SET dname = :new.dname WHERE deptno = :new.deptno;
END update_view_trigger;

※執行「UPDATE xxx_view SET ename = 'bbb', dname='ccc' WHERE empno = 7844;」

※刪除

CREATE OR REPLACE TRIGGER update_view_trigger
    INSTEAD OF DELETE ON xxx_view 
    FOR EACH ROW
DECLARE
    xxx_emp_count NUMBER;
BEGIN
    DELETE FROM emp WHERE empno = :old.empno;
    
    SELECT COUNT(*) INTO xxx_emp_count FROM EMP
    WHERE deptno = :old.deptno;
    
    IF xxx_emp_count = 0 THEN
        DELETE FROM dept WHERE deptno = :old.deptno;
    END IF;
END update_view_trigger;

※執行「DELETE FROM xxx_view WHERE deptno = 30;」

※增刪改

也就是將剛剛的新增、修改、刪除合併成一支

CREATE OR REPLACE TRIGGER update_view_trigger
    INSTEAD OF INSERT OR UPDATE OR DELETE ON xxx_view 
    FOR EACH ROW
DECLARE
    xxx_emp_count NUMBER;
    xxx_dept_count NUMBER;
BEGIN
    IF INSERTING THEN
        SELECT COUNT(*) INTO xxx_emp_count FROM EMP
        WHERE empno = :new.empno;
    
        SELECT COUNT(*) INTO xxx_dept_count FROM DEPT
        WHERE deptno = :new.deptno;
    
        IF xxx_dept_count = 0 THEN
            INSERT INTO dept(deptno, dname) VALUES(:new.deptno, :new.dname);
        END IF;
    
        IF xxx_emp_count = 0 THEN
            INSERT INTO emp(empno, ename) VALUES(:new.empno, :new.ename);
        END IF;
    ELSIF UPDATING THEN
        UPDATE emp SET ename = :new.ename WHERE empno = :new.empno;
        UPDATE dept SET dname = :new.dname WHERE deptno = :new.deptno;
    ELSIF DELETING THEN
        DELETE FROM emp WHERE empno = :old.empno;
    
        SELECT COUNT(*) INTO xxx_emp_count FROM EMP
        WHERE deptno = :old.deptno;
    
        IF xxx_emp_count = 0 THEN
            DELETE FROM dept WHERE deptno = :old.deptno;
        END IF;
    END IF;
END update_view_trigger;

※view和巢狀表

※事前準備

DROP TYPE xxx_nested;
DROP TYPE xxx_type;
CREATE OR REPLACE TYPE xxx_type IS OBJECT(
    pid NUMBER,
    pname VARCHAR2(20),
    pdate DATE
);
    
-- 新增巢狀表，型態是自定的，叫xxx_type
CREATE OR REPLACE TYPE xxx_nested IS TABLE OF xxx_type NOT NULL;
    
DROP TABLE xxx_table PURGE;
CREATE TABLE xxx_table(
    xid NUMBER,
    xname VARCHAR(20) NOT NULL,
    ooo_nested xxx_nested,
    CONSTRAINT vt_pk PRIMARY KEY(xid)
) NESTED TABLE ooo_nested STORE AS PROJECTS_NESTED_TABLE;
    
-- 新增資料來練習
INSERT INTO xxx_table VALUES(1, 'xxx',
    xxx_nested(
        xxx_type(10, 'aaa', TO_DATE('2000-01-01', 'YYYY-MM-DD')),
        xxx_type(20, 'bbb', TO_DATE('2011-11-11', 'YYYY-MM-DD')),
        xxx_type(30, 'ccc', TO_DATE('1988-01-11', 'YYYY-MM-DD'))
    )
);
    
INSERT INTO xxx_table VALUES(2, 'ooo',
    xxx_nested(
        xxx_type(11, 'ddd', TO_DATE('1997-02-22', 'YYYY-MM-DD')),
        xxx_type(22, 'eee', TO_DATE('1996-10-11', 'YYYY-MM-DD'))
    )
);
    
-- 看結果有沒有在裡面
SELECT * FROM xxx_table;
    
-- 新增view
CREATE OR REPLACE VIEW v1 AS
SELECT xid, xname, ooo_nested
FROM xxx_table
WHERE xid = 1;

※增刪改

INSERT INTO TABLE(
    SELECT ooo_nested FROM v1
)VALUES(3, 'fff', TO_DATE('1999-09-09', 'YYYY-MM-DD'));
    
UPDATE TABLE(SELECT ooo_nested FROM v1)xxx
set value(xxx) = xxx_type(
    10, 'fff', TO_DATE('2000-01-01', 'YYYY-MM-DD')
)WHERE xxx.pid = 10;
    
DELETE FROM TABLE(
    SELECT ooo_nested FROM v1
)xxx
WHERE xxx.pid=10;

※執行就會出現「ORA-25015: cannot perform DML on this nested table view column」的錯，雖然和前面的錯誤訊息不同，但反正就是會錯

※view trigger

CREATE OR REPLACE TRIGGER nested_trigger
    INSTEAD OF INSERT OR UPDATE OR DELETE
    ON NESTED TABLE ooo_nested OF v1
-- DECLARE
BEGIN
    IF INSERTING THEN
        INSERT INTO TABLE(
            SELECT ooo_nested FROM xxx_table WHERE xid = :parent.xid
        )VALUES(:new.pid, :new.pname, :new.pdate);
    
    ELSIF UPDATING THEN
        UPDATE TABLE(
            SELECT ooo_nested FROM xxx_table WHERE xid = :parent.xid
        )xxx
        SET VALUE(xxx) = xxx_type(
            :new.pid, :new.pname, :new.pdate
        )WHERE xxx.pid = :old.pid;
    
    ELSIF DELETING THEN
        DELETE FROM TABLE(
            SELECT ooo_nested FROM xxx_table WHERE xid = :parent.xid
        )xxx
        WHERE xxx.pid = :old.pid;
    END IF;
END;

※:new和:old控制的是自定型態的表，也就是xxx_type； :parent控制的是有巢狀欄位的表，也就是xxx_table，可以想成在insert時給的是pid、pname、pdate，而它的爸爸自然就是xxx_table了，所以控制的是xid、xname，對照下面幾行比較清楚明白
xxx_type-->pid、pname、pdate==>:new、:old
xxx_nested -->xxx_type
xxx_table(v1)-->xid、xname、ooo_nested(xxx_nested)==>:parent

※再次執行增刪改，然後再看v1或xxx_table就會發現成功了