Oracle/Apex - Mutating Trigger

Afternoon experts, I'm trying to create a trigger that takes the value when someone inserts a person into our apex_access_control table to insert specific values into a separate table that will have the users "Agency" that will allow them access to agency specific information.

When I run my code, I get the following Error:

Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-04091: table DEVADSIRPT.APEX_ACCESS_CONTROL is mutating, trigger/function may not see it ORA-06512: at "DEVADSIRPT.TRG_AFTER_INSERT", line 5 ORA-04088: error during execution of trigger 'DEVADSIRPT.TRG_AFTER_INSERT', insert into "DEVADSIRPT"."APEX_ACCESS_CONTROL" ( "ID", "ADMIN_USERNAME", "ADMIN_PRIVILEGES", "SETUP_ID") values ( :b1, :b2, :b3, :b4)

Please let me know what other information you would need

create or replace TRIGGER TRG_AFTER_INSERT
AFTER INSERT
ON APEX_ACCESS_CONTROL
FOR EACH ROW

DECLARE
V_AGENCY VARCHAR2(10);

BEGIN
SELECT UA_AGENCY
INTO V_AGENCY
FROM APEX_ACCESS_CONTROL A , USER_ACCESS B
WHERE A.ID = B.UA_USER_ID
AND A.ADMIN_USERNAME = V('APP_USER');


INSERT INTO TEST_TRIGGER
(TT_USERID,
TT_NAME,
TT_AGENCY)
VALUES(
:NEW.ID,
:NEW.ADMIN_USERNAME,
V_AGENCY);
END TRG_AFTER_INSERT;

TABLE apex_access_control
 Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER                      
 ADMIN_USERNAME                                     VARCHAR2(255)               
 ADMIN_PRIVILEGES                                   VARCHAR2(255)               
 SETUP_ID                                           NUMBER                      
 CREATED_BY                                         VARCHAR2(255)               
 CREATED_ON                                         DATE                        
 UPDATED_ON                                         DATE                        
 UPDATED_BY                                         VARCHAR2(255)               
 VIEW_EXT                                           VARCHAR2(1) 

TABLE user_access
 Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 UA_ID                                     NOT NULL NUMBER                      
 UA_USER_ID                                         NUMBER                      
 UA_AGENCY                                          VARCHAR2(2)                 
 UA_VIEW_ADMIN                                      VARCHAR2(1)                 
 UA_VIEW_MAINT                                      VARCHAR2(1)                 
 UA_VIEW_DATAENTRY                                  VARCHAR2(1)                 
 UA_VIEW_EXTRACT                                    VARCHAR2(1)                 
 UA_VIEW_RIM                                        VARCHAR2(1)                 
 UA_VIEW_ACC                                        VARCHAR2(1)                 
 UA_VIEW_JMS                                        VARCHAR2(1)                 
 UA_VIEW_WAR                                        VARCHAR2(1)                 
 UA_VIEW_TCC                                        VARCHAR2(1)                 
 UA_CREATE_USER                                     VARCHAR2(1)                 
 UA_VIEW_FLD                                        VARCHAR2(1)  

Open in new window

LVL 1
hej613Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this part is the issue:
SELECT UA_AGENCY
INTO V_AGENCY
FROM APEX_ACCESS_CONTROL A , USER_ACCESS B
WHERE A.ID = B.UA_USER_ID
AND A.ADMIN_USERNAME = V('APP_USER');

Open in new window


you cannot largely access from the same table your trigger is on (except the inserted rowitself)
you could do this, not sure if this is matching the business needs:
SELECT b.UA_AGENCY
INTO V_AGENCY
FROM USER_ACCESS B
WHERE B.UA_USER_ID = :NEW.ID ;

Open in new window

0
hej613Author Commented:
Thanks Angel - That didn't work quite right either...

I would like to set the Agency to the same agency as the user doing the insert.

So if the user logged in was "PD" agency, it would automatically carry that agency over from their user_access.ua_agency entry to the "new" entry.
0
hej613Author Commented:
Would a better solution maybe call a procedure or two from the trigger to do what I'm trying to accomplish?

Thanks,
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
There are several approaches:

http://www.oracle-base.com/articles/9i/mutating-table-exceptions.php

http://www.oracle-base.com/articles/11g/trigger-enhancements-11gr1.php

But whatever you choose: DO NOT MARK YOUR TRIGGER as "autonomous transaction"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alexander Eßer [Alex140181]Software DeveloperCommented:
is that a C grade???? why that penalty?!?!?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.