Triggers on every table in schema

CrackerJack

Hi All,

I want to know every details that whenever someone performs a DML in schema A on any tables to logged into a table.

I have attempted these:

-Create log table


CREATE TABLE audit_rst(

user_id VARCHAR2(30),

session_id NUMBER,

host varchar2(30),

ip_add varchar2(30),

action CHAR(3), 

date_executed DATE);

--Create a row based trigger for every tabel available

--this exampel shows only one table


CREATE OR REPLACE TRIGGER AUDIT_cc_2008

 AFTER INSERT OR DELETE OR UPDATE ON cc_2008 FOR EACH ROW

DECLARE 

v_operation VARCHAR2(10) := NULL;

BEGIN

IF INSERTING THEN

   v_operation := 'I';

ELSIF UPDATING THEN

   v_operation := 'U';

ELSE

   v_operation := 'D';

END IF;

IF INSERTING OR UPDATING THEN

  INSERT INTO audit_rst(

  USER_ID,

  SESSION_ID,

  HOST,

  ACTION,

  DATE_EXECUTED) 

  VALUES (

  user,

  sys_context('USERENV','SESSIONID'),

  sys_context('USERENV','HOST'),

  SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15)

  v_operation, SYSDATE);

ELSE 

  INSERT INTO audit_rst (

  USER_ID,

  SESSION_ID,

  HOST,

  ACTION,

  DATE_EXECUTED) 

  VALUES (

  user,

  sys_context('USERENV','SESSIONID'),

  sys_context('USERENV','HOST'),

  v_operation, SYSDATE);


END IF; 

END;

Do I need to create this trigger for every table available in teh schema...or is tehre a schema level trigger that can scan through the schema and pick up any dml changes to any table and log it into teh audit_rst table?

628524

fyi

Schema-Level Events for Trigger Definitions Event

SERVERERROR : Oracle fires the trigger whenever a server error message is logged.
LOGON : Oracle fires the trigger after a client application logs on to the database successfully.
LOGOFF : Oracle fires the trigger before a client application logs off the database.
CREATE : Oracle fires the trigger whenever a CREATE statement adds a new database object to the schema.
DROP : Oracle fires the trigger whenever a DROP statement removes an existing database object from the
schema.
ALTER : Oracle fires the trigger whenever an ALTER statement modifies an existing database object in the
schema.

105967

First of all, why do you think, you need to audit all these operations? If there is no business requirement, then better let it. Triggers can make your application very slow.

If you really want to audit all theses events, why didn't you look at the AUDIT command and use this? It is faster and you can audit a lot more. You can write the audit-records into the db or (probably better) filesystem.

If you want to do it with your triggers (just for the fun of it), I suggest, you add the autonomous transaction directive, otherwise, you'll never see any failed operations.

Rob van Wijk

Instead of coding it yourself, being error prone and using slow "for each row" database triggers, you'll definitely want to use builtin database features, in my opinion.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4007.htm#SQLRF01107

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/security.htm#sthref2916

Regards,
Rob.

Edited by: Rob van Wijk on Sep 11, 2008 9:56 AM

Sorry Leo, forgot to refresh ...