Sudhanshu Singh Sudhanshu Singh - 25 days ago 7
SQL Question

What is the error in following PL/SQL Trigger Code

CREATE OR REPLACE TRIGGER t_for_debit
BEFORE UPDATE
ON account
FOR EACH ROW
WHEN (new.acc_bal>0)
DECLARE
amt ACCOUNT.ACC_BAL%TYPE;
BEGIN
select acc_bal into amt from account where accno=:old.accno;
if :old.acc_bal-:new.acc_bal then
DBMS_OUTPUT.PUT_LINE('PREVIOUS AMOUNT ->'||:old.acc_bal||' DEDUCE BALANCE ||:new.acc_bal||' NEW BALANCE IS ' ||:old.acc_bal-:new.acc_bal );
else
insert into loan values(:old.acc_name,:old.acc_bal-:new.acc_bal,:old.accno)
DBMS_OUTPUT.PUT_LINE('TXN SUCCESSFULLY BUT DUE TO DEBIT MONEY MORE THAN ACCOUNT MONEY');
DBMS_OUTPUT.PUT_LINE('WE OPENED YOUR LOAN ACCOUNT. PLEASE FEED THE LOAN BEFORE FURTHER USE');
DBMS_OUTPUT.PUT_LINE('LOAN CUSTOMER NAME '||:old.acc_name||' HAVING LOAN AMOUT '||:old.acc_bal-:new.acc_bal||' ASSOCAITE WITH '||:old.accno || ' ACCOUNT NO');
END IF;
END;
/


When I compiled this code on SQL*Plus command prompt then following error showing.

6/93 PLS-00103: Encountered the symbol "NEW" when expecting one of the
following:
) , * & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec as between from using || member submultiset

Answer Source

There were several issues with your code.

  • if :old.acc_bal-:new.acc_bal THEN .. is not a valid statement it is modified to

    if :old.acc_bal-:new.acc_bal > 0 THEN

Your trigger will compile with above change, However.

  • select acc_bal into amt statement will raise an error ORA-04091: table HR.ACCOUNT is mutating, trigger/function may not see it. This will happen when you run an update statement on account table. In the context of the trigger it is not required as we can get all the required values from :old.acc_bal and :new.acc_bal . So, it is commented.

  • You cannot concatenate a result of a numerical difference with a string inside DBMS_OUTPUT.PUT_LINE(). It will throw an exception ORA-06502: PL/SQL: numeric or value error. So, Here i changed the code to store the result of the difference into the amt variable and re-used it in DBMS_OUTPUT.

    SET DEFINE OFF
    SET SERVEROUTPUT ON
    CREATE OR REPLACE TRIGGER t_for_debit
    BEFORE UPDATE 
    ON account
    FOR EACH ROW
    WHEN (new.acc_bal > 0)
    DECLARE
    amt ACCOUNT.ACC_BAL%TYPE; 
    BEGIN
    amt := :old.acc_bal-:new.acc_bal;
        --select acc_bal into amt from account where accno = :old.accno;
    if amt > 0 then
       DBMS_OUTPUT.PUT_LINE('PREVIOUS AMOUNT ->'||:old.acc_bal||' DEDUCE BALANCE '||:new.acc_bal||' NEW BALANCE IS ' || amt);
    else
        insert into loan(acc_name,acc_bal,accno)  values(:old.acc_name,:old.acc_bal-:new.acc_bal,:old.accno);
        DBMS_OUTPUT.PUT_LINE('TXN SUCCESSFULLY BUT DUE TO DEBIT MONEY MORE THAN ACCOUNT MONEY');
        DBMS_OUTPUT.PUT_LINE('WE OPENED YOUR LOAN ACCOUNT. PLEASE FEED THE LOAN BEFORE FURTHER USE');
        DBMS_OUTPUT.PUT_LINE('LOAN CUSTOMER NAME '||:old.acc_name||' HAVING LOAN AMOUT '||amt||' ASSOCAITE WITH '||:old.accno || '        ACCOUNT NO');
    END IF;
    END;
    /
    

Here is the output for the else condition.

     1 rows updated.
     TXN SUCCESSFULLY BUT DUE TO DEBIT MONEY MORE THAN ACCOUNT MONEY
     WE OPENED YOUR LOAN ACCOUNT. PLEASE FEED THE LOAN BEFORE FURTHER USE
     LOAN CUSTOMER NAME INV HAVING LOAN AMOUT -1 ASSOCAITE WITH 1        ACCOUNT NO