Trigger五(系統 trigger DDL、登入/登出、開啟/關閉、錯誤訊息、啟用/禁用) (PL/SQL 二十二)

官網連結

※禁止使用DDL

CREATE OR REPLACE TRIGGER test_ddl
    BEFORE DDL ON SCHEMA 
BEGIN
    RAISE_APPLICATION_ERROR(-20000, '不能使用DDL');
END test_ddl;

※執行「CREATE SEQUENCE xxx;」就會出現訊息

※以下的練習還是要用trigger，所以先將trigger刪除，「DROP TRIGGER test_ddl;」

※ON SCHEMA一般用戶就可以CREATE； 但ON DATABASE預設沒權限

※官網 的 Table 9-5 Database Event Triggers 和 Table 9-6 Client Event Triggers可以參考

※系統定義的事件屬性

CREATE OR REPLACE TRIGGER test_ddl
    AFTER CREATE OR DROP OR ALTER ON DATABASE
BEGIN
    DBMS_OUTPUT.PUT_LINE('ORA_LOGIN_USER=' || ORA_LOGIN_USER);
    DBMS_OUTPUT.PUT_LINE('ORA_DICT_OBJ_TYPE=' || ORA_DICT_OBJ_TYPE);
    DBMS_OUTPUT.PUT_LINE('ORA_DICT_OBJ_OWNER=' || ORA_DICT_OBJ_OWNER);
    BMS_OUTPUT.PUT_LINE('ORA_DICT_OBJ_NAME=' || ORA_DICT_OBJ_NAME);
    BMS_OUTPUT.PUT_LINE('ORA_SYSEVENT=' || ORA_SYSEVENT);
END test_ddl;

※執行「DROP SEQUENCE xxx;」會出現以下結果：
ORA_LOGIN_USER=SYS
ORA_DICT_OBJ_TYPE=SEQUENCE
ORA_DICT_OBJ_OWNER=SYS
ORA_DICT_OBJ_NAME=XXX
ORA_SYSEVENT=DROP

ORA_DICT_OBJ_OWNER是SYS是因為我用SYS用戶進來的，才有權限，只要是ON DATABASE，預設一般用戶沒有權限

※還有很多屬性，可參考官網 的Table 9-4 System-Defined Event Attributes

※控制特定欄位不能修改和刪除

CREATE OR REPLACE trigger test_ddl
    BEFORE ALTER ON SCHEMA
DECLARE
    CURSOR xxx_emp(
        table_owner all_tab_columns.OWNER%TYPE,
        table_table_name all_tab_columns.TABLE_NAME%TYPE
    ) IS
    SELECT column_name FROM all_tab_columns
    WHERE owner = table_owner
    AND table_name = table_table_name;
BEGIN
    IF ora_dict_obj_type = 'TABLE' THEN
        FOR emp_row IN xxx_emp(ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME) LOOP
    
            IF ora_is_alter_column(emp_row.column_name) THEN
                IF emp_row.column_name IN('EMPNO', 'DEPTNO') THEN
                    raise_application_error(-20000, '不能修改EMPNO和DEPTNO欄位');
                END IF;
            END IF;
    
            IF ora_is_drop_column(emp_row.column_name) THEN
                IF emp_row.column_name IN('EMPNO', 'DEPTNO') THEN
                    raise_application_error(-20001, '不能刪除EMPNO和DEPTNO欄位');
                END IF;
            END IF;
    
        END LOOP;
    END IF;
END test_ddl;

※ALTER TABLE emp MODIFY(deptno);
ALTER TABLE emp DROP COLUMN empno;

※登入/登出記錄表

DROP TABLE login_logout_log PURGE;
CREATE TABLE login_logout_log(
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    username VARCHAR2(20),
    login_date DATE,
    logout_date DATE,
    ip VARCHAR2(30)
);
    
SELECT * FROM login_logout_log;

※因為以下都是ON DATABASE，所以都用有權限的使用者來創建

※登入/登出、開啟/關閉 Trigger

CREATE OR REPLACE TRIGGER XXX_LOGIN
    AFTER LOGON ON DATABASE
BEGIN
    INSERT INTO LOGIN_LOGOUT_LOG(username, login_date, ip)
    VALUES(ORA_LOGIN_USER, SYSDATE, ORA_CLIENT_IP_ADDRESS);
END XXX_LOGIN;
    
CREATE OR REPLACE TRIGGER XXX_LOGOUT
    BEFORE LOGOFF ON DATABASE
BEGIN
    INSERT INTO LOGIN_LOGOUT_LOG(username, logout_date, ip)
    VALUES(ORA_LOGIN_USER, SYSDATE, ORA_CLIENT_IP_ADDRESS);
END XXX_LOGOUT;

※登入/登出就會紀錄在表裡了

※開啟/關閉大同小異，只要改成AFTER STARTUP和BEFORE SHUTDOWN，然後執行SHUTDOWN IMMEDIATE和STARTUP就會記錄在表裡了，不過要注意PL/SQL Developer不支援這兩個命令，會出現「ORA-00900: invalid SQL statement」

※錯誤訊息 Trigger

CREATE OR REPLACE TRIGGER XXX_ERROR
    AFTER SERVERERROR ON DATABASE
BEGIN
    INSERT INTO ERR_LOG(dbname, err_date, err_msg)
    VALUES(ORA_DATABASE_NAME, SYSDATE, DBMS_UTILITY.FORMAT_ERROR_STACK);
END XXX_ERROR;

※SELECT * FROM III;給一個沒有的table名稱就會產生錯誤

※啟用/禁用Trigger

．預設在編譯完trigger後，就可以用了，可以在ON table 加上DISABLE或ENABLE(無D)，也就是預設在ON table後面就有ENABLE了，所以可以不打

．user_triggers、all_triggers、dba_triggers(sys權限)這三張表可以看全部的trigger，如看狀態可以用SELECT status FROM user_triggers就可以看到ENABLED或DISABLED(有D)

也可以使用ALTER TRIGGER trigger_name DISABLE/ENABLE，針對特定的trigger啟/禁用

．還可以使用ALTER TABLE [schema.]table_name ENABLE/DISABLE ALL TRIGGERS，如ALTER TABLE emp ENABLE ALL TRIGGERS;