Des Kelly
asked on
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','TER MINAL');
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_CO NFIG_TAB$A UD (
ESTIMATED_MATERIAL_COST,PA RT_NO,
aud_action,aud_timestamp,a ud_user,au d_term,aud _program)
VALUES (
:new.ESTIMATED_MATERIAL_CO ST,
:new.PART_NO,
'INS',SYSDATE,USER,v_termi nal,v_modu le);
ELSIF UPDATING THEN
INSERT INTO AUDITMAN.INVENTORY_PART_CO NFIG_TAB$A UD (
ESTIMATED_MATERIAL_COST,PA RT_NO,
aud_action,aud_timestamp,a ud_user,au d_term,aud _program)
VALUES (
:new.ESTIMATED_MATERIAL_CO ST,
:new.PART_NO,
'UPD',SYSDATE,USER,v_termi nal,v_modu le);
ELSE
INSERT INTO AUDITMAN.INVENTORY_PART_CO NFIG_TAB$A UD (
ESTIMATED_MATERIAL_COST,PA RT_NO,
aud_action,aud_timestamp,a ud_user,au d_term,aud _program)
VALUES (
:old.ESTIMATED_MATERIAL_CO ST,
:old.PART_NO,
'DEL',SYSDATE,USER,v_termi nal,v_modu le);
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.
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','TER
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_CO
ESTIMATED_MATERIAL_COST,PA
aud_action,aud_timestamp,a
VALUES (
:new.ESTIMATED_MATERIAL_CO
:new.PART_NO,
'INS',SYSDATE,USER,v_termi
ELSIF UPDATING THEN
INSERT INTO AUDITMAN.INVENTORY_PART_CO
ESTIMATED_MATERIAL_COST,PA
aud_action,aud_timestamp,a
VALUES (
:new.ESTIMATED_MATERIAL_CO
:new.PART_NO,
'UPD',SYSDATE,USER,v_termi
ELSE
INSERT INTO AUDITMAN.INVENTORY_PART_CO
ESTIMATED_MATERIAL_COST,PA
aud_action,aud_timestamp,a
VALUES (
:old.ESTIMATED_MATERIAL_CO
:old.PART_NO,
'DEL',SYSDATE,USER,v_termi
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.
ASKER
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.
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.
OK . post error message too
ASKER
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*
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*
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];"
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];"
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.
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.
ASKER
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.
grant select on v_$SESSION to IFSAPP;
Same error occuring.
As I said will test on another environment.
Thanks,
Ger.
And I would believe that you are going to create the trigger on the same schema where the table lies(IFSAPP)
ASKER
Yes indeed.
Have to park this a while as I have an issue with Production Backups as I write.
Have to park this a while as I have an issue with Production Backups as I write.
>>Hi I have granted select to user directly on view: grant select on v_$SESSION to IFSAPP;
What about v_$mystat?
What about v_$mystat?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
grant insert on INVENTORY_PART_CONFIG_TAB$
ASKER
Hi Jonesone.
I'll give that a spin.
Will be tomorrow at this point as I have some production issues going on.
Thanks,
Ger.
I'll give that a spin.
Will be tomorrow at this point as I have some production issues going on.
Thanks,
Ger.
ASKER
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','HOS T');
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','SES SIONID');
IF INSERTING THEN
INSERT INTO AUDITMAN.INVENTORY_PART_CO NFIG_TAB$A UD (
ESTIMATED_MATERIAL_COST,PA RT_NO,
aud_action,aud_timestamp,a ud_user,au d_term,aud _program)
VALUES (
:new.ESTIMATED_MATERIAL_CO ST,
:new.PART_NO,
'INS',SYSDATE,USER,v_termi nal,v_modu le);
ELSIF UPDATING THEN
INSERT INTO AUDITMAN.INVENTORY_PART_CO NFIG_TAB$A UD (
ESTIMATED_MATERIAL_COST,PA RT_NO,
aud_action,aud_timestamp,a ud_user,au d_term,aud _program)
VALUES (
:new.ESTIMATED_MATERIAL_CO ST,
:new.PART_NO,
'UPD',SYSDATE,USER,v_termi nal,v_modu le);
ELSE
INSERT INTO AUDITMAN.INVENTORY_PART_CO NFIG_TAB$A UD (
ESTIMATED_MATERIAL_COST,PA RT_NO,
aud_action,aud_timestamp,a ud_user,au d_term,aud _program)
VALUES (
:old.ESTIMATED_MATERIAL_CO ST,
:old.PART_NO,
'DEL',SYSDATE,USER,v_termi nal,v_modu le);
END IF;
END IF;
END;
/
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','HOS
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','SES
IF INSERTING THEN
INSERT INTO AUDITMAN.INVENTORY_PART_CO
ESTIMATED_MATERIAL_COST,PA
aud_action,aud_timestamp,a
VALUES (
:new.ESTIMATED_MATERIAL_CO
:new.PART_NO,
'INS',SYSDATE,USER,v_termi
ELSIF UPDATING THEN
INSERT INTO AUDITMAN.INVENTORY_PART_CO
ESTIMATED_MATERIAL_COST,PA
aud_action,aud_timestamp,a
VALUES (
:new.ESTIMATED_MATERIAL_CO
:new.PART_NO,
'UPD',SYSDATE,USER,v_termi
ELSE
INSERT INTO AUDITMAN.INVENTORY_PART_CO
ESTIMATED_MATERIAL_COST,PA
aud_action,aud_timestamp,a
VALUES (
:old.ESTIMATED_MATERIAL_CO
:old.PART_NO,
'DEL',SYSDATE,USER,v_termi
END IF;
END IF;
END;
/
ASKER
Excellent assist - helped me get over the line with this.
Open in new window