troubleshooting Question

Oracle/Apex - Mutating Trigger

Avatar of hej613
hej613 asked on
Oracle Database
5 Comments1 Solution873 ViewsLast Modified:
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)  
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros