Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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.
0
sam15
Asked:
sam15
  • 5
  • 5
1 Solution
 
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
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now