Forum Stats

  • 3,827,902 Users
  • 2,260,838 Discussions
  • 7,897,403 Comments

Discussions

Triggers on every table in schema

CrackerJack
CrackerJack Member Posts: 472
edited Sep 11, 2008 3:56AM in SQL & PL/SQL
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?
Tagged:

Answers

  • 628524
    628524 Member Posts: 115
    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
    105967 Member Posts: 1,027
    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
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    edited Sep 11, 2008 3:56AM
    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 ...
This discussion has been closed.