PL/SQL trigger giving error?

Got this question for an exam but getting error from trigger. Please help.

Question

customer(cust_id,cust_name,address) rent_info(cust_id,date_out,date_due_in,date_returned,fine) rented_video(cust_id,no_of_videos)

Issuedate should be current date and due date should be 7 days after issue date. When video is returned by the customer the rent_info table should contain cust_id, date_out, date_due_in. A trigger is used to insert data into rent_info table. The following validation must be done before inserting data.

A customer is not allowed to take more than 3 videos in the same date. When a video is returned the returm date is updated. And fine is calculated if any.

Fine is calculated by

1. For the first three days of delay Rs 10 per day

2. For the follwoing three days of delay Rs 20 per day

3. After the six dates charge fine of Rs 30 per day

Write a procedure/trigger for doing the updation operation.

I solved it like this.

Made three table:

create table customer(

cust_id number(4),

cust_name varchar2(8),

address varchar2(8)

);



create table rent_info(

cust_id number(4),

date_out date,

date_due_in date,

date_returned date,

fine number(10)

);



create table rented_video(

cust_id number(4),

no_vid number(4)

);

Procedure for taking book

create or replace procedure take_proc(c_id in int,d_out in date) is

val number(3) :=0;

begin

    insert into rent_info values(c_id,d_out,d_out+7,NULL,0);

    update rented_video set no_vid=no_vid+1 where cust_id=c_id;

    --val := select count(date_out) from rent_info where (date_out='12-jan-2010');

    --dbms_output.put_line('Values is '||val);

end;

/

Procedure for returning book

create or replace procedure return_proc(c_id in int,d_ret in date) is

val number(3) :=0;

begin

    update rented_video set no_vid=no_vid-1 where cust_id=c_id;

    update rent_info set date_returned=d_ret where cust_id=c_id;

    --insert into rent_info values(c_id,d_out,d_out+7,NULL,0);

    update rented_video set no_vid=no_vid-1 where cust_id=c_id;

    --val := select count(date_out) from rent_info where (date_out='12-jan-2010');

    --dbms_output.put_line('Values is '||val);

end;

/

Trigger for updating Fine when book is returned

create or replace trigger ret_trig

before update on rent_info

for each row

declare

tfine number(7) := 0;

temp number(7) := 0;

rdate date;

dudate date;

cid number(4);

begin

    --select date_returned into rdate from rent_info;

    --select date_due_in into dudate from rent_info;

    --select cust_id into cid from rent_info;

    --if (rdate- dudate) <=3 then

        --temp := rdate- dudate;

        --tfine := tfine+ temp * 10;

    --end if;

    if (:new.date_returned-:old.date_due_in ) <=3 then

        temp := :new.date_returned-:old.date_due_in;

        tfine := tfine+ temp * 10;

        dbms_output.put_line('Fine Values is '|| tfine);

    elsif (:new.date_returned-:old.date_due_in ) <=6 then

        temp := :new.date_returned-:old.date_due_in;

        tfine := tfine+ 3 * 10;

        tfine := tfine+ 20*(temp-3);

        dbms_output.put_line('Fine Values is '|| tfine);

    else

        temp := :new.date_returned-:old.date_due_in;

        tfine := tfine+ 3 * 10;

        tfine := tfine+ 3 * 20;

        tfine := tfine+ 30*(temp-6);

        dbms_output.put_line('Fine Values is '|| tfine);

    end if; 



    --update rent_info set fine=fine+tfine where cust_id=:old.cust_id;

end;

/

I could calculate the fine correctly but couldnt update it to the rent_info table ( Last line of the trigger which does the updation is commented).

I Think I have made some logical mistake in trigger creation. Please tell my how to solve the problem correctly.

Sample values to insert

insert into customer values(1,'john','abc h');

insert into customer values(2,'joseph','cde h');

insert into rented_video values(1,0);

insert into rented_video values(2,0);

exec take_proc(1,'12-jan-2010');

exec take_proc(2,'13-jan-2010');

exec return_proc(1,'16-jan-2010');
exec re开发者_运维百科turn_proc(2,'29-jan-2010');

Inside a row-level trigger, you change the value of a column by simply doing an assignment to :NEW - you don't issue an UPDATE statement. e.g.:

:NEW.fine := :OLD.fine + tfine;

Why are you using a trigger? You are building a very clear API by developing these procedures, but then you shove code into a trigger. Why not incorporate this logic into return_proc to make things more obvious?