Trigger一(DML trigger一 增刪改、子事務、自增主鍵) (PL/SQL 十八)

※Trigger就是執行了某件事後，會自動做某件事的動作

※DML trigger針對增刪改的之前或之後，自動執行一些程式，分成表級和行級

※表級DML trigger

※針對表的trigger

CREATE OR REPLACE TRIGGER trigger_test
    BEFORE INSERT OR DELETE ON DEPT
DECLARE
    xxx_min NUMBER;
    xxx_week NUMBER;
BEGIN
    SELECT 
        TO_NUMBER(TRIM(TO_CHAR(SYSDATE, 'D'))), 
        TO_NUMBER(TRIM(TO_CHAR(SYSDATE, 'HH24MI')))
    INTO xxx_week, xxx_min FROM DUAL;
    
    IF xxx_week = 1 OR xxx_week = 7 THEN
        RAISE_APPLICATION_ERROR(-20000, '星期六日不允許更動DEPT表');
    ELSIF xxx_min < 900 OR xxx_min > 1830 THEN
        RAISE_APPLICATION_ERROR(-20001, '非上班時間不允許更動DEPT表');
    END IF;
END;

※執行「INSERT INTO DEPT VALUES(80, 'a', 'b');」因為是BEFORE，所以會在執行insert之前就去判斷能不能新增

※1代表星期日，7代表星期六

※針對表裡的欄位trigger

CREATE OR REPLACE TRIGGER trigger_test
BEFORE UPDATE OF LOC ON DEPT
DECLARE
    xxx_hour NUMBER;
BEGIN
    SELECT TO_NUMBER(TRIM(TO_CHAR(SYSDATE, 'HH24')))
    INTO xxx_hour FROM DUAL;
    
    IF xxx_hour >= 12 THEN
        RAISE_APPLICATION_ERROR(-20001, '12點之後不允許修改DEPT表的LOC欄位');
    END IF;
END;

※因為是針對DEPT表的LOC欄位，所以「UPDATE DEPT SET DNAME = 'xxx' WHERE DEPTNO = 80;」可以成功修改，但「UPDATE DEPT SET LOC = 'ooo' WHERE DEPTNO = 80;」如果過12點就會跳出trigger

※子事務

※trigger裡也可以有事務

※新增一張表做練習

CREATE TABLE after_emp_sal(
    EMPNO NUMBER(4),
    ENMAE VARCHAR2(10),
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    AFTER_SAL NUMBER(7,2),
    CONSTRAINT AFTER_PK PRIMARY KEY(EMPNO)
);

※這張表是在做EMP的SAL(薪水)和COMM(佣金)欄位，加總後判斷多少錢，然後依加薪的程度有所不同，最後新增到這張表

※先不開啟子事務

CREATE OR REPLACE TRIGGER add_sal
    AFTER INSERT OR UPDATE OF SAL, COMM ON EMP
DECLARE
    -- PRAGMA AUTONOMOUS_TRANSACTION;
    CURSOR xxx_cur IS SELECT * FROM EMP;
    xxx_emp EMP%ROWTYPE;
    xxx_sal EMP.SAL%TYPE;
    xxx_after_sal after_emp_sal.after_sal%type;
BEGIN
    FOR xxx_emp IN xxx_cur LOOP
        xxx_sal := NVL(xxx_emp.sal, 0) + NVL(xxx_emp.comm, 0);
    
        IF xxx_sal < 2000 THEN
            xxx_after_sal := xxx_sal * 1.3;
        ELSIF xxx_sal <= 3000 THEN
            xxx_after_sal := xxx_sal * 1.2;
        ELSIF xxx_sal <= 4000 THEN
            xxx_after_sal := xxx_sal * 1.1;
        END IF;
    
        INSERT INTO after_emp_sal values(
            xxx_emp.empno,
            xxx_emp.ename,
            xxx_emp.sal,
            xxx_emp.comm,
            xxx_after_sal
        );
    END LOOP;
    -- COMMIT;
END;

※執行「INSERT INTO EMP (empno, ename, sal, comm) VALUES(8888, 'xxx', 2400, 200);」之後就會發現after_emp_sal已經新增了，也包含了8888這筆，但如果開啟另外一個session，就會發現是空的，因為沒有COMMIT，所以COMMIT或ROLLBACK後，就會全看的見或全看不見

※如果只開啟trigger的COMMIT，就會出ORA-04092:cannot COMMIT in a trigger錯誤訊息，說trigger裡，不能有COMMIT，這時再開啟PRAGMA那行的子事務就可以了

※兩者最大的差異在8888新增的這一筆，如果是用子事務，就不會包括

※如果有子事務，然後把INSERT那筆rollback後，after_emp_sal還是有資料(當然沒有8888，要等下次再做一次才有)

※行級DML trigger

只有加上FOR EACH ROW才表示行級DML trigger，也才可以使用:new、:old

官網有寫:new和:old的表，新增的:old和刪除的:new都是null

※新增測試

CREATE OR REPLACE TRIGGER line_trig_add
    BEFORE INSERT ON DEPT FOR EACH ROW
DECLARE
    xxx_count NUMBER;
    xxx_deptno NUMBER;
BEGIN
    SELECT COUNT(*) INTO xxx_count FROM DEPT 
    WHERE :new.loc IN(
        SELECT LOC FROM DEPT
    );
    
    IF xxx_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20000, '公司沒有這個地點！');
    ELSE
        xxx_deptno := :new.deptno;
        IF SUBSTR(xxx_deptno, LENGTH(xxx_deptno), 1) != 0 OR xxx_deptno <= 0 THEN
            RAISE_APPLICATION_ERROR(-20001, '部門號必需是10的倍數！');
        END IF;
    END IF;
END;

※INSERT INTO DEPT VALUES(80, 'xxx', 'ooo');
INSERT INTO DEPT VALUES(62, 'xxx', 'CHICAGO');
以上兩條分別出現不一樣的trigger

※修改測試

CREATE OR REPLACE TRIGGER line_trig_update
    BEFORE UPDATE OF SAL ON EMP FOR EACH ROW
DECLARE
BEGIN
    IF ABS(:new.sal - :old.sal) >= 1000 THEN
        RAISE_APPLICATION_ERROR(-20000, '加薪太過！');
    END IF;
END;

※如果加薪幅度超過1000就會跳出trigger

※刪除測試

CREATE OR REPLACE TRIGGER line_trig_delete
    BEFORE DELETE ON DEPT FOR EACH ROW
DECLARE
BEGIN
    IF :old.deptno = 10 THEN
        RAISE_APPLICATION_ERROR(-20000, '無法刪除10部門');
    END IF;
END;

※只有刪除10部門才會跳出trigger

※注意事項

1.宣告ddd DEPT%ROWTYPE;
然後使用ddd := :new;是不可以的，雖然:new或:old很像%ROWTYPE，但是是不可以的

2.宣告ooo NUMBER;
然後使用ooo := :old.sal;這也是不行的，原始值是不能被修改的，但:new是可以的，如上面的新增就有用到

3.上面的程式碼是我自己run成功的結果貼上去的，有一次我自己打開PL/SQL Developer，然後打開一個新的Test Window，然後複製我成功的程式碼，居然出現「ORA-01036: illegal variable name/number」，debug後發現是:new、:old的錯，也就是它認為我不能有「:」開頭的變數，見鬼了，後來我try了好久，發現有這種「:」開頭的，可以先把它刪除，變成new和old然後編譯就會過了，但當然是不能run，所以我去trigger按右鍵Edit或在command輸入edit trigger名稱就會跳出來，然後再將「:」加上去，最後編譯就可以了

4.編譯完trigger後，就不能更改別的表了，如ON DEPT，就不能換成別的，否則會出現「ORA-04095:trigger 'trigger名稱' already exists on another table, cannot replace it」的錯，只能先Drop這個trigger或重新寫一支trigger，名稱不一樣就ok

※自增主鍵

Oracle12c有提供和其他家資料庫一樣，不用再做Sequence，以下先說不是12c時的做法

※建立兩張表做練習

CREATE SEQUENCE ttt_sequence;
    
CREATE TABLE trig_test_table(
    id NUMBER,
    name VARCHAR2(10),
    CONSTRAINT ttt_pk PRIMARY KEY(id)
);
    
CREATE TABLE trig_test_table_copy AS SELECT * FROM trig_test_table WHERE 1=0;

※where 1=0是因為假設trig_test_table有10筆資料，而因為where條件不成立，所以只會複製表的結構而已

※trigger

CREATE OR REPLACE TRIGGER trigger_test
    BEFORE INSERT ON trig_test_table_copy FOR EACH ROW
DECLARE
    xxx_ttt trig_test_table%ROWTYPE;
BEGIN
    DELETE FROM trig_test_table_copy;
    SELECT ttt_sequence.NEXTVAL INTO :new.id FROM DUAL;
    
    xxx_ttt.id := :new.id;
    xxx_ttt.name := :new.name;
    
    INSERT INTO trig_test_table VALUES xxx_ttt;
END;

※也可以不用%ROWTYPE，直接用一般的INSERT，如下：

CREATE OR REPLACE TRIGGER trigger_test
    BEFORE INSERT ON trig_test_table_copy FOR EACH ROW
BEGIN
    DELETE FROM trig_test_table_copy;
    INSERT INTO VALUES(ttt_sequence.NEXTVAL, :new.name);
END;

※因為不用宣告變數，所以連DECLARE也不要了，比較簡潔

※insert into trig_test_table_copy(name) values('xxx');就可以看到trig_test_table新增進去了

※多一張表是因為如果沒有copy那張表，那第一次insert時觸發了Trigger，而Trigger裡面因為沒有copy那張表，是自己的表，那又觸發了Trigger，所以會變成無限迴圈

※Oracle12c的自增主鍵

CREATE TABLE trig_test_table(
    -- id NUMBER,
    id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
    name VARCHAR2(10),
    CONSTRAINT ttt_pk PRIMARY KEY(id)
);

※註解的部分變成下面那一行，其實也就是將原本的SEQUENCE寫在裡面，和table挷起來而已，圓括號裡的參數可以不打，參數用法都和SEQUENCE一樣

※這時連trigger都不用了