Re: Another Trigger Query...

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 15 Dec 2004 23:26:40 +0100
Message-ID: <41c0b90f$0$11558$626a14ce@news.free.fr>

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

| 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

|

Yes, I misunderstood the OP question.

There surely are some tuning to do.
The query will return several rows if this is executed with SYS (as SYS always has audsid=0) or if this is a parallel DML (I think all the PQ processes have the same audsid than the coordinator but i don't check it) but is there multiple fires of the tab1copy trigger in case of parallel DML?

Btw, there is a mistake in the last trigger, you should read "create or replace trigger mycopytrgaft
after insert or update or delete on tab1copy" and not "on tabl", (too fast copy and paste).

Michel Cadot Received on Wed Dec 15 2004 - 16:26:40 CST