i have created blow a database for a boat rental company, the problem is that i am having issues with my triggers , 1,2 and 3 CREATE TABLE OWNER (

i have created blow a database for a boat rental company, the problem is that i am having issues with my triggers , 1,2 and 3

CREATE TABLE OWNER

(

OWN_ID INT PRIMARY KEY NOT NULL,

OWN_F_NAME VARCHAR(15) NOT NULL,

OWN_L_NAME VARCHAR(15) NOT NULL,

OWN_PHONE INT NOT NULL, 

OWN_CITY VARCHAR(15) NOT NULL

);

CREATE TABLE CUSTOMER

(

CUS_ID VARCHAR(5) CONSTRAINT CUS_ID_PK PRIMARY KEY,

CUS_L_NAME VARCHAR(15) NOT NULL,

CUS_F_NAME VARCHAR(15) NOT NULL,

CUS_BALANCE INT NOT NULL,

CUS_PHONE INT NOT NULL,

CUS_CITY VARCHAR(15) NOT NULL

);

CREATE TABLE CREW

(

CREW_ID VARCHAR(5) CONSTRAINT CREW_PK PRIMARY KEY,

CREW_F_NAME VARCHAR(15) NOT NULL,

CREW_L_NAME VARCHAR(15) NOT NULL,

CREW_RATE INT NOT NULL,

CREW_PHONE INT NOT NULL

);

CREATE TABLE EQUIPMENT

(

EQUIP_ID VARCHAR(5) CONSTRAINT EQUIP_PK PRIMARY KEY,

EQUIP_NAME VARCHAR(15),

EQUIP_DESC VARCHAR(50),

EQUIP_QUANTITY INT NOT NULL,

EQUIP_RATE INT NOT NULL

);

CREATE TABLE ROUTE

(

ROUTE_ID VARCHAR(5) CONSTRAINT ROUTE_PD PRIMARY KEY,

ROUTE_NAME VARCHAR(20) NOT NULL,

ROUTE_BEGIN VARCHAR(20) NOT NULL,

ROUTE_END VARCHAR(20) NOT NULL,

ROUTE_RATE INT NOT NULL

);

CREATE TABLE MAINT_FACILITY

(

FACI_ID VARCHAR(5) CONSTRAINT FACI_PK PRIMARY KEY,

FACI_NAME VARCHAR(20) NOT NULL,

FACI_LOCATION VARCHAR(20) NOT NULL,

FACI_PHONE INT NOT NULL,

FACI_RATING VARCHAR(15) CONSTRAINT FACI_RATING_5BEST CHECK(Faci_Rating In ('1','2','3','4','5'))

);

CREATE TABLE BOAT

(

BOAT_ID varchar(5) CONSTRAINT BOAT_PK PRIMARY KEY,

OWN_ID INT CONSTRAINT BOAT_OWN_FK REFERENCES OWNER(OWN_ID),

BOAT_SIZE VARCHAR(15) CONSTRAINT SIZES CHECK (boat_size In ('LARGE','MEDIUM','SMALL')),

BOAT_RATE INT NOT NULL,

boat_availability varchar(3)

);

CREATE TABLE LEASE

(

LEASE_ID VARCHAR(5) CONSTRAINT LEASE_PK PRIMARY KEY,

BOAT_ID VARCHAR(5) CONSTRAINT LEASE_BOAT_FK REFERENCES BOAT(BOAT_ID),

CUS_ID VARCHAR(5) CONSTRAINT LEASE_CUSTOMER_FK REFERENCES CUSTOMER(CUS_ID),

CREW_ID VARCHAR(5) CONSTRAINT LEASE_CREW_FK REFERENCES CREW(CREW_ID),

ROUTE_ID VARCHAR(5) CONSTRAINT LEASE_ROUTE_FK REFERENCES ROUTE(ROUTE_ID),

LEASE_PRICE INT NOT NULL,

LEASE_START_DATE DATE,

LEASE_END_DATE DATE,

RETURN_DATE DATE

);

CREATE TABLE EXPERIENCE_HISTORY

(

EXPERIENCE VARCHAR(15) CONSTRAINT CUSTOMER_EXPERIENCE CHECK (experience IN ('WORST', 'BAD', 'FAIR', 'GOOD', 'EXCELLENT')),

CUS_ID VARCHAR(5) CONSTRAINT EXPERIENCE_CUS_FD REFERENCES CUSTOMER(CUS_ID),

BOAT_ID VARCHAR(5) CONSTRAINT EXPERIENCE_BOAT_FK REFERENCES BOAT(BOAT_ID),

LEASE_ID VARCHAR(5) CONSTRAINT EXPERIENCE_LEASE_FK REFERENCES LEASE(LEASE_ID),

ROUTE_ID VARCHAR(5) CONSTRAINT EXPERIENCE_ROUTE_fK REFERENCES ROUTE(ROUTE_id),

EXPERIENCE_DESC VARCHAR(40) NOT NULL,

CONSTRAINT EXPERIENCE_PK PRIMARY KEY (CUS_ID,BOAT_ID)

);

CREATE TABLE MAINTENANCE_LOG

(

MAINT_ID VARCHAR(5) CONSTRAINT MAINT_LOG PRIMARY KEY,

BOAT_ID VARCHAR(5) CONSTRAINT MAINT_BOAT_FK REFERENCES BOAT(BOAT_ID),

FACI_ID VARCHAR(5) CONSTRAINT MAINT_FACI_FK REFERENCES MAINT_FACILITY(FACI_ID),

MAINT_DATE DATE,

MAINT_PRICE INT NOT NULL,

MAINT_DESCRIP VARCHAR(50)

);

CREATE TABLE EQUIPMENT_LOG

(

EQUIP_ID VARCHAR(5) CONSTRAINT EQUIP_FK REFERENCES EQUIPMENT(EQUIP_ID),

LEASE_ID VARCHAR(5) CONSTRAINT EQUIP_LEASE_FK REFERENCES LEASE(LEASE_ID),

CONSTRAINT EQUIP_LOG_PK PRIMARY KEY(EQUIP_ID, LEASE_ID)

);

---INSERTING RECORDS INTO OWNER TABLE

INSERT INTO OWNER values (1001,'Farah','Ali',320319549,'saint cloud');

INSERT INTO OWNER values (1002,'Conor','Ronald',763339568,'sauk Center');

INSERT INTO OWNER values (1003,'Michael','Smith',911555666,'las vegas');

INSERT INTO OWNER values (1004,'Tosh','bkosh',455888777,'los angeles');

INSERT INTO OWNER values (1005,'Olson','Bzdok',811861818,'san francisco');

--INSERTING RECORDS IN CUSTOMER TABLE

INSERT INTO CUSTOMER VALUES ('1','Aneno','Musah','50','1115656511','Denver');

INSERT INTO CUSTOMER VALUES ('2','Dave','East','101','22555322','Minneapolis');

INSERT INTO CUSTOMER VALUES ('3','Big','Abdi','200','33233331','Dallas');

INSERT INTO CUSTOMER VALUES ('4','Shawn','Mendez','300','44114423','Boston');

INSERT INTO CUSTOMER VALUES ('5','Dale','Faai','350','552645526','New york');

--INSERTING RECORDS INTO CREW TABLE

INSERT INTO CREW VALUES ('11','Yaya','Ponike','20','1004441001');

INSERT INTO CREW VALUES ('22','Denis','guster','30','2003322001');

INSERT INTO CREW VALUES ('33','Rahi','Abraha','30','300355445');

INSERT INTO CREW VALUES ('44','Guster','Gump','40','400463502');

INSERT INTO CREW VALUES ('55','Kevin','Garnet','50','500454502');

--INSERTING RECORDS INTO EQUIPMENT TABLE

INSERT INTO EQUIPMENT VALUES ('5001','SAILS','SMALL SIZE','100','10');

INSERT INTO EQUIPMENT VALUES ('5002','LINES','LONG ONES','200','20');

INSERT INTO EQUIPMENT VALUES ('5003','ANCHORS','EXPENSIVE','542','5');

INSERT INTO EQUIPMENT VALUES ('5004','DINGHIES','STRONG','45','90');

INSERT INTO EQUIPMENT VALUES ('5005','COMPASS','WATER RESISTANT','86','63');

--INSERTING RECORDS INTO ROUTE TABLE

INSERT INTO ROUTE VALUES ('6001','palm valley','granite rd','palm st','30');

INSERT INTO ROUTE VALUES ('6002','greenway village','yellow st','greenway lane','15');

INSERT INTO ROUTE VALUES ('6003','Lake George','division st','George lane','20');

INSERT INTO ROUTE VALUES ('6004','mendota heights','carson ave','mendota ave','50');

INSERT INTO ROUTE VALUES ('6005','pleasant Park','parker st','pleasant rd','60');

--inserting records into MAINTENANCE FACILITY TABLE --

INSERT INTO MAINT_FACILITY VALUES('700','Nelsons Shop','Mankato','566566564','2');

INSERT INTO MAINT_FACILITY VALUES('701','Ready Shop','Minneapolis','665665654','5');

INSERT INTO MAINT_FACILITY VALUES('703','Highway Shop','St. Paul','775775758','4');

INSERT INTO MAINT_FACILITY VALUES('704','Stop n Go Shop','St. Cloud','887887879','3');

INSERT INTO MAINT_FACILITY VALUES('705','Belevue Shop','Brainerd','998999987','4');

--INSERTING RECORDS INTO BOAT TABLE

INSERT INTO BOAT VALUES('800','1001','LARGE','30','no');

INSERT INTO BOAT VALUES('801','1002','MEDIUM','10','yes');

INSERT INTO BOAT VALUES('802','1003','SMALL','40','no');

INSERT INTO BOAT VALUES('803','1004','LARGE','50','yes');

INSERT INTO BOAT VALUES('804','1005','MEDIUM','60','no');

--INSERTING RECORS INTO LEASE TABLE

INSERT INTO LEASE VALUES ('900','800','1','11','6001','150','10-OCT-2013','12-NOV-2013','12-NOV-2013');

INSERT INTO LEASE VALUES ('901','801','2','22','6002','150','08-OCT-2013','22-NOV-2013','');

INSERT INTO LEASE VALUES ('902','802','3','33','6003','150','01-JAN-2013','01-FEB-2013','25-JAN-2013');

INSERT INTO LEASE VALUES ('903','803','4','44','6004','150','01-NOV-2013','12-NOV-2013','');

INSERT INTO LEASE VALUES ('904','804','5','55','6005','150','10-OCT-2013','20-OCT-2013','30-OCT-2013');

--INSERTING RECORDS INTO EXPEREINCE_HISTORY TABLE

INSERT INTO EXPERIENCE_HISTORY VALUES('WORST','1','800','900','6001','STORMY DAY');

INSERT INTO EXPERIENCE_HISTORY VALUES('BAD','2','801','901','6002','VERY NICE WEATHER');

INSERT INTO EXPERIENCE_HISTORY VALUES('FAIR','3','802','902','6003','VERY FRIENDLY CREW MEMBERS');

INSERT INTO EXPERIENCE_HISTORY VALUES('GOOD','4','803','903','6004','FAIRLY TAKEN CARE OF BOAT');

INSERT INTO EXPERIENCE_HISTORY VALUES('EXCELLENT','5','804','904','6005','PERFECT CREW');

--INSERTING RECORS INTO MAINTENANCE_LOG TABLE

INSERT INTO MAINTENANCE_LOG VALUES('2000','800','700','22-oct-2013','100','Painting');

INSERT INTO MAINTENANCE_LOG VALUES('2001','801','701','10-nov-2013','50','cleaning');

INSERT INTO MAINTENANCE_LOG VALUES('2003','802','703','20-jan-2013','60','new planks introduced');

INSERT INTO MAINTENANCE_LOG VALUES('2004','803','704','15-aug-2013','70','new maps ');

INSERT INTO MAINTENANCE_LOG VALUES('2005','804','705','01-dec-2013','80','radio fixed');

--INSERTING RECORDS INTO EQUIPMENT_LOG TABLE

INSERT INTO EQUIPMENT_LOG VALUES('5001','900');

INSERT INTO EQUIPMENT_LOG VALUES('5002','901');

INSERT INTO EQUIPMENT_LOG VALUES('5003','902');

INSERT INTO EQUIPMENT_LOG VALUES('5004','903');

INSERT INTO EQUIPMENT_LOG VALUES('5005','904');

-trigger 1 , to avoid booking a boat that is already booked and gone

Create Or Replace Trigger Avoid_over_booking

before INSERT ON LEASE

for each row

DECLARE

boat_status VARCHAR2(3);

Begin

Select BOAT_AVAILABILITY 

INTO boat_status 

FROM BOAT

Where boat_id = :new.boat_id;

DBMS_OUTPUT.PUT_LINE(boat_status);

If boat_status ='no'

Then

Raise_application_error(-20010,'sorry!!! The boat you selected is already booked, try an available boat');

ELSIF boat_status ='yes'

THEN

UPDATE BOAT

SET BOAT_AVAILABILITY = 'no'

WHERE BOAT_ID = :new.boat_id;

End if;

End;

/

Show error;

-------trigger 2, calculating final balance after return of the boat

CREATE OR REPLACE TRIGGER Final_Balance

 AFTER UPDATE ON LEASE

FOR EACH ROW

DECLARE

old_balance NUMBER(10,3);

additional_balance NUMBER(10,3);

days NUMBER(10,0);

expected_end_date DATE;

actual_end_date DATE;

BEGIN

SELECT CUS_BALANCE

INTO old_balance

FROM CUSTOMER

WHERE CUS_ID=:NEW.CUS_ID;

expected_end_date:= :OLD.LEASE_END_DATE;

actual_end_date:= :new.RETURN_DATE;

days:= actual_end_date-expected_end_date;

IF days > 0 THEN

additional_balance := DAYS * 75;

UPDATE CUSTOMER

SET CUS_BALANCE = :NEW.LEASE_PRICE + additional_balance + old_balance

WHERE CUS_ID = :NEW.CUS_ID;

ELSIF

DAYS < 0 THEN

additional_balance := DAYS * 20;

--since days are negative , additinal balance is negative too

UPDATE CUSTOMER

SET CUS_BALANCE = :NEW.LEASE_PRICE + old_balance + additional_balance

WHERE CUS_ID = :NEW.CUS_ID;

END IF;

UPDATE BOAT

SET BOAT_AVAILABILITY = 'yes'

WHERE BOAT_ID = :NEW.BOAT_ID;

END;

/

SHOW ERROR;

--trigger 3 , Booking is not allowed if Balance is over $400

CREATE OR REPLACE TRIGGER excess_balance

BEFORE INSERT on LEASE

FOR EACH ROW

DECLARE

Balance_due Number(10,3);

BEGIN

SELECT CUS_BALANCE 

INTO Balance_due

FROM CUSTOMER

WHERE CUS_ID = :new.cus_id;

DBMS_OUTPUT.PUT_LINE(Balance_due);

IF Balance_due > 400 

THEN

RAISE_APPLICATION_ERROR(-20040,'Sorry!Booking is not allowed for customers with balance due over $400! Pay Your old balance! Thanks!!!');

END IF;

END;

/

SHOW ERROR;