Re: Another Trigger Query...

<fitzjarrell_at_cox.net> a écrit dans le message de news:1103129839.047854.188910_at_z14g2000cwz.googlegroups.com...

| 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

I don't try it but i think something like this should work:

create or replace package mypkg
is
  intab1mod boolean;
end;
/

create or replace trigger mytab1trgbef1
before insert or update or delete on tab1 begin

   mypkg.intab1mod := true;
end;
/

create or replace trigger mytab1trgbef2
before insert or update or delete on tab1 for each row
begin

   if inserting then insert into tab1copy ...    elsif updating then ...
   elsif deleting then ...
   end if;
end;
/

create or replace trigger mytab1trgaft
after insert or update or delete on tab1 begin

   mypkg.intab1mod := false;
end;
/

create or replace trigger mycopytrgaft
after insert or update or delete on tab1 declare

     curmodule v$session.module%type;
begin

   select module into curmodule
   from v$session where audsid=sys_context('userenv','sessionid');    if not mypkg.intab1mod and curmodule != 'SQL*Plus' then      raise_application_error (-20000, 'Direct modification on tab1copy only allowed from SQL*Plus');    end if;
end;
/

Regards
Michel Cadot Received on Wed Dec 15 2004 - 13:58:47 CST