Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    7

    Unanswered: Confusing trigger!!

    CREATE OR REPLACE TRIGGER samptry
    AFTER UPDATE OF balance ON ACCOUNT FOR EACH ROW
    DECLARE
    minbal ACCOUNT_TYPE.MINIMUM_BALANCE%TYPE;
    tmpbal NUMBER(15,2);
    tmpamt NUMBER(15,2);
    tmpamt1 NUMBER(15,2);
    amt1 NUMBER(15,2);
    ac_no QUANTUM.ac_no%TYPE;
    auto_sweep QUANTUM.auto_sweep%TYPE;
    reverse_sweep QUANTUM.reverse_sweep%TYPE;
    amt_to_transfd QUANTUM.amt_to_transfd%TYPE;
    balance_above1 QUANTUM.balance_above%TYPE;
    aswp_enddate QUANTUM.aswp_enddate%TYPE;
    rswp_enddate QUANTUM.rswp_enddate%TYPE;
    dacno FIXED_DEPOSIT.d_acno%TYPE;
    matdate FIXED_DEPOSIT.maturity_date%TYPE;
    amt FIXED_DEPOSIT.amount%TYPE;
    BEGIN
    SELECT AC_NO,AUTO_SWEEP,REVERSE_SWEEP, AMT_TO_TRANSFD,BALANCE_ABOVE,ASWP_ENDDATE,RSWP_END DATE INTO AC_NO,AUTO_SWEEP,REVERSE_SWEEP, AMT_TO_TRANSFD,BALANCE_ABOVE1,ASWP_ENDDATE,RSWP_EN DDATE FROM QUANTUM WHERE ac_no=:NEW.ac_no;
    SELECT MINIMUM_BALANCE INTO minbal FROM ACCOUNT_TYPE WHERE ACCOUNT_TYPE_ID =:OLD.ac_type;
    SELECT D_ACNO,MATURITY_DATE,AMOUNT INTO dacno,matdate,amt FROM FIXED_DEPOSIT WHERE d_acno=:NEW.ac_no;
    tmpbal:=:OLD.balance-minbal;
    IF(auto_sweep='Y') THEN
    IF(SYSDATE<=matdate)THEN
    IF(SYSDATE!=aswp_enddate)THEN
    IF(tmpbal>balance_above1) THEN
    tmpamt:=:OLD.balance-amt_to_transfd;
    amt1:=amt+amt_to_transfd;
    UPDATE FIXED_DEPOSIT SET amount=amt1 WHERE d_acno=:NEW.ac_no;
    INSERT INTO ACC_TRANS(ac_no,trans_date,trans_type,trans_desc,t rans_Amt,balance)VALUES(:NEW.ac_no,SYSDATE,'db','T o FixedDeposit',amt_to_transfd,tmpamt);
    dbms_output.put_line(tmpamt);
    :NEW.balance:=tmpamt;
    END IF;
    END IF;
    END IF;
    END IF;
    END;
    /


    here it shows error :
    ora-04084:cannot change new values for this trigger type.

    actually when account balance changes,some calculation has to be done and the resulting balance must be update again in the account balance.

    how can i do this...
    plz tell me.

    s.kokila

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Re: Confusing trigger!!

    The problem in ur plsql code is in the line
    :NEW.balance:=tmpamt;

    You can not assign values to :new.column_names .
    for this you have to update the account_balance table.
    Take care for oracle Mutating error.

    If you want any help pls let me know.

    Thanks
    Pagnint
    (No need to search web before posting new question)

  3. #3
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    7

    Re: Confusing trigger!!

    After changing it into update command ,its not showing any compilation error,but when executing it tells


    The following error has occurred:

    ORA-04091: table ACCOUNT is mutating, trigger/function may not see it
    ORA-06512: at "SAMPTRY", line 31
    ORA-04088: error during execution of trigger 'SAMPTRY'

    how can i update that table?
    plz tell me.

    s.kokila

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Confusing trigger!!

    Can you not make this a BEFORE instead of an AFTER trigger? Then your assignment ":NEW.balance:=tmpamt;" will be valid and you won't be needing to perform an UPDATE.

  5. #5
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Re: Confusing trigger!!

    Pagnint
    (No need to search web before posting new question)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •