Trigger三(DML trigger三 表、行級trigger) (PL/SQL 二十)

也就是將行級和表級合成一個trigger，之前的做法要寫四支trigger(before、after+行、表級)
分成四種，可看官網的解釋，稍為往下一點的Table 9-1

※基本語法

CREATE OR REPLACE TRIGGER LINE_TABLE_TRIGGER
    FOR INSERT OR UPDATE OR DELETE ON DEPT
    COMPOUND TRIGGER
    
    BEFORE STATEMENT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('表級之前');
    END BEFORE STATEMENT;
    
    AFTER STATEMENT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('表級之後');
    END AFTER STATEMENT;
    
    BEFORE EACH ROW IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('行級之前');
    END BEFORE EACH ROW;
    
    AFTER EACH ROW IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('行級之後');
    END AFTER EACH ROW;
END;

※執行「INSERT INTO DEPT VALUES (50, 'a', 'b');」可以出現結果

※結果：
表級之前
行級之前
行級之後
表級之後

※使用:new、:old

CREATE OR REPLACE TRIGGER LINE_TABLE_TRIGGER
    FOR INSERT OR UPDATE OR DELETE ON DEPT
    COMPOUND TRIGGER
    
    BEFORE EACH ROW IS
    BEGIN
        IF INSERTING THEN
            IF :new.dname IS NULL THEN
                :new.dname := 'default';
            END IF;
    
            IF :new.loc IS NULL THEN 
                :new.loc := '芝加哥';
            END IF;
        END IF;
    END BEFORE EACH ROW;
END;

※INSERT INTO DEPT(DEPTNO) VALUES(50);

※當然還是只有行級才能用

※宣告變數

CREATE OR REPLACE TRIGGER xxx
    FOR INSERT OR UPDATE OR DELETE ON EMP
    COMPOUND TRIGGER
    
    BEFORE EACH ROW IS
        xxx_sal emp.sal%TYPE;
    BEGIN
        IF INSERTING OR UPDATING THEN
            :new.ename := UPPER(:new.ename);
        END IF;
    
        IF INSERTING THEN
            SELECT AVG(sal) INTO xxx_sal FROM EMP;
            IF :new.sal > xxx_sal THEN
                &RAISE_APPLICATION_ERROR(-20000, '不可高於平均工資');
            END IF;
        END IF;
    END BEFORE EACH ROW;
END;

※INSERT INTO EMP(EMPNO, ENAME, SAL) VALUES(9999, 'test', 8000);

※寫在IS和BEGIN之間即可