Solved

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

Posted on 2015-02-03
15
407 Views
Last Modified: 2015-02-06
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
Comment
Question by:itfocus
  • 7
  • 3
  • 2
  • +2
15 Comments
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40586120
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
 

Author Comment

by:itfocus
ID: 40586791
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
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40586844
OK . post error message too
0
 

Author Comment

by:itfocus
ID: 40586955
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40587514
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40587519
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
 

Author Comment

by:itfocus
ID: 40588286
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40588449
And I would believe that you are going to create the trigger on the same schema where the table lies(IFSAPP)
0
 

Author Comment

by:itfocus
ID: 40588453
Yes indeed.
Have to park this a while as I have an issue with Production Backups as I write.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40588516
>>Hi I have granted select to user directly on view: grant select on v_$SESSION to IFSAPP;

What about v_$mystat?
0
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 40588708
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
 
LVL 34

Expert Comment

by:johnsone
ID: 40588723
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
 

Author Comment

by:itfocus
ID: 40591540
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
 

Author Comment

by:itfocus
ID: 40593783
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
 

Author Closing Comment

by:itfocus
ID: 40593810
Excellent assist - helped me get over the line with this.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now