创建一个监听多个表的全局触发器

编程入门行业动态更新时间:2024-10-22 05:17:08

本文介绍了创建一个监听多个表的全局触发器的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我想在Oracle 11g中创建一个全局触发器.可用于审核约100个表.多个表可以触发一个触发器.如果是，那我该如何实现呢?

I want to create a global trigger in Oracle 11g. Which can be used for auditing of around 100 tables. Can multiple tables fire a single trigger. If yes then how can I achieve this?

推荐答案

我想在oracle 11g中创建一个全局触发器，该触发器可用于审核约100个表

I want to create a global trigger in oracle 11g ,which can be used for auditing of around 100 tables

您是否有理由重新发明轮子?为什么不利用Oracle的内置审计?

Is there a reason you want to reinvent the wheel? Why not make use of Oracle's inbuilt auditing?

Oracle Base 提供了一些有关如何入门的基本信息关于审核:

Oracle Base provides some basic info as to how to get started on Auditing:

可以通过设置AUDIT_TRAIL静态参数来启用审核，该参数具有以下允许值.

Auditing can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

以下列表提供了每种设置的说明:

The following list provides a description of each setting:

none or false - Auditing is disabled. db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$). db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated. xml- Auditing is enabled, with all audit records stored as XML format OS files. xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated. os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

要启用对数据库审核跟踪的审核，请启用对数据库的审核

To enable auditing to database audit trail enable auditing to db

SQL> ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE; System altered.

关闭&重新启动数据库

Shutdown & restart the db

SQL> SHUTDOWN Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP ORACLE instance started.

现在要由用户cube审核SELECTS，INSERTS，UPDATES，DELETES，请执行以下操作:

Now to audit SELECTS, INSERTS, UPDATES, DELETES by user cube do this:

CONNECT sys/password AS SYSDBA AUDIT ALL BY cube BY ACCESS; AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY cube BY ACCESS;

可以通过查询DBA_AUDIT_TRAIL

进一步阅读: