oracle trigger触发器记录某个用户ddl的操作信息--part1

最新推荐文章于 2023-05-04 09:36:45 发布

clg10051

最新推荐文章于 2023-05-04 09:36:45 发布

阅读量542

点赞数

文章标签：数据库后端数据结构与算法

前言

因为项目需要，需要记录对数据库某个用户的DDL操作的信息，然后进行下一步的深加工与处理。

测试

1,编写触发器的权限

2，编写触发器的语法

一些由PLSQL代码内部产生的DDL不会触发触发器

ddl_event如下：

The following ddl_event values are valid:

ALTER

Causes the database to fire the trigger whenever an ALTER statement modifies a database object in the data dictionary. An ALTER DATABASE statement does not fire the trigger.
ANALYZE

Causes the database to fire the trigger whenever the database collects or deletes statistics or validates the structure of a database object.

See Also:
Oracle Database SQL Language Reference for information about using the SQL statement ANALYZE to collect statistics
ASSOCIATE STATISTICS

Causes the database to fire the trigger whenever the database associates a statistics type with a database object.
AUDIT

Causes the database to fire the trigger whenever an AUDIT statement is issued.
COMMENT

Causes the database to fire the trigger whenever a comment on a database object is added to the data dictionary.
CREATE

Causes the database to fire the trigger whenever a CREATE statement adds a database object to the data dictionary. The CREATE DATABASE or CREATECONTROLFILE statement does not fire the trigger.
DISASSOCIATE STATISTICS

Causes the database to fire the trigger whenever the database disassociates a statistics type from a database object.
DROP

Causes the database to fire the trigger whenever a DROP statement removes a database object from the data dictionary.
GRANT

Causes the database to fire the trigger whenever a user grants system privileges or roles or object privileges to another user or to a role.
NOAUDIT

Causes the database to fire the trigger whenever a NOAUDIT statement is issued.
RENAME

Causes the database to fire the trigger whenever a RENAME statement changes the name of a database object.
REVOKE

Causes the database to fire the trigger whenever a REVOKE statement removes system privileges or roles or object privileges from a user or role.
TRUNCATE

Causes the database to fire the trigger whenever a TRUNCATE statement removes the rows from a table or cluster and resets its storage characteristics.
DDL

Causes the database to fire the trigger whenever any of the preceding DDL statements is issued.

数据库事件如下：

One of the following database events. You can create triggers for these events on either DATABASE or SCHEMA unless otherwise noted. Each database event is valid in either a BEFORE trigger or an AFTER trigger, but not both. For each of these triggering events, the database opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).

AFTER STARTUP

Causes the database to fire the trigger whenever the database is opened. This event is valid only with DATABASE, not with SCHEMA.
BEFORE SHUTDOWN

Causes the database to fire the trigger whenever an instance of the database is shut down. This event is valid only with DATABASE, not with SCHEMA.
AFTER DB_ROLE_CHANGE

In a Data Guard configuration, causes the database to fire the trigger whenever a role change occurs from standby to primary or from primary to standby. This event is valid only with DATABASE, not with SCHEMA.
AFTER SERVERERROR

Causes the database to fire the trigger whenever both of these conditions are true:
- A server error message is logged.
- Oracle relational database management system (RDBMS) determines that it is safe to fire error triggers.
  
  Examples of when it is unsafe to fire error triggers include:
  - RDBMS is starting up.
  - A critical error has occurred.
AFTER LOGON

Causes the database to fire the trigger whenever a client application logs onto the database.
BEFORE LOGOFF

Causes the database to fire the trigger whenever a client application logs off the database.
AFTER SUSPEND

Causes the database to fire the trigger whenever a server error causes a transaction to be suspended.

4,准备在SCOTT用户进行测试

SQL> select username from dba_users where username='SCOTT';

USERNAME

------------------------------

SCOTT

连接到SCOTT用户

SQL> conn scott/system

Connected.

创建存储触发器加工结果的表

SQL> create table t_record_ddl(a int);

Table created.

创建触发器

SQL> create or replace trigger tri_record_ddl

after create or alter on schema

3 begin

4 insert into t_record_ddl values(1);

commit;

6 end;

7 /

Trigger created.

5,验证触发器是否功能正常

触发触发器前的表为空

SQL> select * from t_record_ddl;

no rows selected

创建一个表

(

在触发器内部用使用commit)

SQL> create table t_add(a int);

create table t_add(a int)

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-04092: cannot COMMIT in a trigger

ORA-06512: at line 3

重新编辑修正触发器

SQL> create or replace trigger tri_record_ddl

after create or alter on schema

3 begin

4 insert into t_record_ddl values(1);

5 end;

6 /

Trigger created.

SQL> create table t_add(a int);

Table created.

SQL> select * from t_record_ddl;

----------

变更一个表

SQL> alter table t_add add b int;

Table altered.

SQL> select * from t_record_ddl;

----------

变更一个表的列

SQL> alter table t_add rename column b to b_new;

Table altered.

SQL> select * from t_record_ddl;

----------

删除一个表的列

SQL> alter table t_add drop column a

2 ;

Table altered.

SQL> select * from t_record_ddl;

----------

变更表的列数据类型

修改触发器，以记录触发触发器的DDL对象

SQL> create or replace trigger tri_record_ddl

after create or alter on schema

3 begin

4 insert into t_record_ddl values(ora_dict_obj_name);

5 end;

6 /

Trigger created.

如果被修改的表发生DDL，且被触发器引用，不能修改此表的数据结构

SQL> alter table t_record_ddl modify a varchar2(500);

alter table t_record_ddl modify a varchar2(500)

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-30512: cannot modify SCOTT.T_RECORD_DDL more than once in a transaction

ORA-06512: at line 2

禁用触发器

SQL> alter trigger tri_record_ddl disable;

Trigger altered.

禁用触发器，即可修改被触发器引用的表

SQL> alter table t_record_ddl modify a varchar2(500);

Table altered.

启用触发器

SQL> alter trigger tri_record_ddl enable;

Trigger altered.

新增表

SQL> create table t_add_obj(a int);

Table created.

SQL> select * from t_record_ddl;

--------------------------------------------------------------------------------

T_ADD_OBJ

变更表

SQL> alter table t_add_obj add b int;

Table altered.

SQL> select * from t_record_ddl;

--------------------------------------------------------------------------------

T_ADD_OBJ

SQL> alter table t_add_obj drop column b;

Table altered.

SQL> select * from t_record_ddl;

--------------------------------------------------------------------------------

T_ADD_OBJ

SQL> alter table t_add_obj rename column a to new_a;

Table altered.

SQL> select * from t_record_ddl;

--------------------------------------------------------------------------------

T_ADD_OBJ

SQL> alter table t_add_obj modify new_a varchar2(10);

Table altered.

SQL> select * from t_record_ddl;

--------------------------------------------------------------------------------

T_ADD_OBJ

在非当前用户进行DDL

SQL> show user

USER is "SYSTEM"

SQL> create table t_system(a int);

Table created.

SQL> alter table t_system add b int;

Table altered.

可见在非当前用户进行DDL，不会触发触发器

SQL> select * from t_record_ddl;

--------------------------------------------------------------------------------

T_ADD_OBJ

禁用触发器

SQL> alter trigger tri_record_ddl disable;

Trigger altered.

重定义触发器引用的表

SQL> drop table t_record_ddl purge;

Table dropped.

存储DDL的对象名称及DDL的时间

SQL> create table t_record_ddl(obj_name varchar2(500),ddl_time date);

Table created.

SQL> alter trigger tri_record_ddl enable;

Trigger altered.

修正触发器，添加更加选项和功能

SQL> create or replace trigger tri_record_ddl

after create or alter on schema

3 begin

4 insert into t_record_ddl values(ora_dict_obj_name,sysdate);

5 end;

6 /

Trigger created.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select * from t_record_ddl;

no rows selected

SQL> create table t_more(a int);

Table created.

SQL> alter table t_more add b int;

Table altered.

SQL> alter table t_more rename column a to new_a;

Table altered.

SQL> alter table t_more drop column b;

Table altered.

SQL> alter table t_more modify new_a varchar2(10);

Table altered.

SQL> col obj_name for a50

SQL> col ddl_time for a30

SQL> select * from t_record_ddl order by 2;

OBJ_NAME DDL_TIME

-------------------------------------------------- ------------------------------

T_MORE 2015-04-24 12:01:36

T_MORE 2015-04-24 12:01:46

T_MORE 2015-04-24 12:01:58

T_MORE 2015-04-24 12:02:11

T_MORE 2015-04-24 12:02:29

结论：
1，触发器功能相当强大，某些功能仍须查阅官方文档，获取进一步信息
2，触发器包括基于DDL或DML的触发器，并且有诸多选项可以高级定制触发器的实现逻辑
3，触发器包括属性函数，可以实现某些特定的功能或获取某些信息
4，触发器内部不能包括COMMIT相关语句，即内部事务会自动提交
5，触发器引用的表，要修改必须先禁用触发器