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_POSSQL命令中可以正确添加分区子句位置
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;
/

转载于:https://www.cnblogs.com/AlbertCQY/archive/2013/04/07/3003981.html

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值