This discussion is archived
14 Replies Latest reply: Feb 17, 2013 10:24 AM by EdStevens RSS

trigger

941949 Explorer
Currently Being Moderated
hi everybody ,
can you people please help me to correct this trigger?i wrote this trigger to capture select,insert,update and delete statements.this trigger not showing any error.problem is in table named test i m not getting sql statements run by the users and when i check the dba_fga_audit_trail i m not getting client machine name,client ip address and client name who runs the sql statements in the database,why ?your help appreciated.thanks.


CREATE TABLE test
(user_id varchar2(30),
sess_id number(10),
logon_time varchar2(100),
client_ip varchar2(20),
client_name varchar2(150),
client_machine varchar2(150),
v_program varchar2(100)
);


CREATE OR REPLACE TRIGGER trg_session_info
after logon on database
ON DATABASE
DECLARE
cnt integer;
CURSOR get_tab IS
SELECT table_name FROM sys.dba_tables where owner='SCOTT';
v_tabname get_tab%rowtype;
begin
insert into test
(
user_id,sess_id,logon_time,client_ip,
client_name,client_machine,v_program
)
select username,sid,systimestamp,sys_context('USERENV','IP_ADDRESS'),
osuser,machine,program
from v$session
where sid = (select sid from v$mystat where rownum = 1 )
and ( upper(program) like '%TOAD%'
or upper(program) like '%SQLPLUS%'
);
IF NOT get_tab%ISOPEN THEN
OPEN get_tab;
END IF;

LOOP

FETCH get_tab INTO v_tabname;
EXIT WHEN get_tab%NOTFOUND;
dbms_output.put_line(v_tabname.table_name);

select count(*) into cnt from dba_audit_policies where object_schema='SCOTT' and object_name=v_tabname.table_name;
if cnt=0 then
dbms_fga.add_policy (
object_schema => 'SCOTT',
object_name => v_tabname.table_name,
policy_name => 'AUDIT_' || v_tabname.table_name,
audit_column => NULL,
audit_condition => NULL,
statement_types => 'SELECT,INSERT,UPDATE,DELETE'
);
end if;
END LOOP;
END;
  • 1. Re: trigger
    Nitesh. Explorer
    Currently Being Moderated
    ...

    Edited by: Niteshkhush on Feb 12, 2013 5:05 PM
  • 2. Re: trigger
    941949 Explorer
    Currently Being Moderated
    is there anybody here on this forum to help me in solving this trigger issue
  • 3. Re: trigger
    Karthick_Arp Guru
    Currently Being Moderated
    Most of the time we feel the tool that we use is not working properly. But the actual issue would be that the choice of tool. Is the correct tool used to adress the problem.

    So rather than putting some piece of code and asking to fix it, if you could tell your requirement and what you are trying to achieve may be you could end up getting a better and more optimal solution.
  • 4. Re: trigger
    Padma.... Newbie
    Currently Being Moderated
    Hi,

    You have done an insert in the trigger but have not committed it. so changes would not be visible. So use a commit along with PRAGMA AUTONOMOUS TRANSACTION and i hope it would help.

    Thanks
    Padma...
  • 5. Re: trigger
    John Stegeman Oracle ACE
    Currently Being Moderated
    You have done an insert in the trigger but have not committed it. so changes would not be visible. So use a commit along with PRAGMA AUTONOMOUS TRANSACTION and i hope it would help.
    No, please don't do this.

    There's a reason that commit is not allowed in triggers. Well, more than one, actually.
  • 6. Re: trigger
    Paul Horth Expert
    Currently Being Moderated
    Padma.... wrote:
    Hi,

    You have done an insert in the trigger but have not committed it. so changes would not be visible. So use a commit along with PRAGMA AUTONOMOUS TRANSACTION and i hope it would help.

    Thanks
    Padma...
    Noooooo! Do not commit inside trigger (except perhaps when error logging).

    See http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4003705800346120218
  • 7. Re: trigger
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    There is no practical way to capture the executing SQL statement text inside of a DML Trigger fired by that statement.
  • 8. Re: trigger
    941949 Explorer
    Currently Being Moderated
    thank you all for quick response and my requirement is to write a trigger to capture "select,insert,update and delete" statements executed by the users(or clients) in the oracle database (10g or 10.2.0.3),from which machine(machine name)these statements executed,machine ipaddress,logon time of users who run the sql statements ,statements execution time .
    your help HIGLY APPRECIATED.
  • 9. Re: trigger
    EdStevens Guru
    Currently Being Moderated
    938946 wrote:
    thank you all for quick response and my requirement is to write a trigger to capture "select,insert,update and delete" statements executed by the users(or clients) in the oracle database (10g or 10.2.0.3),from which machine(machine name)these statements executed,machine ipaddress,logon time of users who run the sql statements ,statements execution time .
    your help HIGLY APPRECIATED.
    If your requirement is to "write a trigger", then whoever created the requirement needs some serious re-eduction.

    Your requirement should be to "capture <data>)"

    The requirements are not the same.

    If you see the requirement as "capture data", you will soon discover there are better methods than a trigger ....

    If your only tool is a hammer, every problem looks like a nail.

    Edited by: EdStevens on Feb 14, 2013 7:12 AM
  • 10. Re: trigger
    941949 Explorer
    Currently Being Moderated
    my requirement is to capture tha data plus sql statements.i want customised auditing (for a single db user).
  • 11. Re: trigger
    Etbin Guru
    Currently Being Moderated
    Taking a look at http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_fga.htm#ARPLS66348 might help

    Regards

    Etbin
  • 12. Re: trigger
    941949 Explorer
    Currently Being Moderated
    is there naybody?who can solve the above trigger and modify the trigger according to my requirement?

    Edited by: 938946 on Feb 17, 2013 4:59 AM
  • 13. Re: trigger
    Etbin Guru
    Currently Being Moderated
    I try to aviod triggers whenever possible, nevertheless maybe this: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:59412348055

    Regards

    Etbin
  • 14. Re: trigger
    EdStevens Guru
    Currently Being Moderated
    938946 wrote:
    my requirement is to capture tha data plus sql statements.i want customised auditing (for a single db user).
    What, specifically, about your requirement do you feel cannot be met with either standard auditing or Fine Grained Auditing, already available as part of the oracle database? I seriously doubt you need to re-invent the wheel.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points