※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都不用了
