CODEWITHSUNDEEP
sqloracle-database
Hugo Nilsson
Hugo Nilsson

Reputation: 29

Call function in trigger

So i have a function that returns saldo. It looks like this:

create or replace function get_saldo(
p_knr in konto.knr%type)
return number
as
v_saldo konto.saldo%type;
begin
select saldo
into v_saldo
from konto
where knr = p_knr;
return v_saldo;
exception
when no_data_found then
return -1;
end;

My trigger should call get_saldo and check that the amount of money being inserted in the withdraw table is not more than money on the account. My trigger looks like this so far:

create or replace trigger bifer_överföring 
before update or insert 
of belopp
on insättning
for each row 
begin 
if (:new.belopp < get_saldo(p_knr)) then 
raise_application_error(-20001,'Överföringssumman är högre
än vad som finns på kontot'); 
end if; 
end;

Hope you guys understand what i mean. Its a school task where we need to use stored procedures. Im very new to stored procedures and i really cant seem to get the hang of it.

Upvotes: 0

Views: 104

Answers (1)

Littlefoot
Littlefoot

Reputation: 142768

Looks OK to me, except this:

if (:new.belopp < get_saldo(p_knr)) then 
                            -----

You should pass something to the function; it can't be p_knr but something like :new.knr (or whichever column exists in the insättning table and can be used to calculate saldo).

Upvotes: 1

Related Questions