Hi folks, I have the following tables:
create table member (
mid varchar2 (7),
password varchar2 (8) not null,
fname varchar2 (15) not null,
lname varchar2 (15) not null,
address varchar2 (50),
email varchar2(30),
cash_balance number (10,2) not null,
primary key (mid)
);
create table transaction (
mid varchar2 (7),
symbol varchar2 (8),
trans_date date,
trans_type varchar2 (20) not null,
quantity number (7,2) not null,
price_per_share number (7,3) check (price_per_share >= 0.0) not null,
commission number (5,2) check (commission >= 0.0) not null,
amount number (8,2) not null,
primary key (mid, symbol, trans_date),
foreign key (mid) references member,
foreign key (symbol) references security
);
I am trying to create a trigger that will fire each time a new transaction is added to the Transaction table. I want it to be able to update the cash balance accordingly for a trade, depending on if it is a buy or sell. Here's the trigger below:
create or replace
TRIGGER insert_transaction
BEFORE INSERT ON transaction
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE trans_type transaction.trans_type%type;
trans_amount transaction.amount%type;
balance member.cash_balance%type;
amount_too_high EXCEPTION;
BEGIN
select trans_type, amount, cash_balance
into trans_type, trans_amount, balance
from transaction, member
where member.mid= :new.mid and transaction.mid=member.mid;
if (:new.trans_type='buy') then
if(:new.trans_amount<balance) then
update member
set cash_balance= cash_balance - new.trans_amount
where member.mid=:new.mid;
else
RAISE amount_too_high;
end if;
else
update member
set cash_balance=cash_balance + new.trans_amount
where member.mid= :new.mid;
end if;
EXCEPTION
WHEN amount_too_high THEN
DBMS_OUTPUT.PUT_LINE('Transaction amount higher than member balance.');
RAISE_APPLICATION_ERROR(-20002, 'NOT ENOUGH CASH');
END;
The trigger is given me a bad bind variable on line 18 which is the bolded update member. I don't understand why this is happening or how to fix it. I initially had more bad bind variables that were a result of using :new.trans_amount in the nested if. Once I got rid of the : it seemed to fix the problem. However, I don't know where exactly this last error is occurring.
I just want to be able to fix this error so that I can begin testing it.