I am a beginner at sql and Ive been stuck on this problem for weeks now, I just cant figure it out. So i signed up here to hopefully get some help.
Simplified explanation: I have 2 tables, account and deposition that looks like this
create table ACCOUNT(
acc_id number(8),
accty_id number(6) not null,
date_time date not null,
balance number(10,2) not null);
alter table ACCOUNT
add constraint account_acc_id_pk primary key(acc_id)
add constraint account_accty_id_fk foreign key(accty_id) references ACCOUNT_TYPE(accty_id);
create table DEPOSITION(
dep_id number(9),
cust_id varchar2(11) not null,
acc_id number(8) not null,
amount number(10,2) not null,
date_time date not null);
alter table DEPOSITION
add constraint deposition_dep_id_pk primary key(dep_id)
add constraint deposition_cust_id_fk foreign key(cust_id) references CUSTOMER(cust_id)
add constraint deposition_acc_id_fk foreign key(acc_id) references ACCOUNT(acc_id);
I also have a function called get_balance that looks like this:
create or replace function get_balance(
p_acc_id in account.acc_id%type)
return number
as
v_balance account.balance%type;
errormes varchar2(1000);
errorcode number(10);
begin
select balance
into v_balance
from account
where p_acc_id = acc_id;
return v_balance;
exception
when no_data_found then
dbms_output.put_line('Account not found, check account number');
return null;
when others then
errormes := sqlerrm;
errorcode := sqlcode;
dbms_output.put_line('something went wrong');
dbms_output.put_line('error code: ' ||sqlcode);
dbms_output.put_line('error message: '||sqlerrm);
end;
the problem is that I want to design an after insert trigger on the deposition table which make sure that the amount deposited to an account is correct and prints out that the balance is correct or incorrect after deposition.
As far as I know, I cant access the old value of balance on the account before deposition with after insert, then how can I check that the balance is correct after deposition? If I call the get_balance function, i think that i will just get the balance after the deposition has occured, which returns new balance?
The balance on the account is updated with the following procedure:
create or replace procedure do_deposition(
p_cust_id in deposition.cust_id%type,
p_acc_id in deposition.acc_id%type,
p_amount in deposition.amount%type)
as
v_balance account.balance%type;
errormes varchar2(1000);
errorcode number(10);
begin
insert into deposition(dep_id, cust_id, acc_id, amount, date_time)
values(pk_seq.nextval, p_cust_id, p_acc_id, p_amount, sysdate);
commit;
update account
set balance = balance + p_amount
where acc_id = p_acc_id;
commit;
dbms_output.put_line('Balance in account' || p_acc_id ||' after deposition :'||get_balance(p_acc_id));
exception
when others then
errormes := sqlerrm;
errorcode := sqlcode;
dbms_output.put_line('something went wrong in do_deposition');
dbms_output.put_line('error code: ' ||sqlcode);
dbms_output.put_line('error message: '||sqlerrm);
end;
I know that there is probably a lot wrong with this but I cant seem to find any examples that resembles this problem. Please forgive my ignorance, any help would be greatly appreciated. This is my first post so im sorry if I have done something wrong in my post
Thanks
Some of my many attempts:
create or replace trigger aifer_deposition
after insert
on deposition
for each row
declare
v_acc_id account.acc_id%type;
v_balance account.balance%type;
begin
select acc_id, balance
into v_acc_id, v_balance
from account
where v_acc_id = :new.acc_id;
if (v_balance = (v_balance + :new.amount)) then
dbms_output.put_line('Correct amount deposited');
else
dbms_output.put_line('Incorrect amount deposited ');
end if;
end;
create or replace trigger aifer_deposition
after insert
on deposition
for each row
begin
if account.acc_id = :new.acc_id then
account.balance = account.balance + :new.amount;
dbms_output.put_line('Correct amount deposited');
else
dbms_output.put_line('Incorrect amount deposited ');
end if;
end;
But its clear that I am missing some basic knowledge regarding after insert triggers since these do not work
The problem is that you are keeping the delta amount and the running balance in two different tables. The insert of one table and the update of another are two separate actions and would require two triggers that would not be able to communicate.
The first option is to not try to put this verification synchronously with your inserts/updates. Create a schedule job that on occasion (daily, etc..) adds up all your delta amounts and compares it to the balance and reports any inconsistencies.
The second option is a small redesign: place the running balance in the same table as the delta amount. Now you can do a trigger that can compare the previous row with the new row and perform your verification checks. But what if this or any other trigger you come up with is disabled by a DBA for some reason? You can't rely on triggers for data consistency. You'll end up needing a scheduled job to check it asynchronously anyway. And, this doesn't get away from the violation of 3rd normal form by storing a derivative value.
The last option is to not record the balance at all, at least not in your primary source tables. In data modeling we want to have one source of truth so there isn't a possibility of two related pieces of data being in disagreement. You can simply calculate it at query time by adding up the deltas. Or for performance, you can have a summary table that is known to be derivative in nature and therefore not a source of truth, which you can populate with a scheduled job. You can also populate it with a trigger to get it real-time, and then have a schedule job correct it on occasion in case your trigger was disabled for any reason. But you'll always treat it as derivative/aggregate data.
In summary, there is no way to guarantee that amount and balance, whether in one table or two, are in sync with each other at all times in all circumstances. A trigger can get disabled. Someone can do DML outside of your procedure (even if nobody has permissions, they can get permissions). You can introduce a bug into your procedure unintentionally, etc.. this is the problem with derived attributes. Therefore, either calculate the derived value at query time only, or build in scheduled correction mechanisms to keep things in sync and allow for the possibility of inconsistency in the interval of time between those corrections.