TableTriigger.

i have a table T1 and with two client applications. One application is oracle web application and another is power builder client application.

Is it possible to wite a trigger that set some :NEW values in the row inserted only after the client application inserts or updates a row in the table. I do not want the trigger to fire when web application inserts rows. Can you add WHEN clause using environment variable for name of client.
sam15Asked:
Who is Participating?
 
sdstuberCommented:
the trigger might look something like this...

CREATE OR REPLACE TRIGGER your_trigger
    BEFORE INSERT OR UPDATE
    ON your_table
    FOR EACH ROW
BEGIN
    if your_package.your_package_variable = 'WEB APP' then     --- put your identifying condition here
        null;
    else
          :new.col1 := sysdate;
          :new.col2 := 'abc';              ---- change these as needed
          :new.col3 := user;
    end if;
END;
0
 
sdstuberCommented:
only if you have some way to identify which application is which.

Do they login from particular servers?
Distinct users?
Do they set a context/package variable/client identifier/ etc?
0
 
sam15Author Commented:
well when i look att the V$session there is a column that shows different names for each sessions.
I think oracle has an environment variable for client_identifier.

http://psoug.org/reference/sys_context.html


they log in from client machines. they use application accounts. web users use browser and client/server users us software on their machine.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sam15Author Commented:
I wonder also if i can base the trigger on where the transaction comes from mod_plsql or not?

CREATE OR REPLACE TRIGGER test_tbl
AFTER UPDATE OR DELETE OR INSERT ON test_tbl
FOR EACH ROW
BEGIN
  IF owa.num_cgi_vars > 0 THEN
      lvIPAddress := owa_util.get_cgi_env(param_name => 'REMOTE_ADDR');
      lvUser := SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER');
  ELSE
      lvIPAddress := SYS_CONTEXT('USERENV',
                                 'IP_ADDRESS');
      lvUser := SYS_CONTEXT('USERENV', 'SESSION_USER');
  END IF;
 
  -- insert info into your audit table
END;  
0
 
sdstuberCommented:
you can use anything you want

that was my whole point.

you only need ONE attribute that is distinct between them.  I listed a bunch of examples, but I don't know what you have and what might be different

so, if you have a cgi value you can extract, that's fine

if it's coming from a web server, you could probably use that too
0
 
sam15Author Commented:
IF owa.num_cgi_vars > 0 THEN

would this always be > 0 for MOD_PLSQL and 0 for client/server connection. I am wondering if it is safe to use.
0
 
sdstuberCommented:
it should work to identify a mod_plsql app

however it's not 100% reliable because any app that calls owa.init_cgi_env  could populate the owa array.
it's not likely though.

and it should be relatively easy to test.  Create your trigger, make sure only one of the applications is active and see what happens,  then try it with the other one.
0
 
sam15Author Commented:
V$SESSION has a column named "PROGRAM" that shows the name of the client application
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm

I cannot find the correct parameter for this in USERENV namepspace that can be retreived using SYS_CONTEXT. Do you know which one. I tried module but it is blank in sql*plus.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions165.htm

I am trying to improve the trigger performance by having it only fire when the data is loaded from client. 90% of data is loaded from WEB so i do not really need to fire the trigger. do you think adding WHEN clause *i.e WHEH program = client.exe"
will do this or it is similar to having IF condition in the  body of trigger.
0
 
sdstuberCommented:
you'll have to query v$session if you want to use program

the when condition is basically the same as an IF, it should be slightly more efficient but it has restrictions.  It can't involve any pl/sql.

0
 
sam15Author Commented:
is this what you normally use to query v$session for your session

select <whatever you want> from v$session where audsid=userenv('sessionid');

 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.