How to write a Trigger that Adds a $2 late penalty to a members unpaid balance account

I want to create a trigger that enforces that a late return penalty is $2 each day late. The fine should automatically be calculated and added to the balance. Below is my code as well as my attempt for the trigger. The trigger only accounts for the rental code of one day, but if I can figure out one of the rental codes, the other two will be easy to figure out.

What Trigger code i have so far:

Create or Replace Trigger Late_Rule
BEFORE INSERT ON Transaction
For each Row
DECLARE
NofDays NUMBER; 
ChargeDays Number;
Fee Number;

BEGIN

SELECT count(Rental_Code) into NofDays
From Transaction Group by Mem_num having  Mem_num= :new.Mem_num;
 DBMS_output.put_line(NofDays);

If Rental_Code = 'R101'
 If (Date_Rented_Out - Date_returned) > 1
    NofDays- 1 = ChargeDays
    ChargeDays * 2 = Fee
ELSE 
Fee = 0
  END IF;
END IF;

Update Fee into Unpaid_Bal

end;
/


  show error;

--Attempt 2

Create or replace trigger Late_Fee_Rule
Before UPDATE ON transaction
for each row
DECLARE
Fine_Due NUMBER;
BEGIN
SET Fine_Due = trunc(sysdate - date_rented_out) * 2
where date_returned is null
and (sysdate - date_rented_out) > decode (Rental_code, 'R101', 1, 'R103', 3, 'R105', 5);
Update Member
where Unpaid_Bal = (Unpaid_Bal + Fine_Due)


End;
/
show error;

Answer

If any data (such as fine due) will change continuously (e.g. every day), then don’t store it in your model at all.

Instead, calculate it at report time using a view, e.g. (adapted from APC’s example):

CREATE transaction_vw AS
SELECT t.*
      ,CASE WHEN date_returned is null
             and (sysdate - date_returned_out)
               > DECODE(rental_code
                       ,'R101', 1
                       ,'R103', 3
                       ,'R105', 5)
       THEN trunc(sysdate - date_returned_out) * 2
       END AS fine_due
FROM transaction t;

Leave a Reply

Your email address will not be published. Required fields are marked *