The Oracle trigger table has changed. The trigger can't read it. (must see)
The reason is that the updated table and the read table are the same table.
CREATE or replace TRIGGER T_userupdateT BEFORE update ON T_user REFERENCING OLD AS old NEW AS N_ROW FOR EACH ROW DECLARE U_xtfidemp1 varchar (36); u_xtempcode1 varchar (20); u_xtempcodeCount int:=0; U_xtfidempCount int:=0; u_id1 int:=0; BEGIN upright xtfidemp1; u_xtempcodeCount int:=0; U_xtfidempCount int:=0; u_id1 int:=0; BEGIN uprixtfidemp1; uprixtempcode1; u_id1:=:N_ROW.u_id Select count (u_xtempcode) into u_xtempcodeCount from eas.T_user where u_xtempcode is not null and u_xtempcode=u_xtempcode1 and UsingiduCode ID 1; select count (U_xtfidemp) into U_xtfidempCount from eas.T_user where U_xtfidemp is not null and U_xtfidemp=U_xtfidemp1 and UsingiduduCode ID 1; IF u_xtempcodeCount > 0 or U_xtfidempCount > 0 THEN RAISE_APPLICATION_ERROR (- 20001, 'eas.T_user uprixtempcode', 'error and repetition when updating data in' eas.T_user uprixtemp'); END IF End
When an error occurs, it is because the trigger is on T_userupdateT and on T_user, and the T_user is read inside the trigger, so there is an error.
Modify as follows
CREATE or replace TRIGGER T_userupdateT BEFORE update ON T_user REFERENCING OLD AS old NEW AS N_ROW FOR EACH ROW DECLARE U_xtfidemp1 varchar (36); u_xtempcode1 varchar (20); u_xtempcodeCount int:=0; U_xtfidempCount int:=0; u_id1 int:=0; PRAGMA AUTONOMOUS_TRANSACTION;BEGIN upright xtfidemp1; u_xtempcodeCount int:=0; U_xtfidempCount int:=0; u_id1 int:=0; PRAGMA AUTONOMOUS_TRANSACTION;BEGIN uprixtfidemp1; uprixtempcode1; u_id1:=:N_ROW.u_id Select count (u_xtempcode) into u_xtempcodeCount from eas.T_user where u_xtempcode is not null and u_xtempcode=u_xtempcode1 and UsingiduCode ID 1; select count (U_xtfidemp) into U_xtfidempCount from eas.T_user where U_xtfidemp is not null and U_xtfidemp=U_xtfidemp1 and UsingiduduCode ID 1; IF u_xtempcodeCount > 0 or U_xtfidempCount > 0 THEN RAISE_APPLICATION_ERROR (- 20001, 'eas.T_user uprixtempcode', 'error and repetition when updating data in' eas.T_user uprixtemp'); END IF COMMIT; end
There are two more sentences of PRAGMA AUTONOMOUS_TRANSACTION;COMMIT;.
The above Oracle trigger table has changed. The solution that the trigger can not read it (must see) is all the content shared by the editor. I hope I can give you a reference and support it.
