Updating Column B Value with Clumn A Old Value

Question

Created the following VendorAccount Table

VendorAccount

(Ven_AccountId, 
  Ven_RegNo,   
  Ven_Invoice_RefNo,    
  TotalAmount,
  Paid_ToVen ,    
  Balance
)

In the above table the value of TotalAmount is came from another Table(Vendor_InvoiceDetails).The TOTALBALANCE value is selected through POPUP LOV.

The Pad_TOVEN amount is subtracted from TOTALAMOUNT(TotalAMount-Pad_TOVEN).When ever i select TOTALAMOUNT it gives me the old TOTAL VALUE as shown below in picture. enter image description here

I want the updated the value.When ever i select TotalAmount it should give me the new updated value on the basis of Ven_Invoice_RefNo i-e 2300 in TotalAmount Column in new entry. Below is the trigger but it has the fallowing error

ERROR: PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current curs

Trigger:

create or replace trigger "VENDORACCOUNT_T2"
BEFORE
insert or update or delete on "VENDORACCOUNT"
for each row
begin
DECLARE new_balance INT; 
DECLARE new_total INT;
DECLARE new_paid INT;

   SELECT balance INTO old_balance,
   total INTO old_total,
       PAID_TOVEN INTO new_paid
   FROM vendoraccount
   WHERE ven_regno = new.ven_regno
   AND VEN_INVOICE_REFNO = new.VEN_INVOICE_REFNO;

   UPDATE vendoraccount SET TOTALAMOUNT = old_total + old_balance - new_paid,
   balance = TOTALAMOUNT - new_paid
   WHERE VEN_REGNO= new.VEN_REGNO
   AND VEN_INVOICE_REFNO = new.VEN_INVOICE_REFNO;

end;

heuristican · Accepted Answer · 2013-02-13T10:05:04.787

There are five things to be done as far as I see:

Move begin below "declare" lines.
Use just one "declare" keyword.
Declare old_balance.
Declare old_total.

Just one "into".

create or replace trigger "VENDORACCOUNT_T2"
BEFORE
insert or update or delete on "VENDORACCOUNT"
for each row
DECLARE new_balance INT; 
        new_total INT;
        new_paid INT;
        old_balance INT;
        old_total INT;
begin

SELECT balance, total, PAID_TOVEN 
INTO old_balance, old_total, new_paid
FROM vendoraccount
WHERE ven_regno = new.ven_regno
AND VEN_INVOICE_REFNO = new.VEN_INVOICE_REFNO;

UPDATE vendoraccount SET TOTALAMOUNT = old_total + old_balance - new_paid,
balance = TOTALAMOUNT - new_paid
WHERE VEN_REGNO= new.VEN_REGNO
AND VEN_INVOICE_REFNO = new.VEN_INVOICE_REFNO;
end;

LINE 8:Position 43: PL/SQL: ORA-00923: FROM keyword not found where expected LINE 8:Position 4: PL/SQL: SQL Statement ignored — Usman YousafZai, Feb 13 '13 at 06:32

score 0 · Answer 2 · answered Dec 05 '12 at 10:30

0

You should move the declaration section before 'begin'

answered Dec 05 '12 at 10:30

Shepherdess

651
6
7

1

@MuhammadUsman:I dint see any declaration `old_total`,now what is old_balance here .And In oracle select will be like `select x,y into v_x,v_y from ...` – Gaurav Soni Dec 05 '12 at 11:00
@Shepherdess i get the same error when i move the declaration section before begin – Usman YousafZai Dec 06 '12 at 16:42

Updating Column B Value with Clumn A Old Value

2 Answers2