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;