Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

Oracle - Trying to build a trigger to capture changes made to data field using SQL*Plus & SQLDeveloper

Hi I'm trying to get the following trigger to compile:
CREATE OR REPLACE TRIGGER INV_PART_CONF_TAB$AUDTRG
 AFTER INSERT OR DELETE OR UPDATE ON INVENTORY_PART_CONFIG_TAB
 FOR EACH ROW
 DECLARE
    v_currModule VARCHAR2(50)  :='SQL Developer';
    v_operation  VARCHAR2(10) := NULL;
    v_terminal   VARCHAR2(30) := sys_context('USERENV','TERMINAL');
    v_module     VARCHAR2(30);  
 
  BEGIN

 SELECT module INTO v_currModule FROM v$session WHERE sid=(select sid from v$mystat where rownum=1);
 
  IF (v_currModule = 'SQL*Plus' OR v_currModule = 'SQL Developer') AND (USER = 'IFSAPP' OR USER = 'IFSINFO')
  THEN
   
    select terminal, program into v_terminal, v_module from v$session;
                                                             
    IF INSERTING THEN
            INSERT INTO AUDITMAN.INVENTORY_PART_CONFIG_TAB$AUD (                                                          
            ESTIMATED_MATERIAL_COST,PART_NO,                                                                          
            aud_action,aud_timestamp,aud_user,aud_term,aud_program)
            VALUES (                                    
           :new.ESTIMATED_MATERIAL_COST,
           :new.PART_NO,                                                                      
           'INS',SYSDATE,USER,v_terminal,v_module);
    ELSIF UPDATING THEN
           INSERT INTO AUDITMAN.INVENTORY_PART_CONFIG_TAB$AUD (                                                          
           ESTIMATED_MATERIAL_COST,PART_NO,                                                                          
           aud_action,aud_timestamp,aud_user,aud_term,aud_program)
           VALUES (                                    
           :new.ESTIMATED_MATERIAL_COST,
           :new.PART_NO,                                                                      
           'UPD',SYSDATE,USER,v_terminal,v_module);
    ELSE
       INSERT INTO AUDITMAN.INVENTORY_PART_CONFIG_TAB$AUD (                                                          
       ESTIMATED_MATERIAL_COST,PART_NO,                                                                          
           aud_action,aud_timestamp,aud_user,aud_term,aud_program)
           VALUES (                                    
           :old.ESTIMATED_MATERIAL_COST,
           :old.PART_NO,
           'DEL',SYSDATE,USER,v_terminal,v_module);
    END IF;
END IF;
END;


/
the line which is failing is:
 SELECT module INTO v_currModule FROM v$session WHERE sid=(select sid from v$mystat where rownum=1);

When I run:
 SELECT module FROM v$session WHERE  sid = (select sid from v$mystat where rownum=1);
In isolation I gert result.

I have tried substituting the following line:
SELECT module INTO v_currModule FROM v$session WHERE audsid=userenv('SESSIONID');


This compiles but trigger errors because of multiple lines returned.


All suggestions welcomed.
0
itfocus
Asked:
itfocus
  • 7
  • 3
  • 2
  • +2
1 Solution
 
Wasim Akram ShaikCommented:
not sure the error lines,, but do this.. it should help.. the error, i think is because of sid not being passed in one of the queries


CREATE OR REPLACE TRIGGER norkom56_sit_cr.alert_status_trig
 AFTER INSERT OR DELETE OR UPDATE ON norkom56_sit_cr.alert_status
 FOR EACH ROW 
 DECLARE 
    v_currModule VARCHAR2(50)  :='SQL Developer';
    v_operation  VARCHAR2(10) := NULL;
    v_terminal   VARCHAR2(30) := sys_context('USERENV','TERMINAL');
    v_module     VARCHAR2(30);  
    v_sid varchar2(100);
    
    BEGIN

 SELECT sid,module INTO v_sid,v_currModule FROM v$session WHERE sid=(select distinct sid from v$mystat where rownum<2);
  
  IF (v_currModule = 'SQL*Plus' OR v_currModule = 'SQL Developer') AND (USER = 'IFSAPP' OR USER = 'IFSINFO') 
  THEN
   
    select terminal, program into v_terminal, v_module from v$session where sid=v_sid;
 IF INSERTING THEN 
           INSERT INTO AUDITMAN.INVENTORY_PART_CONFIG_TAB$AUD (                                                          
            ESTIMATED_MATERIAL_COST,PART_NO,                                                                          
            aud_action,aud_timestamp,aud_user,aud_term,aud_program) 
            VALUES (                                     
           :new.ESTIMATED_MATERIAL_COST,
           :new.PART_NO,                                                                      
           'INS',SYSDATE,USER,v_terminal,v_module); 
           NULL;
    ELSIF UPDATING THEN 
          INSERT INTO AUDITMAN.INVENTORY_PART_CONFIG_TAB$AUD (                                                          
           ESTIMATED_MATERIAL_COST,PART_NO,                                                                           
           aud_action,aud_timestamp,aud_user,aud_term,aud_program) 
           VALUES (                                     
           :new.ESTIMATED_MATERIAL_COST,
           :new.PART_NO,                                                                      
           'UPD',SYSDATE,USER,v_terminal,v_module); 
           NULL;
           ELSE 
       INSERT INTO AUDITMAN.INVENTORY_PART_CONFIG_TAB$AUD (                                                          
       ESTIMATED_MATERIAL_COST,PART_NO,                                                                           
           aud_action,aud_timestamp,aud_user,aud_term,aud_program) 
           VALUES (                                     
           :old.ESTIMATED_MATERIAL_COST,
           :old.PART_NO,
           'DEL',SYSDATE,USER,v_terminal,v_module); 
           NULL;
    END IF; 
END IF;
END;

Open in new window

0
 
itfocusAuthor Commented:
HI Wasim,
Appreciate your input.
Still same problem though.
ran query:
 SELECT sid,module FROM v$session WHERE sid=(select distinct sid from v$mystat where rownum<2);
successufully.
I am on Oracle 9i - I wonder is it a version issue.
I will test on a later version tomorrow.

Thanks,

Ger.
0
 
Wasim Akram ShaikCommented:
OK . post error message too
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
itfocusAuthor Commented:
Error:
SQL> show error
Errors for TRIGGER INV_PART_CONF_TAB$AUDTRG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/2     PL/SQL: SQL Statement ignored
10/95    PL/SQL: ORA-00942: table or view does not exist
SQL> list 10
 10*
0
 
Mark GeerlingsDatabase AdministratorCommented:
This error message is not always the whole truth: "ORA-00942: table or view does not exist".

Sometimes, that error really means: "The table exists, but you don't have permission to see it".  If that is the problem, log in as the owner of the table (or view) then:
grant select on [table or view name] to [user who needs permission]

And, be aware that many of the "v$objects" in Oracle are actually synonyms to views named like: "v_$object" that are owned by SYS.  In those cases, you need to "connect as sysdba" then "grant select on v_$... to [user];"
0
 
slightwv (䄆 Netminder) Commented:
Another possible issue:
Grants in code like triggers and procedures MUST be explicitly granted and CANNOT come from a ROLE.

You can select from v$ views likely because you were granted a ROLE like DBA.  When you create stored code, you no longer have access to the view.

You need to grant select as posted above to the user not the role.
0
 
itfocusAuthor Commented:
Hi I have granted select to user directly on view:
grant select on v_$SESSION to IFSAPP;

Same error occuring.

As I said will test on another environment.

Thanks,

Ger.
0
 
Wasim Akram ShaikCommented:
And I would believe that you are going to create the trigger on the same schema where the table lies(IFSAPP)
0
 
itfocusAuthor Commented:
Yes indeed.
Have to park this a while as I have an issue with Production Backups as I write.
0
 
slightwv (䄆 Netminder) Commented:
>>Hi I have granted select to user directly on view: grant select on v_$SESSION to IFSAPP;

What about v_$mystat?
0
 
johnsoneSenior Oracle DBACommented:
I made some modifications to the original to get rid of as many system view selects as possible.
CREATE OR REPLACE TRIGGER INV_PART_CONF_TAB$AUDTRG
 AFTER INSERT OR DELETE OR UPDATE ON INVENTORY_PART_CONFIG_TAB
 FOR EACH ROW 
 DECLARE 
    v_currModule VARCHAR2(50) := sys_context('USERENV','MODULE');
    v_operation  VARCHAR2(10) := NULL;
    v_terminal   VARCHAR2(30) := sys_context('USERENV','TERMINAL');
    v_module     VARCHAR2(30);  
  
  BEGIN

  IF (v_currModule = 'SQL*Plus' OR v_currModule = 'SQL Developer') AND (USER = 'IFSAPP' OR USER = 'IFSINFO') 
  THEN
   
    select program into v_module from v$session where audsid = sys_context('USERENV','SESSIONID');
                                                              
    IF INSERTING THEN 
            INSERT INTO AUDITMAN.INVENTORY_PART_CONFIG_TAB$AUD (                                                          
            ESTIMATED_MATERIAL_COST,PART_NO,                                                                          
            aud_action,aud_timestamp,aud_user,aud_term,aud_program) 
            VALUES (                                     
           :new.ESTIMATED_MATERIAL_COST,
           :new.PART_NO,                                                                      
           'INS',SYSDATE,USER,v_terminal,v_module); 
    ELSIF UPDATING THEN 
           INSERT INTO AUDITMAN.INVENTORY_PART_CONFIG_TAB$AUD (                                                          
           ESTIMATED_MATERIAL_COST,PART_NO,                                                                           
           aud_action,aud_timestamp,aud_user,aud_term,aud_program) 
           VALUES (                                     
           :new.ESTIMATED_MATERIAL_COST,
           :new.PART_NO,                                                                      
           'UPD',SYSDATE,USER,v_terminal,v_module); 
    ELSE 
       INSERT INTO AUDITMAN.INVENTORY_PART_CONFIG_TAB$AUD (                                                          
       ESTIMATED_MATERIAL_COST,PART_NO,                                                                           
           aud_action,aud_timestamp,aud_user,aud_term,aud_program) 
           VALUES (                                     
           :old.ESTIMATED_MATERIAL_COST,
           :old.PART_NO,
           'DEL',SYSDATE,USER,v_terminal,v_module); 
    END IF; 
END IF;
END;
/

Open in new window

This should only require the following grant (assuming that IFSAPP owns the procedure):

grant select on v_$SESSION to IFSAPP;

You can get everything you need with the exception of PROGRAM from SYS_CONTEXT.  If you can live without that field and use module instead, then you can do it all with SYS_CONTEXT.  Not sure what shows in that field with SQL Developer, but with SQL*Plus the only added information in the PROGRAM field is the inclusion of the host you are connecting from, which you can get from SYS_CONTEXT('USERENV','HOST') and avoid the system views altogether.
0
 
johnsoneSenior Oracle DBACommented:
And are we sure the permission error is on the V$ tables now?  There are insert statements into tables owned by another user.  Has permission been granted to IFSAPP on those tables directly (i.e. not through a role).  Specifically this grant (run as AUDITMAN):

grant insert on INVENTORY_PART_CONFIG_TAB$AUD to ifsapp;
0
 
itfocusAuthor Commented:
Hi Jonesone.
I'll give that a spin.
Will be tomorrow at this point as I have some production issues going on.

Thanks,

Ger.
0
 
itfocusAuthor Commented:
Hi Jonesone,
Got it working.
Many thanks Good Man.
I had to make a couple of minor changes.
Basically I was not getting into v_currModule also the terminal was coming back as unknown:

This is working code - changes underlined:

CREATE OR REPLACE TRIGGER INV_PART_CONF_TAB$AUDTRG
 AFTER INSERT OR DELETE OR UPDATE ON INVENTORY_PART_CONFIG_TAB
 FOR EACH ROW
 DECLARE
   v_currModule VARCHAR2(50) := NULL;
    v_operation  VARCHAR2(10) := NULL;
    v_terminal   VARCHAR2(30) := SYS_CONTEXT('USERENV','HOST');
    v_module     VARCHAR2(30);  
 
  BEGIN
  select module into  v_currModule from v$session where audsid = userenv('sessionid') ;
  IF (v_currModule = 'SQL*Plus' OR v_currModule = 'SQL Developer') AND (USER = 'IFSAPP' OR USER = 'IFSINFO')
  /*  IF (v_currModule = 'SQL*Plus') AND (USER = 'IFSAPP' OR USER = 'IFSINFO')  */
  THEN
   
    select program into v_module from v$session where audsid = sys_context('USERENV','SESSIONID');
                                                             
    IF INSERTING THEN
            INSERT INTO AUDITMAN.INVENTORY_PART_CONFIG_TAB$AUD (                                                          
            ESTIMATED_MATERIAL_COST,PART_NO,                                                                          
            aud_action,aud_timestamp,aud_user,aud_term,aud_program)
            VALUES (                                    
           :new.ESTIMATED_MATERIAL_COST,
           :new.PART_NO,                                                                      
           'INS',SYSDATE,USER,v_terminal,v_module);
    ELSIF UPDATING THEN
           INSERT INTO AUDITMAN.INVENTORY_PART_CONFIG_TAB$AUD (                                                          
           ESTIMATED_MATERIAL_COST,PART_NO,                                                                          
           aud_action,aud_timestamp,aud_user,aud_term,aud_program)
           VALUES (                                    
           :new.ESTIMATED_MATERIAL_COST,
           :new.PART_NO,                                                                      
           'UPD',SYSDATE,USER,v_terminal,v_module);
    ELSE
       INSERT INTO AUDITMAN.INVENTORY_PART_CONFIG_TAB$AUD (                                                          
       ESTIMATED_MATERIAL_COST,PART_NO,                                                                          
           aud_action,aud_timestamp,aud_user,aud_term,aud_program)
           VALUES (                                    
           :old.ESTIMATED_MATERIAL_COST,
           :old.PART_NO,
           'DEL',SYSDATE,USER,v_terminal,v_module);
    END IF;
END IF;
END;
/
0
 
itfocusAuthor Commented:
Excellent assist - helped me get over the line with this.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now