Re: Passing a parm to a trigger

In article <3960F96D.85BD8290_at_wright.edu>,   terry.young_at_wright.edu wrote:
> This is a multi-part message in MIME format.
> --------------9AB44A69E1289F331C385BEC
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Would anyone have an idea about how to capture a parm and
> send it to a trigger?
>
> For example we have an application that logs into an Oracle
> account with a specific user account then using role
> enabling and role checking logs into a generic oracle
> account set up specifically for that application, then
> disconnects from the user account.
>
> The problem is when you have an update to a table, the same
> account is doing the update in the trigger, no way to
> identify who is doing the updating.
>
> Is there any way of doing this and still do the account
> switching.

an application context (see create context) in Oracle8i release 8.1 would be the preferred method.

Prior to that, a Package with a "get" and a "set" function/procedure would provide much the same functionality. eg:

create package my_pkg
as

   function get_user return varchar2;
   procedure set_user( p_username in varchar2 ); end;
/

create package body my_pkg
as

   g_username varchar2(30);

   function get_user is begin return g_username; end;

   procedure set_user(p_username in varchar2) is begin

        g_username := p_username; end;
end;

you would carefully grant execute on this package to restrict people from being able to "spoof" the username.

> --------------9AB44A69E1289F331C385BEC
> Content-Type: text/x-vcard; charset=us-ascii;
> name="terry.young.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Terry Young
> Content-Disposition: attachment;
> filename="terry.young.vcf"
>
> begin:vcard
> n:Young;Terry
> tel;work:937-775-2008
> x-mozilla-html:FALSE
> url:www.wright.edu/~terry.young
> org:Information Services;Computing and Telecommunications Services
> version:2.1
> email;internet:terry.young_at_wright.edu
> title:Analyst/Programmer
> adr;quoted-printable:;;140W Library Annex=0D=0A3640 Colonel Glenn
 Hwy.=0D=0A;Dayton;Ohio;45435-001;
> fn:Terry Young
> end:vcard
>
> --------------9AB44A69E1289F331C385BEC--
>
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.