Re: Another Trigger Query...

Michel Cadot wrote:
> <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

Nicely done, however I read the original post as the OP wanting to restrict direct modification of TAB1COPY from SQL*Plus. If that is the case then it's a simple matter of changing your logic in the last trigger:

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 not allowed from SQL*Plus');
end if;
end;
/

I have not tried this, either. and I think the query in the last trigger could return more than one row.
It's still a well thought out solution possibility.

David Fitzjarrell Received on Wed Dec 15 2004 - 15:57:17 CST