Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2009
    Posts
    5

    Unanswered: DateDiff trigger

    Hey everyone!

    I am using Oracle iSQL+, and am having trouble creating a trigger for my database class.

    We are required to create a trigger that will add/subtract a customers' balance based on when they return their rented boat from the company.

    I have gone through several iterations of my code, and have eliminated many, many errors. Here is what I have so far:

    Code:
    --Late/Early Return Trigger
    
    Create or Replace TRIGGER Return
    
    AFTER UPDATE ON Rental
    FOR EACH ROW
    
    Declare Ret_Date Date;
     Due_Date Date;
     Rent_Time Number(4);
     Charge Number(10, 2);
     CusID varchar2 (4);
    
    BEGIN
    
     Select Rent_DueDate INTO Due_Date
     FROM Rental
      WHERE Rental.Rental_ID=:NEW.Rental_ID;
    
     Select Cus_ID INTO CusID
     FROM Rental
      WHERE Rental.Rental_ID=:NEW.Rental_ID;
    
    --Calculates if rental is early or late
    -- Neg number = Returned early
    -- Pos number = Returned late
    
    Select DateDiff(day,Rent_DueDate,sysdate) As Rent_Time
    From Rental;
    
    --If Returned Early
    If Rent_Time < 0 Then
     Charge:=(Rent_Time * 20); --Calculates refund @ $20/day
    
    --If Returned Late
    ElsIf Rent_Time > 0 Then
     Charge:=(Rent_Time * 100); --Calculates charge @ $100/day
    End If;
    
    
    --Updates customer balance
    Update Cust
    Set Cus_Bal = Cus_Bal + Charge
    Where Cus_ID = CusID;
    
    --Notice of Change
    DBMS_Output.Put_Line('*** Balance update for customer: ' || CusID || ' ***');
    
    
    END;
    I currently run into an error dealing with my DateDiff function, and another unnamed error.

    21/1 PL/SQL: SQL Statement ignored
    21/8 PL/SQL: ORA-00904: "DATEDIFF": invalid identifier

    I am not quite sure how close I am to actually getting this to work, and if it will actually do what is needed.

    I also realize that my code is kinda sloppy, but this is my down & dirty version (I usually clean it up after I can get it to work)

    Any advice or help would be greatly appreciated!


    ~AB

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE OR REPLACE TRIGGER return1
      AFTER UPDATE ON rental
      FOR EACH ROW
    DECLARE
      ret_date   DATE;
      due_date   DATE;
      rent_time  NUMBER(4);
      charge     NUMBER(10,2);
      cusid      VARCHAR2(4);
    BEGIN
      SELECT rent_duedate
      INTO   due_date
      FROM   rental
      WHERE  rental.rental_id = :NEW.rental_id;
      
      SELECT cus_id
      INTO   cusid
      FROM   rental
      WHERE  rental.rental_id = :NEW.rental_id;
      
      --Calculates if rental is early or late
      -- Neg number = Returned early
      -- Pos number = Returned late
      SELECT Datediff(DAY,rent_duedate,SYSDATE) AS rent_time
      FROM   rental;
      
      --If Returned Early
      IF rent_time < 0 THEN
        charge := (rent_time * 20); --Calculates refund @ $20/day
      --If Returned Late
      ELSIF rent_time > 0 THEN
        charge := (rent_time * 100); --Calculates charge @ $100/day
      END IF;
      
      --Updates customer balance
      UPDATE cust
      SET    cus_bal = cus_bal + charge
      WHERE  cus_id = cusid;
      
      --Notice of Change
      dbms_output.Put_line('*** Balance update for customer: '
                           ||cusid
                           ||' ***');
    END;
    RETURN is PL/SQL keyword & as such should NOT be used as object name.
    Oracle does not recognize DATEDIFF as a valid function/object.
    Have you successfully created it?

    >SELECT Datediff(DAY,rent_duedate,SYSDATE) AS rent_time FROM rental;

    How many rows get return from SELECT above & where do these rows end up after SELECT?
    Last edited by anacedent; 12-05-09 at 16:28.
    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 2009
    Posts
    5
    Quote Originally Posted by anacedent View Post

    Oracle does not recognize DATEDIFF as a valid function/object.
    Have you successfully created it?
    DATEDIFF is an inherent SQL function - so I shouldn't have had to create anything - correct?

    Quote Originally Posted by anacedent View Post
    >SELECT Datediff(DAY,rent_duedate,SYSDATE) AS rent_time FROM rental;

    How many rows get return from SELECT above & where do these rows end up after SELECT?
    There really aren't any rows to save - my original intent was to make the difference in dates a variable within my PL\SQL.
    I had originally set up my script like this:
    Code:
    Rent_Time:=DateDiff(day,Rent_DueDate,sysdate);
    but I got the same error as I do now - I am not quite sure how to implement this.


    However, after looking at a few examples - I tried the DateDiff in a select statement - and my attempt at that is what I original posted here.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >DATEDIFF is an inherent SQL function - so I shouldn't have had to create anything - correct?
    Since we both know, you can't prove a negative; I am unable to prove that DATEDIFF does not exist for Oracle.

    Please provide URL to any Oracle manual where DATEDIFF is described & documented.

    Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation
    above contains the whole Oracle Doc. Set

    Since I am not clear on what exactly DATEDIFF is supposed to do, let me share this with you.
    With Oracle you can directly subtract two DATE datatypes (DATE1 - DATE2).

    Enjoy!
    Last edited by anacedent; 12-05-09 at 20:13.
    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.

  5. #5
    Join Date
    Dec 2009
    Posts
    5
    Here is the link to Microsoft's Developer Network's explanation of datediff:

    "DATEDIFF

    Returns the number of date and time boundaries crossed between two specified dates.
    Syntax

    DATEDIFF ( datepart , startdate , enddate ) "

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Here is the link to Microsoft's Developer Network's explanation of datediff:
    Nothing prevents you from re-inventing the wheel by writing DATEDIFF for Oracle RDBMS.
    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 2009
    Posts
    5
    I solved my problem - I forgot to select from dual

    Now I just have to create some test cases to test the trigger.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    please post the final trigger code.
    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.

  9. #9
    Join Date
    Dec 2009
    Posts
    5
    Here is my trigger - it did complete successfully!

    lol - I guess I did more than just select from dual - I dopped my DateDiff for just simple subtraction! Thank you for the help
    Code:
    --Late/Early Return Trigger
    
    Create or Replace TRIGGER Return
    
    AFTER UPDATE ON Rental
    FOR EACH ROW
    
    Declare Ret_Date Date;
     Due_Date Date;
     Rent_Time Number(4);
     Charge Number(10, 2);
     CusID varchar2 (4);
    
    BEGIN
    
     Select Rent_DueDate INTO Due_Date
     FROM Rental
      WHERE Rental.Rental_ID=:NEW.Rental_ID;
    
     Select Cus_ID INTO CusID
     FROM Rental
      WHERE Rental.Rental_ID=:NEW.Rental_ID;
    
    --Ret_Date:=sysdate;
    
    --Calculates if rental is early or late
    -- Neg number = Returned early
    -- Pos number = Returned late
    
    select trunc(SYSDATE-to_date(Due_Date)) INTO Rent_Time from dual;
    
    
    --If Returned Early
    If Rent_Time < 0 Then
     Charge:=(Rent_Time * 20); --Calculates refund @ $20/day
     DBMS_Output.Put_Line('*** Early Return:  Refunding Balance ***');
    
    --If Returned Late
    ElsIf Rent_Time > 0 Then
     Charge:=(Rent_Time * 100); --Calculates charge @ $100/day
     DBMS_Output.Put_Line('*** Late Return:  Charging Fee ***');
    End If;
    
    
    --Updates customer balance
    Update Cust
    Set Cus_Bal = Cus_Bal + Charge
    Where Cus_ID = CusID;
    --Rental Returned
      Update Rental
      Set Rent_ReturnDate = sysdate
      Where Rental_ID=:New.Rental_ID;
    --Updates boat avaiability to T
      Update Boat
      Set Boat_Avail='T'
      Where Boat_ID=:New.Boat_ID;
    
    --Notice of Change
    DBMS_Output.Put_Line('*** Balance Updated ***');
    
    
    END;
    /

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >select trunc(SYSDATE-to_date(Due_Date)) INTO Rent_Time from dual;
    alternatively accomplish the above with less overhead by below
    RENT_TIME := trunc(SYSDATE-Due_Date);
    It is WRONG to use TO_DATE() function on a DATE datatype.
    TO_DATE transforms a STRING into a DATE & should always include a format mask!
    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.

  11. #11
    Join Date
    May 2009
    Location
    India
    Posts
    66
    Just in case, I just checked the SQL manual. No DATEDIFF in Oracle.

    Just goes to show how pervasive Microsoft has become.

    Also, why two statements for the same where condition?

    Select Rent_DueDate INTO Due_Date
    FROM Rental
    WHERE Rental.Rental_ID=:NEW.Rental_ID;

    Select Cus_ID INTO CusID
    FROM Rental
    WHERE Rental.Rental_ID=:NEW.Rental_ID;
    End

Posting Permissions

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