Avatar of hej613
hej613 asked on

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

Oracle Database

Avatar of undefined
Last Comment
Alex [***Alex140181***]

8/22/2022 - Mon
SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
hej613

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.
ASKER
hej613

Would a better solution maybe call a procedure or two from the trigger to do what I'm trying to accomplish?

Thanks,
ASKER CERTIFIED SOLUTION
Alex [***Alex140181***]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Alex [***Alex140181***]

is that a C grade???? why that penalty?!?!?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy