Link to home
Start Free TrialLog in
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

SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hej613
hej613

ASKER

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.
Avatar of hej613

ASKER

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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
is that a C grade???? why that penalty?!?!?