maximum number of recursive SQL(%) level

[mike2000]

can someone help me with this maximum number of recursive SQL level, I guess the trigger cause the problem


CREATE OR REPLACE TRIGGER QTRIG_MTR_SPLIT_PR_CTR_BIUR
BEFORE INSERT
ON QCTRL_MTR_SPLIT
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
v_EXISTS VARCHAR2(2) :=0;
v_EXISTS_SPLIT VARCHAR2(2) :=0;

BEGIN
SELECT count(1)
INTO v_EXISTS_SPLIT
FROM qctrl_mtr_split
WHERE PLANT_NO=:NEW.PLANT_NO and MTR_CTR_NO=:NEW.MTR_CTR_NO and EFF_DT_FROM=:NEW.EFF_DT_FROM and EFF_DT_TO=:NEW.EFF_DT_TO and MTR_NO=:NEW.MTR_NO;

SELECT count(1)
INTO v_EXISTS
FROM qctrl_ctr_mtr_list
WHERE PLANT_NO=:NEW.PLANT_NO and mtr_ctr_no=:NEW.MTR_CTR_NO and CTR_NO=:NEW.RULING_CTR_NO AND ASSIGN_NO=:NEW.ASSIGN_NO
AND eff_dt_to = (SELECT max(eff_dt_to) FROM qctrl_ctr_mtr_list WHERE PLANT_NO=:NEW.PLANT_NO and mtr_ctr_no=:NEW.MTR_CTR_NO and CTR_NO=:NEW.RULING_CTR_NO AND ASSIGN_NO=:NEW.ASSIGN_NO);

IF v_EXISTS_SPLIT = 0 THEN
IF v_EXISTS = 1 THEN
INSERT INTO qctrl_mtr_split
VALUES (:NEW.plant_no,
:NEW.mtr_ctr_no,
:NEW.eff_dt_from,
:NEW.eff_dt_to,
:NEW.mtr_no,
:NEW.split,
:NEW.ruling_ctr_no,
:NEW.assign_no,
:NEW.tik_ind,
:NEW.operator_override,
:NEW.download_to_tips,
:NEW.download_reason_cd,
:NEW.user_defined1,
:NEW.user_defined2,
:NEW.comments,
:NEW.split_src_cd,
:NEW.s_decimal,
:NEW.seller_cd,
:NEW.remit_fl_cd,
:NEW.tax_class_cd,
:NEW.tax_combo_cd,
:NEW.eco_unit_cd,
:NEW.USER_ID,
:NEW.UPDT_DT,
:NEW.hist_idx,
:NEW.ctr_category_cd);
ELSE
raise_application_error( -20004, 'Plant.');
END IF;
ELSE
IF v_EXISTS = 1 THEN
UPDATE qctrl_mtr_split
SET plant_no=:NEW.plant_no,
mtr_ctr_no=:NEW.mtr_ctr_no,
eff_dt_from=:NEW.eff_dt_from,
eff_dt_to=:NEW.eff_dt_to,
mtr_no=:NEW.mtr_no,
split=:NEW.split,
ruling_ctr_no=:NEW.ruling_ctr_no,
assign_no=:NEW.assign_no,
tik_ind=:NEW.tik_ind,
operator_override=:NEW.operator_override,
download_to_tips=:NEW.download_to_tips,
download_reason_cd=:NEW.download_reason_cd,
user_defined1=:NEW.user_defined1,
user_defined2=:NEW.user_defined2,
comments=:NEW.comments,
split_src_cd=:NEW.split_src_cd,
s_decimal=:NEW.s_decimal,
seller_cd=:NEW.seller_cd,
remit_fl_cd=:NEW.remit_fl_cd,
tax_class_cd=:NEW.tax_class_cd,
tax_combo_cd=:NEW.tax_combo_cd,
eco_unit_cd=:NEW.eco_unit_cd,
USER_ID=:NEW.USER_ID,
UPDT_DT=:NEW.UPDT_DT,
hist_idx=:NEW.hist_idx,
ctr_category_cd=:NEW.ctr_category_cd
WHERE PLANT_NO=:NEW.PLANT_NO and MTR_CTR_NO=:NEW.MTR_CTR_NO and EFF_DT_FROM=:NEW.EFF_DT_FROM and EFF_DT_TO=:NEW.EFF_DT_TO and MTR_NO=:NEW.MTR_NO;
ELSE
raise_application_error( -20004, 'Plant.');
END IF;
END IF;
END;

/

[mike2000]

any advises on this post???

[dharma]

Use a Instead of Trigger on a View created on that table.

HTH
-dharma