Re: Another Trigger Query...

Michel Cadot wrote:
> <arijitchatterjee123_at_yahoo.co.in> a écrit dans le message de
> news:1103082634.656686.281310_at_f14g2000cwb.googlegroups.com...
> > Dear Faculties,
> > I have another basic question.I have two tables TAB1 and TAB1COPY
> > whenever I am doing any update or insert or delete on TAB1 that
> > automatically updating TAB1COPY.But I want to restrict direct
> > modification in TAB1COPY through SQL+ screen also.How I should
> > proceed?Please give me some clue.
> > Thanks in advance.
> > Regards
> > Arijit Chatterjee
> >
>
> You can find the module name ("SQL*Plus") in v$session.module.
>
> Regards
> Michel Cadot

Quite right, however I see at least two problems with this request. The first is properly identifying the session attempting to update TAB1COPY (nothing in SYS_CONTEXT9'USERENV',...) will work) and, should that succeed allowing data modifications to TAB1 through SQL*Plus. Let us presume for a moment the proper SID and SERIAL# values are obtained for the current session ( a difficult task, at best, since it's likely that one user account will be used to perform inserts, updates and deletes, giving rise to multiple SID,SERIAL# sets). The user attempts to update TAB1; the trigger on TAB1COPY fires, obtains the information this update is through SQL*Plus (since that is what the current session is using), disallows the update, causing the trigger on TAB1 to fail, resulting in failed updates to TAB1. This has, indirectly, disabled any updates to the source table (TAB1) for the desire to restrict updates to TAB1COPY to only those passed in by the trigger. I believe this requirement cannot be met.

David Fitzjarrell Received on Wed Dec 15 2004 - 10:59:14 CST