Tuesday, 4 September 2018

How to write a sql to get user and sysdate details or to audit db in oracle


CREATE OR REPLACE TRIGGER NEW_TRIGGER_NAME BEFORE UPDATE OR DELETE
ON <TABLE_NAME>
DECLARE
usr_name varchar2(60);
time_stamp date := sysdate;
the_sql varchar2(3005);

BEGIN
usr_name := sys_context('userenv' 'os_user');
the_sql := substr ( sys_context('userenv' 'current_sql'), 1, 3000);

insert into table_audit values ('QAA',usr_name, time_stamp, the_sql);

EXCEPTION
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END;

No comments:

Post a Comment

If you Like my blog Spread it and help friends for whom this blog is useful for their career.