Creating a global trigger that listens to multiple tables

Question

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?

Answer 1

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

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

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

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.

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;

The audited logs can be brought up by querying DBA_AUDIT_TRAIL

Further reading:

• Auditing
• Fine grained auditing
• Configuring and administering auditing