Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Audit command help

Re: Audit command help

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 10 Mar 2004 16:50:35 -0800
Message-ID: <1078966207.977304@yasure>


Teresa Redmond wrote:

>>Look up DDL triggers at: http://www.psoug.org/reference/library.html

>
>
> Okay, I've been fiddling with this and here is what I've come up with.
> I based it on your example:
>
> So far, this works. This puts a record into audit_table after I
> change a value in the atest table. Problem is, the SQL text it puts
> in the record *is the SQL text from the trigger body*!!! I wanted the
> SQL text that changed the value in the atest table! GRRRR!!!

You chose the wrong example and then removed its functionality.

Try this one and don't change CREATE OR ALTER OR DROP to UPDATE OR DELETE which have absolutely nothing to do with the subject:

CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE CREATE OR ALTER OR DROP
ON SCHEMA DECLARE
   oper ddl_log.operation%TYPE;

BEGIN
   SELECT ora_sysevent
   INTO oper
   FROM dual;

   IF oper IN ('CREATE', 'DROP') THEN

     INSERT INTO ddl_log
     SELECT ora_sysevent, ora_dict_obj_owner,
     ora_dict_obj_name, NULL, USER, SYSDATE
     FROM dual;
   ELSIF oper = 'ALTER' THEN
     INSERT INTO ddl_log
     SELECT ora_sysevent, ora_dict_obj_owner,
     ora_dict_obj_name, sql_text, USER, SYSDATE
     FROM v$open_cursor
     WHERE UPPER(sql_text) LIKE 'ALTER%';
   END IF;
END ddl_trigger;
/

If you want to audit UPDATE and DELETE then there are numerous examples at http://asktom.oracle.com and a schema trigger is not the solution.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Wed Mar 10 2004 - 18:50:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US