我是靠谱客的博主 欣慰红牛,最近开发中收集的这篇文章主要介绍PL/SQL系统触发器(DDL触发器事件、数据库系统触发器事件、触发器属性列表)系统触发器,觉得挺不错的,现在分享给大家,希望可以做个参考。

概述

文章目录

  • 系统触发器
    • DDL触发器事件
    • 数据库系统触发器事件
    • 触发器属性列表


系统触发器

DML触发器和替代触发器都是在DML事件上触发的 ,而系统触发器是在DDL事件和数据库服务器事件时触发的。

如果要创建系统触发器,可以使用如下的语法:

--使用管理员才可以创建系统触发器
CREATE [OR REPLACE] TRIGGER trigger_name
[BEFORE | AFTER] [DDL event | DATABASE event]  --系统触发事件
ON [DATABASE | SCHEMA]
[WHEN 触发条件] 
plsql_block

DDL event包括CREATE、DROP、ALTER、GRANT、RENAME、REVOKE、TRUNCATE、ANALYZE、ASSOCIATE STATISTICS、AUDIT、COMMENT等。

DATABASE event是数据库级的系统事件,对每一个触发的事件,Oracle会打开一个匿名事务,触发触发器,提交任何独立的事务,这些事件有SERVERERROR、LOGON、LOGOFF、STARTUP、SHUTDOWN、SUSPEND。

在创建数据库级的触发器时,必须具有ADMINISTER DATABASE TRIGGER的系统权限。

DDL触发器事件

事件触发时机描述
CRAETEBEFORE/AFTER在创建一个方案对象之前或之后触发,比如创建表、索引等对象
DROPBEFORE/AFTER在删除一个方案对象之前或之后触发
ALTERBEFORE/AFTER在修改一个方案对象之前或之后触发
GRANTBEFORE/AFTER在使用GRANT 分配权限之前或之后触发
REVOKEBEFORE/AFTER在使用REVOKE语句取消权限之前或之后触发
RENAMEBEFORE/AFTER在使用RENAME对一个数据库对象进行重命名之前或之后触发
ANALYZEBEFORE/AFTER在使用ANALYZE分析数据库对象之前或之后触发
ASSOCIATE STATISTICSBEFORE/AFTER统计相关的数据库对象之前或之后触发
DISASSOCIATE STATISTICSBEFORE/AFTER取消对一个数据库对象的统计之前或之后触发
AUDITBEFORE/AFTER在使用AUDIT 开启审计功能之前或之后触发
NOAUDITBEFORE/AFTER在使用NOAUDIT语句关闭审计功能之前或之后触发
COMMENTBEFORE/AFTER在对一个数据库对象应用注释之前或之后触发
TRUNCATEBEFORE/AFTER在使用TRUNCATE 清除一个表的内容之前或之后触发
DDLBEFORE/AFTERDDL指定在本表格中列出的任何事件执行之前或之后触发

数据库系统触发器事件

数据库系统触发器用于监视数据库服务的打开、关闭、错误等信息的取得,或者是监控用户的行为操作等。

事件触发时机描述
STARTUPAFTER数据库实例启动之后触发
SHUTDOWNBEFORE当数据库实例关闭前触发,如果数据库是异常关闭,那么这个时间可能不会触发
SERVERERRORAFTER只要发生错误就会被触发
LOGONAFTER用户登录后触发
LOGOFFBEFOR用户注销前触发

触发器属性列表

Oracle在DBMS_STANDARD包中提供了一些功能性的函数,以便在开发系统级别的触发器时可以提供一些系统级别的信息

属性函数描述
ora_client_ip_address返回客户端的IP地址
ora_database_name返回当前数据库的名称
ora_des_encrypted_password返回DES加密后的用户口令
ora_dict_obj_name返回DDL操作所对应的数据库对象名
ora_dict_obj_owner返回DDL操作所对应的对象的所有者名称
ora_dict_obj_type返回DDL操作所对应的数据库对象的类型
ora_dict_obj_name_list(name_list OUT ora_name_list_t)返回在事件中被修改的对象名列表
ora_dict_obj_owner_list(owner_list OUT ora_name_list_t)返回在事件中被修改的对象的所有者列表
ora_grantee(user_list OUT ora_name_list_t)返回授权事件的授权者
ora_instance_num返回例程号
ora_is_creating_nested_table用于检测是否正在建立嵌套表
ora_is_drop_column(column_name IN VARCHAR2)如果指定的column_name正在被移除,则返回True,否则返回False
ora_is_alter_column(column_name IN VARCHAR2)如果指定的column_name已经被修改,则返回True,否则返回False
ora_is_servererror(error_number)用于检测是否返回了特定Oracle错误
ora_login_user返回登录用户名
ora_sysevent用户返回触发触发器的系统事件名

这里的ora_name_list_t是定义在DBMS_STANDARD包中的一个嵌套表类型,定义如下:
TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);

下面的代码演示了如何使用ora_is_alter_column来防止用户对emp表的empno字段进行修改,用ora_is_drop_column来防止emp表的empno字段被删除:

CREATE OR REPLACE TRIGGER preserve_app_cols
   AFTER ALTER ON SCHEMA
DECLARE
   --获取一个表中所有列的游标
   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
   -- 如果正使用的是ALTER TABLE语句修改表
   IF ora_dict_obj_type = 'TABLE'
   THEN
      -- 循环表中的每一列
      FOR v_column_rec IN curs_get_columns (
                             ora_dict_obj_owner,
                             ora_dict_obj_name
                          )
      LOOP
         --判断当前的列名正在被修改
         IF ORA_IS_ALTER_COLUMN (v_column_rec.column_name) THEN
            IF v_column_rec.column_name='EMPNO' THEN
               RAISE_APPLICATION_ERROR (-20003, '不能对empno字段进行修改');
            END IF; 
         END IF; 
         IF ORA_IS_DROP_COLUMN('EMPNO') THEN
            RAISE_APPLICATION_ERROR (-20004, '不能对empno字段进行删除');
         END IF; 
      END LOOP;
   END IF;
END;

示例一、登录登出日志功能

--创建序列
CREATE SEQUENCE user_log_seq;
--创建日志表
CREATE TABLE user_log(
     logid        NUMBER          CONSTRAINT pk_logoid PRIMARY KEY,
     username     VARCHAR2(50)    NOT NULL,
     logondate    DATE,
     logoffdate   DATE,
     ip           VARCHAR2(20),
     logtype      VARCHAR2(20)
);
--创建登录触发器
CREATE OR REPLACE TRIGGER logon_trigger
AFTER LOGON ON DATABASE
BEGIN
	INSERT INTO user_log(logid, username, logondate, ip, logtype)
	VALUES(user_log_seq.nextval, ora_login_user, SYSDATE, ora_client_ip_address, 'LOGON');
END LOGON_TRIGGER;

--创建登出触发器
CREATE OR REPLACE TRIGGER logoff_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
	INSERT INTO USER_LOG(LOGID, USERNAME, LOGOFFDATE, IP, LOGTYPE)
	VALUES(user_log_seq.nextval, ora_login_user, SYSDATE, ora_client_ip_address, 'LOGFF');
END LOGON_TRIGGER;
--切换用户进行登录

SELECT * FROM user_log;  --使用管理登录,查看user_log表

示例二、 系统启动和关闭时,日志记录功能

--创建序列
CREATE SEQUENCE db_event_log_seq;
--查询序列
SELECT * FROM user_sequences WHERE sequence_name = 'DB_EVENT_LOG_SEQ';
--创建数据库记录事件表
CREATE TABLE db_event_log(
     eventid      NUMBER          CONSTRAINT pk_eventid PRIMARY KEY,
     enentType    VARCHAR2(50)    NOT NULL,
     enentDate    DATE    		  NOT NULL,
     eventUser    VARCHAR2(50)    NOT NULL
);
--创建启动之后触发器
CREATE OR REPLACE TRIGGER startup_trigger
AFTER STARTUP ON DATABASE    
BEGIN
	INSERT INTO db_event_log(eventid, eventtype, eventdate, eventuser)
	VALUES(db_event_log_seq.nextval, 'STARTUP', SYSDATE, ora_login_user);
	COMMIT;
END;

--创建关闭之前触发器
CREATE OR REPLACE TRIGGER shutdown_trigger
BEFORE SHUTDOWN ON DATABASE   
BEGIN
	INSERT INTO db_event_log(eventid, eventtype, eventdate, eventuser)
	VALUES(db_event_log_seq.nextval, 'SHUTDOWN', SYSDATE, ora_login_user);
	COMMIT;
END;
--测试在sqlplus中执行
SHUTDOWN ABORT; --关闭数据库
STARTUP;          --启动数据库
SELECT * FROM db_event_log;

示例三、 错误信息日志
SERVERERROR事件可以用来跟踪数据库中发生的错误,错误代码可以通过SERVER_ERROR属性函数在触发器内部得到,可以通过该函数确定堆栈中的错误代码,可以使用DBMS_UTILITY.FORMAT_ERROR_STACK获取错误信息

AFTER SERVERERROR触发器并没有提供方法来调整出现的错误,仅能包含错误的相关的信息,管理员可以通过使用这些触发器来构建强大的日志机制

--创建序列
CREATE SEQUENCE db_error_seq;
--查询序列
SELECT * FROM user_sequences WHERE sequence_name = 'DB_ERROR_SEQ';
---创建一张记录错误信息的数据表
CREATE TABLE db_error(
    eid    	    NUMBER    CONSTRAINT pk_eid PRIMARY KEY,
    username    VARCHAR2(50),
    errorDate   DATE,
    dbname      VARCHAR2(50),
    CONTENT     CLOB
);
--创建数据库错误触发器
CREATE OR REPLACE TRIGGER error_trigger
AFTER SERVEERROR ON DATABASE   
BEGIN
	INSERT INTO db_error(eid, username, errordate, dbname, content)
	VALUES(db_error_seq.nextval, ora_login_user, SYSDATE, ora_database_name, dbms_utility.format_error_stack);
     --dbms_utility包
     --dbms_utility.format_error_stack错误内容
END;

最后

以上就是欣慰红牛为你收集整理的PL/SQL系统触发器(DDL触发器事件、数据库系统触发器事件、触发器属性列表)系统触发器的全部内容,希望文章能够帮你解决PL/SQL系统触发器(DDL触发器事件、数据库系统触发器事件、触发器属性列表)系统触发器所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错,欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
点赞(54)

评论列表共有 0 条评论

立即
投稿
返回
顶部