Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Dec 2012
    Posts
    11

    Unanswered: Trigger Created with compilation errors 10g

    Hi guys, taking a database class and trying to create a trigger to update cust fee based on the date a rental was returned. Not really sure what I am doing. Please be a life saver!

    CREATE OR REPLACE TRIGGER Trig_fee
    AFTER UPDATE
    ON Charter
    REFERENCES NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    IFEE number (5) := 75 * DateDiff( day,return_date, proposed_Return_date);
    DFEE number (5) := 20 * DateDiff( day,return_date,proposed_Return_date);


    BEGIN
    if Return_date > proposed_Return_date then
    Cust_fee := Cust_fee + IFEE;
    INSERT INTO Charter VALUES ( :OLD.Charter_ID, :OLD.Itinerary_ID, :OLD.Cust_ID, :OLD.Boat_ID, Cust_fee, :OLD.pick_up_date, :OLD.Proposed_Return_date, :NEW.Return_date);

    elsif return_date < proposed_date then
    Cust_fee := Cust_fee + DFEE;
    INSERT INTO Charter VALUES ( :OLD.Charter_ID, :OLD.Itinerary_ID, :OLD.Cust_ID, :OLD.Boat_ID, Cust_fee, :OLD.pick_up_date, :OLD.Proposed_Return_date, :NEW.Return_date);


    End IF;
    End

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Trigger Created with compilation errors 10g
    ERROR?
    What Error?
    I don't see any error.

    Since we don't have your tables or data, we can not compile, run or test posted code.
    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2012
    Posts
    11
    You mean something like this?

    --Create Charter Table
    Create Table Charter(Charter_ID number(5), Itinerary_ID number(5),Cust_ID number(5), Boat_ID number (5), Cust_fee number(8,2), Pick_up_date date , Proposed_Return_date date, Return_date date, primary key(Charter_ID), foreign key(Itinerary_ID) references Itinerary(Itinerary_ID), foreign key (Cust_ID) references Customer (Cust_ID), foreign key (Boat_ID) references boat (Boat_ID) );

    Example Insert
    --Insert into Charter table

    Insert into Charter values( 2001, 101, 3001, 10100, 800, '1-Jan-2012', '15-Jan-2012', '13-Jan-2012');

  4. #4
    Join Date
    Dec 2012
    Posts
    11
    It is telling me I have a compilation error(s) but doesn't say what or where

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Sql> show error
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE TABLE charter 
      ( 
         charter_id           NUMBER(5), 
         itinerary_id         NUMBER(5), 
         cust_id              NUMBER(5), 
         boat_id              NUMBER (5), 
         cust_fee             NUMBER(8, 2), 
         pick_up_date         DATE, 
         proposed_return_date DATE, 
         return_date          DATE, 
         PRIMARY KEY(charter_id), 
         FOREIGN KEY(itinerary_id) REFERENCES itinerary(itinerary_id), 
         FOREIGN KEY (cust_id) REFERENCES customer (cust_id), 
         FOREIGN KEY (boat_id) REFERENCES boat (boat_id) 
      );
    which fails for me since I do not have other tables
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Dec 2012
    Posts
    11
    17/3 PLS-00103: Encountered the symbol "end-of-file" when expecting on e of the following: ; <an identifier> <a double-quoted delimi ted-identifier> The symbol ";" was substituted for "end-of-file" to continue.

  8. #8
    Join Date
    Dec 2012
    Posts
    11
    --Create Owner Table
    Create Table Owner(Owner_ID number(5), Owner_Lname varchar2(15), Owner_Fname varchar2(15), Owner_City varchar2(15), Primary Key(Owner_ID));

    --Create Boat Table
    Create Table Boat (Boat_ID number(5), Owner_ID number(5), Boat_Size varchar2(10), Boat_Model varchar2(20), Boat_Fee number(4), available number (1), Primary Key(Boat_ID), Foreign key(Owner_ID) References Owner(Owner_ID));

    --Create Owner_Equipment Table
    Create Table Owner_Equipment(Equip_ID number(5), Equip_Type varchar2(25), Equip_Desc varchar2(25), Boat_ID number(5), Owner_ID number(5), Primary Key(Equip_ID), Foreign Key (Owner_ID) References Owner(Owner_ID), Foreign Key (Boat_ID) References Boat(Boat_ID));

    --Create Customer Table
    Create Table Customer(Cust_ID number(5), Cust_Lname varchar2(20), Cust_Fname varchar2(20), Cust_City varchar2(10), Cust_experience_Level varchar2(10), Balance number (5,2), primary key (Cust_ID));

    --Create Itinerary Table
    Create Table Itinerary(Itinerary_ID number(5), Itinerary_location varchar2(10), Itinerary_Weather varchar2(15), Itinerary_Difficulty varchar2(15), route_length number(5,1), Itinerary_cost number(8,2), Primary key(Itinerary_ID));

    --Create Miller_Equipment Table
    Create Table Miller_Equipment(Miller_Equip_ID number(5), Miller_Equip_type varchar2(15), miller_Equip_desc varchar2(5), Boat_ID Number(5), Primary key(Miller_Equip_ID), Foreign key(Boat_ID) References Boat(Boat_ID));

    --Create Repair Table
    Create Table Repair(Repair_ID number(5), Repair_type varchar2(15), Repair_desc varchar(20), primary key(Repair_ID));

    --Create Facility Table
    Create Table Facility(Facility_ID number(5), Facility_name varchar2(10), Facility_City varchar2(15), Facility_cost_hour number(5,2), Facility_contact varchar2(15), contact_phone number(10), primary key(Facility_ID));

    --Create Charter Table
    Create Table Charter(Charter_ID number(5), Itinerary_ID number(5),Cust_ID number(5), Boat_ID number (5), Cust_fee number(8,2), Pick_up_date date , Proposed_Return_date date, Return_date date, primary key(Charter_ID), foreign key(Itinerary_ID) references Itinerary(Itinerary_ID), foreign key (Cust_ID) references Customer (Cust_ID), foreign key (Boat_ID) references boat (Boat_ID) );

    --Create Crew Table
    Create Table Crew(Crew_ID number(5), Crew_Lname varchar2(25),Crew_Fname varchar2(20), Crew_Title varchar2(20),Crew_payrate number(6,2),Crew_experience_Level varchar(10), primary key (Crew_ID));

    --Create Crew _Schedule Table
    Create Table Crew_Schedule(Crew_ID number(10), Charter_ID number(5),start_date date, End_date date, Hours number(5,2), primary key (Crew_ID,Charter_ID), foreign key (Crew_ID) references Crew(Crew_ID), foreign key(Charter_ID) references Charter(Charter_ID));

    --Create Maintenance Table
    Create table Maintenance(Main_ID number(5), Facility_ID number(5), Repair_ID number(5), Boat_ID number (5), Repaid_date Date, Repair_hours number(5,2), Repair_cost number(10,2), primary key (Main_ID), foreign key(Facility_ID) references Facility(Facility_ID), foreign key(Repair_ID) references Repair(Repair_ID), foreign key (Boat_ID) references Boat (Boat_ID));

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    realize & understand that this forum is for Oracle RDBMS; not any other like MS SQL Server.

    Oracle DB does not know about, support or have any DATEDIFF() function!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Dec 2012
    Posts
    11
    Oh great well that wouldnt help my code then.haha.

    to_date ('return_date') - to_date (p_r_d)?

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >to_date ('return_date') - to_date (p_r_d)?

    NEVER use TO_DATE on DATE datatype!
    In Oracle you can just subtract one DATE datatype from another to obtain an INTERVAL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Dec 2012
    Posts
    11
    thanks, I changed the code to this:

    CREATE OR REPLACE TRIGGER Trig_fee
    AFTER UPDATE
    ON Charter
    REFERENCES NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    xx number (5) := return_date - proposed_return_date;
    IFEE number (5) := 75 * xx;
    DFEE number (5) := 20 * xx;


    BEGIN
    if Return_date > proposed_Return_date then
    Cust_fee := Cust_fee + IFEE;
    INSERT INTO Charter VALUES ( :OLD.Charter_ID, :OLD.Itinerary_ID, :OLD.Cust_ID, :OLD.Boat_ID, Cust_fee, :OLD.pick_up_date, :OLD.Proposed_Return_date, :NEW.Return_date);
    END IF;

    if return_date < proposed_return_date then
    Cust_fee := Cust_fee - DFEE;
    INSERT INTO Charter VALUES ( :OLD.Charter_ID, :OLD.Itinerary_ID, :OLD.Cust_ID, :OLD.Boat_ID, Cust_fee, :OLD.pick_up_date, :OLD.Proposed_Return_date, :NEW.Return_date);
    END IF;

    End

    Still getting compilation error at line 18. I will keep trying.

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> show error
    Errors for TRIGGER TRIG_FEE:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/4      PL/SQL: Item ignored
    2/18     PLS-00201: identifier 'RETURN_DATE' must be declared
    3/6      PL/SQL: Item ignored
    3/25     PLS-00320: the declaration of the type of this expression is
             incomplete or malformed
    
    4/6      PL/SQL: Item ignored
    4/25     PLS-00320: the declaration of the type of this expression is
             incomplete or malformed
    
    8/1      PL/SQL: Statement ignored
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    8/4      PLS-00201: identifier 'RETURN_DATE' must be declared
    13/1     PL/SQL: Statement ignored
    13/4     PLS-00201: identifier 'RETURN_DATE' must be declared
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Dec 2012
    Posts
    11
    I am using the university's oracle and the show error did not give that much data.

    So it doesnt like return_date. Return_date needs to be pulled from the existing table/row.

    I will keep trying. Thanks.

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post results from the following SQL

    SQL> DESC CHARTER
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •