| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Check on column when record is updated
Not much harder either. Create a context (CREATE CONTEXT ... USING ...)
and require application to set current employee ID into this context right
after the user logs in. Contexts can only be altered through authorized
packages or procedures, so you gotta create a package or procedure, which
will be called as part of login and passed the ID, something like this:
create context myappctx using setCurrentEmployeeId /
create or replace procedure setCurrentEmployeeId(p_id IN INTEGER) as
-- this procedure implies that one application user = one Oracle session -- and that logging in as another user requires re-establishing the Oracle -- session. You can actually put the context set into the login procedure -- and authorize that procedure to alter context - you will then be able -- to switch application users during the same Oracle session.begin
In the trigger, you will use this context (which is freely readable) like this:
:NEW.Mod_De_Emp_Id :=
TO_NUMBER(SYS_CONTEXT('myappctx','logged_in_emp_id'));
And if SYS_CONTEXT() eventually returns NULL for this context attribute, you can raise an application error (application user is not logged in).
This way you have single set point - the procedure, which will only set attribute once if not already set - and this information will be available everywhere for Oracle session duration. You may alter the logic behind the login as you see fit, what's important is that you will always have the right information available every time and need not enforce the application to provide it every time it is needed.
-- Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer. "AcCeSsDeNiEd" <dillon_at_rm_accessdenied.darktech.org> wrote in message news:03ns2v0psu4lf1jkgh5v7b9g3evtfon78a_at_4ax.com...Received on Wed Jan 22 2003 - 03:39:49 CST
> Hee hee, thought so my luck was out.
>
> This is not a 'Date' column.
> It's a "Data Entry Employee Id".
>
> That means for the employee who is logged on to the application, his/her ID(number) will be
captured
> in this field.
>
>
> Thanks.
>
> On Wed, 22 Jan 2003 10:54:00 +0300, "Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote:
>
> >Your logic is a bit flawed here. First of all, :OLD represents the current contents
> >of the row being updated and it can't be altered, so
> >> The above works fine except that if the same employee did the data-entry
> >> for both the old and new records, we have a problem. It's gonna give out
> >> an error for a genuine update.
> >is not an issue as this is impossible. Secondly, you do not need to force the
> >*application* to update this column - you can always do it in the trigger
> >itself - just assign SYSDATE to this column, disregarding any input from the
> >application and you will always have correct row modification time regardless
> >if application provided it or not.
> >
> >"You should better learn your kanji before you may kumite."
>
>
> To e-mail me, remove "rm_"
 |
 |