I need help with database 1-Book Loan Trigger

DBMS Oracle sql and all the requirements are needed and trigger

The library loans books to consumers and charges fees when books are not returned on time. If the fees owed to the library for any particular book are in excess of $100, a record of that debt is made and the librarian either pursues the payment of that debt or chooses to freeze the consumers account. The record of this excess debt is generated automatically. You will implement the recording of this excess debt with a trigger that monitors the Loan table and automatically inserts a record into a new Debt table when the fees for a book initially exceed $100.

It is important to realize the Loan table only represents outstanding books on loan. Once a book is returned the record of that book loan is archived to a different table and the record of the past loan is deleted from the Loan table. This means that each record in the Loan table represents an EXISTING loan of a book to a consumer.

Your trigger should only fire once when the fee for the book initially exceeds $100. The fees for that book, charged to the consumer, may continue to increase over time, but you will only place one record of the excess in the Debt table for the librarian to handle. You only need to record this event, all actions are taken separately by the librarian.

Make your trigger efficient by correctly defining the timing and granularity of the trigger and using the WHEN clause.

Create a table named Loan with the following columns:

Book_ID-- character book identifier representing single copy of book in library

Member_ID -- integer identification of member

Fees-- number representing current total fees for the book

Create the following constraints on the Loan table:

Primary key

Create a table named Debt with the following columns:

Debt_ID -- number generated via a sequence

Book_ID -- character book identifier representing single copy of book in library

Member_ID -- integer identification of member

Amt_Owed -- number representing amount owed

Record_date -- Date datatype

Create the following constraints on the Debt table:

Primary and foreign keys

Fees must be greater than one hundred

Create a sequence to generate the Debt_ID number

Develop a DDL script that creates and constrains the Loan and Debt tables and be sure to drop the tables at the start of the script.

Create a trigger on the Loan table that inserts a record into the Debt table when the Fees amount for a book exceeds $100. You may use the Oracle SYSDATE when inserting a date into the Debt table. The Amt_Owed value comes from the Fees value as part of the triggering update.

Include DML statements that will test your trigger. You will need to first insert a record into the Loan table with an initial fee less than $100. After the newloan has been inserted include statements that update the Loan table to execute each of the three test cases below.

Fees updated but leaves the fee amount less than $100.

Fees updated to indicate an amount greater than $100 (example $102).

Fees updated to indicate am amount greater than fees in #2 (example $120).