I manipulate cumulative balance as AMTBAL for each row using procedure during insert transaction in tbl_transaction table.
In-site Procedure condition is: If DR_CR ='CR' then AMTBAL :=AMTBAL+AMOUNT else AMTBAL :=AMTBAL-AMOUNT end if; which is working perfectly.
But If I want to update one record amount then cumulative balance i.e AMTBAL will update using compound trigger. But during update I am getting error from trigger.
--- Update Query
update TBL_TRANSACTION set AMOUNT=900 where traceno=2;
commit;
Error at line 1
ORA-00036: maximum number of recursive SQL levels (50) exceeded
It should update traceno 2 & 3 AMTBAL 600 & 1800 respectively.
(
TRACENO NUMBER(16) NOT NULL,
USER_CODE NUMBER(10) NOT NULL,
ACCOUNTNO VARCHAR2(15 CHAR) NOT NULL,
TDATE DATE NOT NULL,
TRN_CODE VARCHAR2(2 CHAR) NOT NULL,
DR_CR VARCHAR2(2 CHAR) NOT NULL,
AMOUNT NUMBER(18,4) NOT NULL,
AMTBAL NUMBER(18,4) DEFAULT ON NULL 0 NOT NULL,
CURR_CODE VARCHAR2(4 CHAR) ,
BRANCH_CODE VARCHAR2(4 CHAR) ,
ADJUST_TRACENO NUMBER(16),
ADJUST_AMT NUMBER(18,4) DEFAULT ON NULL 0 NOT NULL,
TSYSDATE TIMESTAMP(4) DEFAULT ON NULL SYSTIMESTAMP NOT NULL,
AUTHO_SYS_DATE TIMESTAMP(4) DEFAULT SYSDATE
);
CREATE UNIQUE INDEX PK_tbl_transaction ON tbl_transaction(TRACENO);
Trigger:
CREATE GLOBAL TEMPORARY TABLE INSERTED_tbl_transaction AS SELECT * FROM tbl_transaction WHERE 1=2;
/
CREATE GLOBAL TEMPORARY TABLE DELETED_tbl_transaction AS SELECT * FROM tbl_transaction WHERE 1=2;
/
CREATE OR REPLACE TRIGGER Trig_update_tbl_transaction
FOR UPDATE
ON tbl_transaction
COMPOUND TRIGGER
AFTER EACH ROW IS
BEGIN
INSERT INTO INSERTED_tbl_transaction (TRACENO, USER_CODE, ACCOUNTNO, TDATE, TRN_CODE, DR_CR, AMOUNT, AMTBAL, CURR_CODE, BRANCH_CODE, ADJUST_TRACENO, ADJUST_AMT, TSYSDATE, AUTHO_SYS_DATE)
VALUES (:NEW.TRACENO, :NEW.USER_CODE, :NEW.ACCOUNTNO, :NEW.TDATE, :NEW.TRN_CODE, :NEW.DR_CR, :NEW.AMOUNT, :NEW.AMTBAL, :NEW.CURR_CODE, :NEW.BRANCH_CODE, :NEW.ADJUST_TRACENO, :NEW.ADJUST_AMT, :NEW.TSYSDATE, :NEW.AUTHO_SYS_DATE);
INSERT INTO DELETED_tbl_transaction (TRACENO, USER_CODE, ACCOUNTNO, TDATE, TRN_CODE, DR_CR, AMOUNT, AMTBAL, CURR_CODE, BRANCH_CODE, ADJUST_TRACENO, ADJUST_AMT, TSYSDATE, AUTHO_SYS_DATE)
VALUES (:OLD.TRACENO, :OLD.USER_CODE, :OLD.ACCOUNTNO, :OLD.TDATE, :OLD.TRN_CODE, :OLD.DR_CR, :OLD.AMOUNT, :OLD.AMTBAL, :OLD.CURR_CODE, :OLD.BRANCH_CODE, :OLD.ADJUST_TRACENO, :OLD.ADJUST_AMT, :OLD.TSYSDATE, :OLD.AUTHO_SYS_DATE);
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
DECLARE
V_insPostedYN CHAR(1);
V_delPostedYN CHAR(1);
V_insdr_cr CHAR(2);
V_deldr_cr CHAR(2);
V_insaccountno CHAR(15);
V_instraceno NUMBER(16,0);
V_insbal_tkold NUMBER(14,2);
V_insbal_tknew NUMBER(14,2);
V_insbal_fcold NUMBER(14,2);
V_insbal_fcnew NUMBER(14,2);
V_adjust_traceno NUMBER(16,0);
V_ins_trn_code CHAR(2);
V_Del_trn_code CHAR(2);
V_ins_Amount NUMBER(13,2);
V_del_Amount NUMBER(13,2);
V_tdate DATE;
V_atype_code VARCHAR2(6);
--<<TP Update
V_tpyn CHAR(1);
BEGIN
BEGIN
SELECT
INSERTED_tbl_transaction.dr_cr ,
INSERTED_tbl_transaction.accountno ,
INSERTED_tbl_transaction.traceno ,
INSERTED_tbl_transaction.adjust_traceno ,
INSERTED_tbl_transaction.trn_code ,
INSERTED_tbl_transaction.Amount ,
INSERTED_tbl_transaction.tdate
INTO
V_insdr_cr,
V_insaccountno,
V_instraceno,
V_adjust_traceno,
V_Ins_trn_code,
V_ins_Amount,
V_tdate
FROM INSERTED_tbl_transaction ;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
BEGIN
SELECT
DELETED_tbl_transaction.dr_cr ,
DELETED_tbl_transaction.trn_code ,
DELETED_tbl_transaction.Amount
INTO
V_deldr_cr,
V_Del_trn_code,
V_del_Amount
FROM DELETED_tbl_transaction ;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
------- Start Cumulative Balance update
IF ( V_insdr_cr = 'CR' ) THEN
UPDATE tbl_transaction
SET amtbal = amtbal - V_del_Amount + V_ins_Amount
WHERE accountno = V_insaccountno
AND traceno >= V_instraceno
AND tdate = V_tdate;
ELSE
UPDATE tbl_transaction
SET amtbal = amtbal + V_del_Amount - V_ins_Amount
WHERE accountno = V_insaccountno
AND traceno >= V_instraceno
AND tdate = V_tdate;
END IF;
--End Cumulative Balance update
IF ( V_adjust_traceno > 0 ) THEN
BEGIN
MERGE INTO tbl_transaction
USING (SELECT tbl_transaction.ROWID row_id, tbl_transaction.adjust_amt - DELETED_tbl_transaction.amount + INSERTED_tbl_transaction.amount AS adjust_amt
FROM tbl_transaction ,INSERTED_tbl_transaction ,DELETED_tbl_transaction
WHERE INSERTED_tbl_transaction.traceno = DELETED_tbl_transaction.traceno
AND tbl_transaction.traceno = V_adjust_traceno) src
ON ( tbl_transaction.ROWID = src.row_id )
WHEN MATCHED THEN UPDATE SET adjust_amt = src.adjust_amt;
END;
END IF;
END;
END AFTER STATEMENT;
END;
/
-- Sample Insert data
INSERT INTO DBO.TBL_TRANSACTION (TRACENO, USER_CODE, ACCOUNTNO, TDATE, TRN_CODE, DR_CR, AMOUNT, AMTBAL, CURR_CODE, BRANCH_CODE, ADJUST_TRACENO, ADJUST_AMT)
VALUES ( 1,101,'0002','06-JAN-2020','01','CR',1500,1500,'BDT','0005',0,0);
INSERT INTO DBO.TBL_TRANSACTION (TRACENO, USER_CODE, ACCOUNTNO, TDATE, TRN_CODE, DR_CR, AMOUNT, AMTBAL, CURR_CODE, BRANCH_CODE, ADJUST_TRACENO, ADJUST_AMT)
VALUES ( 2,101,'0002','06-JAN-2020','01','DR',700,800,'BDT','0005',0,0);
INSERT INTO DBO.TBL_TRANSACTION (TRACENO, USER_CODE, ACCOUNTNO, TDATE, TRN_CODE, DR_CR, AMOUNT, AMTBAL, CURR_CODE, BRANCH_CODE, ADJUST_TRACENO, ADJUST_AMT)
VALUES ( 3,101,'0002','06-JAN-2020','01','CR',1200,2000,'BDT','0005',0,0);
commit;
--- Update Query
update TBL_TRANSACTION set AMOUNT=900 where traceno=2;
commit;
Error at line 1
ORA-00036: maximum number of recursive SQL levels (50) exceeded
Please help.
Thanks
Solaiman