PL SQL Program For EB AMOUNT CALCULATION.

PL SQL Program For EB AMOUNT CALCULATION 


create or replace trigger calc After insert or update or delete on EBFIRST for each row
declare
unit number(3);
amt number(8,3);
begin
if inserting then
unit:=:new.cur-:new.prev;
if(unit>=500) then amt:=unit*5;
elsif(unit>=400) then amt:=unit*4;
elsif(unit>=300) then amt:=unit*3;
elsif(unit>50) then amt:=unit*2; 
else amt:=50;
end if;
insert into ebsecond values(:new.cusno,:new.name,unit,amt);
dbms_output.put_line(:new.cusno||' '||:new.name||' '||unit||' '||amt);
dbms_output.put_line('amt calculated and records inserted');
end if;
if updating then
unit:=:new.cur-:new.prev;
amt:=unit*2.5;
update ebsecond set units=unit,amount=amt 
where cusno=:old.cusno;
dbms_output.put_line(:old.cusno||' '||:old.name||' '||unit||' '||amt);
dbms_output.put_line('amt calculated and records updated');
end if; 
if deleting then
delete from ebsecond where cusno=:old.cusno;
dbms_output.put_line(' Record '|| :old.cusno ||' deleted successfully');
end if;
end;