Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Audit command help
Teresa Redmond wrote:
>>Look up DDL triggers at: http://www.psoug.org/reference/library.html
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;
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