Oracle People:

Sometimes it's good not to get an answer :-) as I've been forced to dig a bit deeper into Oracle.

I can't use logminer and I can't use auditing so I've created the following trigger as a sort of low budget auditing:

TRIGGER STYLE AUDITS FOR ORACLE


First create a table that will contain the audit information:

SQL> create table amy.aud (

ID varchar2(20),
TIME varchar2(20),
TERM varchar2(20),
TYPE varchar2(20));

Now create a trigger script:

----------------------------------------
create or replace trigger trig1
after insert or update on AMY.artist
for each row
declare
time_now DATE;
terminal CHAR(10);
begin
time_now := SYSDATE;

terminal := USERENV('TERMINAL');

IF INSERTING THEN

INSERT INTO amy.aud

VALUES (user, time_now,

terminal, 'INSERT');



ELSIF DELETING THEN

INSERT INTO amy.aud

VALUES (user, time_now,

terminal, 'DELETE');



ELSE

INSERT INTO amy.aud

VALUES (user, time_now,

terminal, 'UPDATE');


END IF;

END;

/
---------------------------------------------------