Using if statement in Oracle triggers

Question

Table1: customers

cust_no | cust_credbal | cred_status
Table2:customer_credittopup

cust_no | trans_amount

I have the above two tables, I have written an update trigger after insert into the Table2 to update Table1. Thus the amount in Table1 cust_credbal is updated after adding cust_credbal in Tabl1 + the new value in trans_amount in Table2. How do i add an if statement after the update to check if cust_credbal is > than 20 then cred_status is is set to 'VALID' else 'EXHAUSTED'

My trigger showing this error: Error at line 8: PLS-00049: bad bind variable 'NEW.CUST_CREDITBAL'

CREATE OR REPLACE TRIGGER after_insert_credittopup
AFTER INSERT ON customer_credittopup
FOR EACH ROW 
DECLARE 
  credit number(11);
BEGIN
  UPDATE customers
     SET cust_creditbal=cust_creditbal +:new.trans_amount
   WHERE cust_no=:new.cust_no;

  SELECT Cust_creditbal INTO credit 
    FROM customers WHERE cust_no=:new.cust_no;

  IF(:new.cust_creditbal>0) THEN
    UPDATE customers
       SET Cust_credstatus='Valid'
     WHERE cust_no=:new.cust_no;
  end if;
end;
/

Which table has 'cust_creditbal' field ? If it's in customers, :new.cust_creditbal would make sense only inside the trigger on customers table. — a1ex07
– a1ex07, Commented Dec 18, 2012 at 17:45

a1ex07 · Accepted Answer · 2012-12-18 17:42:46Z

2

It seems that all you need here is just 1 update statement :

UPDATE customers
SET cust_creditbal=cust_creditbal +:new.trans_amount,
Cust_credstatus = CASE WHEN cust_creditbal +:new.trans_amount > 0 THEN 'Valid'
 ELSE 'Invalid' 
 --or if you don't want to change status in such case just put 
 --ELSE Cust_credstatus 
 END
 WHERE cust_no=:new.cust_no;
 --SELECT Cust_creditbal INTO credit FROM customers WHERE cust_no=:new.cust_no;
 --IF(:new.cust_creditbal>0) THEN
 --UPDATE customers
 --SET Cust_credstatus='Valid'
 --WHERE cust_no=:new.cust_no;

edited Dec 18, 2012 at 17:42

answered Dec 18, 2012 at 17:36

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alphy Over a year ago

That worked like a charm for 2 conditions, what if i have 4 conditions such as cust_credbal is > 20 is valid, less than 0 overlimit, equal to zero is exhausted using else if

a1ex07 Over a year ago

You can do it inside CASE : for instance

WHEN cust_creditbal +:new.trans_amount >0 THEN 'Valid'  WHEN cust_creditbal +:new.trans_amount <0 THEN 'Overlimit' ELSE 'Exhausted' END

.

Randy · Accepted Answer · 2012-12-18 17:33:03Z

0

NEW.CUST_CREDITBAL does not match cust_credbal

answered Dec 18, 2012 at 17:33

Randy

16.7k2 gold badges40 silver badges56 bronze badges

Collectives™ on Stack Overflow

Using if statement in Oracle triggers

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related