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;