CODEWITHSUNDEEP
oracle-databasedatabase-triggertoad
sree
sree

Reputation: 1

created trigger to send out an email on delete from a table in toad/oracle. is there a way i can capture which user deleted the record?

created trigger to send out an email on delete from a table in toad/oracle. is there a way i can capture which user deleted the record? There are no entries in the table which gives the user id or name to know who deleted the record.

Upvotes: 0

Views: 181

Answers (2)

korodani
korodani

Reputation: 178

You can capture any DELETE statement on desired table:

audit delete on your_schema.your_table;

In order to enable AUDIT you must have auditing enabled. Check your audit settings with:

 select * from v$parameter where name like '%trail%';

The result should be DB,EXTENDED. If it's not, you can achieve that by:

alter system set audit_trail=db,extended scope=spfile; -- for this change to take affect, you must restart your DB instance.

Afterwards you will find captured DELETE operations in SYS.AUD$ table in a column SQL_TEXT.

Upvotes: 0

Popeye
Popeye

Reputation: 35920

You can get it using:

SELECT sys_context('USERENV', 'SESSION_USER') FROM dual;

Also, you can use USER to frtch the username as following:

V_USER := USER;

now, you can use the V_USER at any where in trigger. Cheers!!

Upvotes: 1

Related Questions