Oracle觸發器3-DDL觸發器

DDL觸發器，當執行DDL語句時會被觸發。按照作用範圍，分為schema triggers,database triggers。schema triggers作用在一個使用者上，database triggers作用在整個資料庫所有使用者上。

建立DDL觸發器

要建立一個DDL觸發器，文法如下：

1 CREATE [OR REPLACE] TRIGGER trigger name                         --建立一個觸發器並制定名稱，or replace是可選項
2 {BEFORE | AFTER } { DDL event} ON {DATABASE | SCHEMA}     --指定觸發器是在DDL事件之前、之後觸發。範圍是on database、on schema
3 [WHEN (...)]                                                                               --可選的WHEN子句，使用邏輯判斷來避免觸發器無意義的執行
4 DECLARE                                                                                  --觸發器具體內容4-7

5 Variable declarations
6 BEGIN
7 ...some code...
8 END;

Examples:

SQL> CREATE OR REPLACE TRIGGER hr.testtrigger
2 AFTER CREATE ON SCHEMA  -- on schema 作用範圍只是在hr使用者下create table等觸發，其他使用者則不會。若是on database則其他使用者create table時會觸發該觸發器
3 BEGIN
4   -- 以下使用的是事件屬性
5 DBMS_OUTPUT.PUT_LINE('I believe you have created a ' ||
6 ORA_DICT_OBJ_TYPE || ' called ' ||
7 ORA_DICT_OBJ_NAME);
8 END;
9 /
Trigger created.

可用事件

                                            可用的DDL事件

DDL事件	觸發時機
ALTER	對資料庫中的任何一個對象使用SQL的ALTER命令時觸發
ANALYZE	對資料庫中的任何一個對象使用SQL的ANALYZE命令時觸發
ASSOCIATE STATISTICS	統計資料關聯到資料庫物件時觸發
AUDIT	通過SQL的AUDIT命令開啟審計時觸發
COMMENT	對資料庫物件做注釋時觸發
CREATE	通過SQL的CREATE命令建立資料庫物件時觸發
DDL	列表中所用的事件都會觸發
DISASSOCIATE STATISTICS	去掉統計資料和資料庫物件的關聯時觸發
DROP	通過SQL的DROP命令刪除資料庫物件時觸發
GRANT	通過SQL的GRANT命令賦權時觸發
NOAUDIT	通過SQL的NOAUDIT關閉審計時觸發
RENAME	通過SQL的RENAME命令對對象重新命名時觸發
REVOKE	通過SQL的REVOKE語句撤銷授權時觸發
TRUNCATE	通過SQL的TRUNCATE語句截斷表時觸發

可用屬性

Oracle 提供了一系列的函數用來提供關於什麼觸發了DDL觸發器以及觸發器的狀態燈資訊。上面那個觸發器的例子就使用了屬性。

                                                       DDL觸發事件以及屬性函數

函數名	傳回值
ORA_CLIENT_IP_ADDRESS	用戶端IP地址
ORA_DATABASE_NAME	資料庫名稱
ORA_DES_ENCRYPTED_PASSWORD	目前使用者的DES演算法加密後的密碼
ORA_DICT_OBJ_NAME	觸發DDL的資料庫物件名稱
ORA_DICT_OBJ_NAME_LIST	受影響的對象數量和名稱列表
ORA_DICT_OBJ_OWNER	觸發DDL的資料庫物件屬主
ORA_DICT_OBJ_OWNER_LIST	受影響的對象數量和名稱列表
ORA_DICT_OBJ_TYPE	觸發DDL的資料庫物件類型
ORA_GRANTEE	被授權人數量
ORA_INSTANCE_NUM	資料庫執行個體數量
ORA_IS_ALTER_COLUMN	如果操作的參數column_name指定的列，返回true,否則false
ORA_IS_CREATING_NESTED_TABLE	如果正在建立一個巢狀表格則返回true,否則false
ORA_IS_DROP_COLUMN	如果刪除的參數column_name指定的列，返回true,否則false
ORA_LOGIN_USER	觸發器所在的使用者名稱
ORA_PARTITION_POS	SQL命令中可以正確添加分區子句位置
ORA_PRIVILEGE_LIST	授予或者回收的許可權的數量。
ORA_REVOKEE	被回收者的數量
ORA_SQL_TXT	觸發了觸發器的SQL語句的行數。
ORA_SYSEVENT	導致DDL觸發器被觸發的時間
ORA_WITH_GRANT_OPTION	如果授權帶有grant選項，返回true。否則false

更多屬性函數請參考官方文檔PL/SQL Language Reference -> Triggers and Oracle Database Data Transfer Utilities

使用事件和屬性

Examples:

--建立資料庫物件時發出警告，刪除資料庫物件時阻止

CREATE OR REPLACE TRIGGER HR.no_drop
BEFORE DDL ON DATABASE
BEGIN
IF ORA_SYSEVENT = 'CREATE'
THEN
DBMS_OUTPUT.PUT_LINE('Warning !!! You have created a '||
                        ORA_DICT_OBJ_TYPE ||' called '||
                        ORA_DICT_OBJ_NAME|| '; UserName(creater):'||
                        ORA_DICT_OBJ_OWNER||'; IP:'||
                        ORA_CLIENT_IP_ADDRESS||'; event:'||
                        ORA_SYSEVENT);                       
ELSIF ORA_SYSEVENT = 'DROP'
THEN
RAISE_APPLICATION_ERROR (-20000,
'Cannot create the ' || ORA_DICT_OBJ_TYPE ||
' named ' || ORA_DICT_OBJ_NAME ||
' as requested by ' || ORA_DICT_OBJ_OWNER);
END IF;
END;

--操作了資料庫表的哪一列

CREATE OR REPLACE TRIGGER preserve_app_cols
   AFTER ALTER ON SCHEMA
DECLARE
   -- cursor to get columns in a table
   CURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2)
   IS
      SELECT column_name
        FROM all_tab_columns
       WHERE owner = cp_owner AND table_name = cp_table;
BEGIN
   -- if it was a table that was altered...
   IF ora_dict_obj_type = 'TABLE'
   THEN
      -- for every column in the table...
      FOR v_column_rec IN curs_get_columns (
                             ora_dict_obj_owner,
                             ora_dict_obj_name
                          )
      LOOP
         -- if the current column was the one that was altered then say so
         IF ora_is_alter_column (v_column_rec.column_name)
         THEN
            -- if the table/column is core?
            IF is_application_column (
                  ora_dict_obj_owner,
                  ora_dict_obj_name,
                  v_column_rec.column_name
               )
            THEN
               RAISE_APPLICATION_ERROR (
                  -20001,
                  'Cannot alter core application attributes'
               );
            END IF; -- table/column is core
         END IF; -- current column was altered
      END LOOP; -- every column in the table
   END IF; -- table was altered
END;
--屬性函數傳回值列表

CREATE OR REPLACE TRIGGER hr.what_privs
   AFTER GRANT ON SCHEMA
DECLARE
   v_grant_type     VARCHAR2 (30);
   v_num_grantees   BINARY_INTEGER;
   v_grantee_list   ora_name_list_t;
   v_num_privs      BINARY_INTEGER;
   v_priv_list      ora_name_list_t;
BEGIN
   v_grant_type := ora_dict_obj_type;
   v_num_grantees := ora_grantee (v_grantee_list);
   v_num_privs := ora_privilege_list (v_priv_list);

   IF v_grant_type = 'ROLE PRIVILEGE'
   THEN
      DBMS_OUTPUT.put_line (
         CHR (9) || 'The following roles/privileges were granted'
      );

      FOR counter IN 1 .. v_num_privs
      LOOP
         DBMS_OUTPUT.put_line (
            CHR (9) || CHR (9) || 'Privilege ' || v_priv_list (counter)
         );
      END LOOP;
   ELSIF v_grant_type = 'OBJECT PRIVILEGE'
   THEN
      DBMS_OUTPUT.put_line (
         CHR (9) || 'The following object privileges were granted'
      );

      FOR counter IN 1 .. v_num_privs
      LOOP
         DBMS_OUTPUT.put_line (
            CHR (9) || CHR (9) || 'Privilege ' || v_priv_list (counter)
         );
      END LOOP;

      DBMS_OUTPUT.put (CHR (9) || 'On ' || ora_dict_obj_name);

      IF ora_with_grant_option
      THEN
         DBMS_OUTPUT.put_line (' with grant option');
      ELSE
         DBMS_OUTPUT.put_line ('');
      END IF;
   ELSIF v_grant_type = 'SYSTEM PRIVILEGE'
   THEN
      DBMS_OUTPUT.put_line (
         CHR (9) || 'The following system privileges were granted'
      );

      FOR counter IN 1 .. v_num_privs
      LOOP
         DBMS_OUTPUT.put_line (
            CHR (9) || CHR (9) || 'Privilege ' || v_priv_list (counter)
         );
      END LOOP;
   ELSE
      DBMS_OUTPUT.put_line ('I have no idea what was granted');
   END IF;

   FOR counter IN 1 .. v_num_grantees
   LOOP
      DBMS_OUTPUT.put_line (
         CHR (9) || 'Grant Recipient ' || v_grantee_list (counter)
      );
   END LOOP;
END;
/