Oracle trigger example

[bottlea]

We have a table that keeps getting deleted but we can't work out who is doing it. We don't want to use database auditing because of its performance hit

Can someone give me an example of a trigger (with the PL/SQL body) that gets the name of the externally authenticated OS user, the name of his/her machine and the date of the DML statement and puts this info into another table before the table's contents get deleted.

Thanks

Andy Bottle

 

[MikeLacey]

Andy,

Could I suggest that you remove the 'drop any table' privilege from all users except the DBA's.

Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[rcurva]

Here is a sample trigger that you will create for every table, that will insert into a log table for every deleted row on that table. You can play around with this trigger to something that will suit your requirements.

This is just a simple example that I can come up right now, you can do better than this one.

CREATE OR REPLACE TRIGGER del_trg1
AFTER DELETE
ON table1
FOR EACH ROW
WHEN ( 1=1 )
DECLARE
v_user VARCHAR2(30);
Begin
SELECT user INTO v_user
FROM DUAL;

INSERT INTO delete_log
VALUES ('table1',v_user,TO_CHAR(sysdate, 'DD-MON-YYYY'), TO_CHAR(sysdate, 'HH24:MI:SS'));
End;
/
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War