Question

SQL Trigger problem

When attemptiing to Create or Replace a trigger I get the error "sql warning trigger created with compilation errors".

Trigger:

CREATE OR REPLACE TRIGGER Late_Fees
after UPDATE
ON InventoryItem
FOR EACH ROW

DECLARE

late_fee number;
num_days number;
BEGIN
num_days:= to_date(:old.ReturnDate)-TO_DATE(:old.DateDue);
select IntValue into late_fee from ApplicationSettings where Setting='Daily Late Fee';
:new.fee := (late_fee)*(num_days);

END;
/

commit;

Table:

create table Rental(
INVID int Primary key,
LoanDate date,
PatronID int,
DueDate date,
ReturnDate date,
constraint PatronID_FK Foreign key (PatronID) references Patrons(PatronID));

Test Input:

insert into rental
values ('1345', '2020-02-20', '000', '2020-02-27', '2020-02-22');
insert into rental
values ('1345', '2020-04-10', '000', '2020-04-17', '2020-04-17');
insert into rental
values ('1234', '2020-02-20', '000', '2020-02-27', '2020-02-22');
insert into rental
values ('1245', '2020-02-20', '000', '2020-02-27', '2020-02-22');
insert into rental
values ('1345', '2020-08-14', '0001', '2020-08-21', '2020-08-20');
insert into rental
values ('1265', '2020-09-01', '0001', '2020-09-08', '2020-09-10');

Answer 1

SOLUTION

CREATE OR REPLACE TRIGGER EMP_DEL
AFTER DELETE ON EMPLOYEES
FOR EACH ROW
BEGIN
INSERT INTO EMP_TEMP
(EMPLOYEE_ID,EMPLOYEE_NAME,JOB_ID,JOB_DESCRIPTION,H
OD,HIREDATE,SALARY,DEPARTMENT_ID)
VALUES (:OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, :OLD.JOB_ID,
:OLD.JOB_DESCRIPTION, :OLD.HOD, :OLD.HIREDATE, :OLD.SALARY,
:OLD.DEPARTMENT_ID);
END;

GIVEN THAT

SQL Trigger problem

When attemptiing to Create or Replace a trigger I get the error "sql warning trigger created with compilation errors".

Trigger:

CREATE OR REPLACE TRIGGER Late_Fees
after UPDATE
ON InventoryItem
FOR EACH ROW

DECLARE

late_fee number;
num_days number;
BEGIN
num_days:= to_date(:old.ReturnDate)-TO_DATE(:old.DateDue);
select IntValue into late_fee from ApplicationSettings where Setting='Daily Late Fee';
:new.fee := (late_fee)*(num_days);

END;
/

commit;

Table:

create table Rental(
INVID int Primary key,
LoanDate date,
PatronID int,
DueDate date,
ReturnDate date,
constraint PatronID_FK Foreign key (PatronID) references Patrons(PatronID));

Test Input:

insert into rental
values ('1345', '2020-02-20', '000', '2020-02-27', '2020-02-22');
insert into rental
values ('1345', '2020-04-10', '000', '2020-04-17', '2020-04-17');
insert into rental
values ('1234', '2020-02-20', '000', '2020-02-27', '2020-02-22');
insert into rental
values ('1245', '2020-02-20', '000', '2020-02-27', '2020-02-22');
insert into rental
values ('1345', '2020-08-14', '0001', '2020-08-21', '2020-08-20');
insert into rental
values ('1265', '2020-09-01', '0001', '2020-09-08', '2020-09-10');

FOR EXAMPLE:

/* Libary Design Exercise 
   version that adds members and distinguishes borrowable from 
   un-borrowable items
   
   Things to Note:
   - we added an IBorrowable interface that applies to Books and DVDs
   - each of Book and DVD now has a lateFee method
   - in the Examples class, if we want to check out the result of
     finding an item, we need to tell Java that the item is indeed 
     Borrowable.  We do this through a _cast_ (see notes for details)
    
   Ask yourself:
   - Why did we need the IBorrowable interface?
*/
import java.util.LinkedList;

class Library {
  LinkedList<LibItem> holdings = new LinkedList<LibItem>();

  // constructor
  Library() {}
  
  // add a new book to the library
  Library addItem(LibItem newItem) {
    holdings.add(newItem);
    return this;
  }
  
  // find a book by its title
  // assumes the title is in the library 
  //   (unrealistic, but what we know how to do so far)
  LibItem findTitle(String atitle) {
    for (LibItem item : holdings) {
      if (item.hasTitle(atitle)) {
        return item;
      }
    }
    // the compiler needs to return something if the book 
    //   isn't found. We can use null for this (we really
    //   should handle the error explicitly, but we haven't
    //   covered how to do that yet).
    return null; 
  }
}

abstract class LibItem {
  String title;
  String location;
   
  LibItem(String title, String location) {
    this.title = title;
    this.location = location;
  }
  
  // check whether item has a given title
  public boolean hasTitle(String atitle) {
    return this.title.equals(atitle);
  }
  
  // tell Java how to display this object when printed
  //  here, we put "obj" in front of the title (to tell strings from objects)
  public String toString() {
    return "Obj:"+this.title;
  }
}

abstract class CirculatingItem extends LibItem implements IBorrowable {
  int timesOut = 0;
  boolean isAvailable = true;
  
  CirculatingItem(String title, String location) {
    super(title, location);
  }
  
  // mark an item as checked out of the library
  public LibItem checkOut() {
    this.isAvailable = false;
    this.timesOut = this.timesOut + 1;
    return this;
  }
  
  // mark an item as checked in at the library
  public LibItem checkIn() {
    this.isAvailable = true;
    return this;
  }
}

// A class for books in a library
class Book extends CirculatingItem {
  
  // constructor
  Book(String title, String callNum) {
    super(title, callNum);
  }
  
  // book late fee is 25 cents per day
  public double lateFee(int daysOver) {
    return daysOver * .25;
  }
}

class DVD extends CirculatingItem {
  
  // Constructor 
  DVD (String title) {
    super(title,"Circulation Desk");
  }
  
  // DVD late fee is 5 dollars up to 3 days then 20 dollars above that
  public double lateFee(int daysOver) {
    if (daysOver <= 3) {
      return 5;
    } else {
      return 20;
    }
  }
}

// reference materials that cannot be checked out
class Reference extends LibItem {
  
  //constructor
  Reference(String title) {
    super(title, "Reference Shelf");
  }
}

class LibMember {
  String name;
  double feesOwed = 0;
  LinkedList<IBorrowable> checkedOut = new LinkedList<IBorrowable>();
  
  // constructor
  LibMember(String name) {
    this.name = name;
  }   
}

// interface for items that can be checked out
interface IBorrowable {
  // computes late fee assess given how many days overdue the item is
  public double lateFee(int daysOver);
}

class Examples {
  Library theLibrary = new Library();
  LibMember k = new LibMember("Kathi");
  DVD swDVD = new DVD("Star Wars 3");
  
  Examples(){
    // can add named objects or create new as you go
    theLibrary.addItem(swDVD); 
    theLibrary.addItem(new Book("Effective Java", "QA76.73 J38 B57"));
    theLibrary.addItem(new Book("Alice in Wonderland", "PR4611 A7"));
    theLibrary.addItem(new Book("Freakonomics", "HB74 P8 L479"));
    theLibrary.addItem(new Reference("Dictionary"));
  }
  
  /* try this expressions
   *   k.borrowItem(theLibary.findTitle("Freakonomics"))
   * What is the compiler concerned about? The fix is to type
   *   k.borrowItem((IBorrowable) theLibary.findTitle("Freakonomics"))
   * See the notes for a discussion about this (casting)
   */
  
}