1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

days_diff on two columns can any one help

Discussion in 'SQL PL/SQL' started by olddog, Oct 28, 2010.

  1. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Hi guys
    I have two columns in table which i need to find the number of days diff and multiply the diff by a given amount, the trigger compiles and the insert works except its not returning a value into the expected field, here's my script Pls what am I missing?

    create or replace
    TRIGGER rental_Hire
    BEFORE INSERT ON rental
    FOR EACH ROW
    DECLARE

    days_diff number;
    BEGIN

    if :new.checkin > dueDate then
    select ceil(to_number('checkin') - to_number('duedate')) into days_diff from rental where rental.Id=rentalId;
    end if;

    :new.penalty := rental_cost*days_diff;
    END;

    Thanks in advance
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Presuming rental_cost is a column in the RENTAL table:

    Code (SQL):
     
    CREATE OR REPLACE
    TRIGGER rental_Hire
    BEFORE INSERT ON rental
    FOR EACH ROW
    DECLARE

    days_diff NUMBER;
    BEGIN

    IF :NEW.checkin > dueDate THEN
    SELECT CEIL(to_number('checkin') - to_number('duedate')) INTO days_diff FROM rental WHERE rental.Id=rentalId;
    END IF;

    :NEW.penalty := :NEW.rental_cost*days_diff;
    END;
    /
     
     
  3. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Thanks for that Zargon
    I did try a different way but ended with the same result that penalty is not returning a value I cant see the prob maybe someone with more experience than me can ill post the table and the trigger so you can understand what im trying to do.

    CREATE TABLE Rental
    (RentalId NUMBER(3) NOT NULL,
    BookingDate DATE DEFAULT SYSDATE NOT NULL,
    BookingMethod VARCHAR2(15) NOT NULL,
    Checkout DATE NOT NULL,
    RentDays NUMBER(2) ,
    Checkin DATE,
    DueDate DATE,
    HistoryLevel NUMBER(3,2),
    Damage CHAR(1),
    Payment_Id NUMBER(9) NOT NULL,
    Id VARCHAR(6) NOT NULL,
    CustomerId NUMBER(4) NOT NULL,
    InsuranceId CHAR(1) NOT NULL,
    total_price number,
    snacksId number,
    CONSTRAINT Rental_Id CHECK (REGEXP_LIKE(Id, '^[0-9]{3}[a-zA-Z]{3}$')) ,
    CONSTRAINT Rental_RentalId CHECK (RentalId BETWEEN 1 AND 999 ),
    CONSTRAINT Rental_Checkin CHECK (Checkin >= Checkout),
    CONSTRAINT Rental_RentalId_pk PRIMARY KEY (RentalId),
    CONSTRAINT Rental_Damage CHECK (Damage = 'Y' OR Damage ='N'),
    CONSTRAINT Rental_Payment_Id_fk FOREIGN KEY (Payment_Id) REFERENCES PayType (Payment_Id),
    CONSTRAINT Rental_Id_fk FOREIGN KEY (Id) REFERENCES Movie (Id),
    CONSTRAINT Rental_CustomerId_fk FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId),
    CONSTRAINT Rental_InsuranceId_fk FOREIGN KEY (InsuranceId) REFERENCES Insurance (InsuranceId));
    --Comment: Regexp used to check Id data entered.
    --Comment: RentalId constraint checks numbers are between 1 and 999.
    --Comment: CheckIn constraint checks date is = to or > than Checkout date.
    --Comment: Foreign keys are referenced to other tables.
    --Comment: DueDate added to table to satisfy insert data.


    INSERT INTO Rental
    values(seq_rentalId.nextval, sysdate, 'in store', sysdate, null, '05/nov/10', '30/oct/10', null, null, 2, '545GVC', 1000, 'N', 700,1);
    INSERT INTO Rental
    values(seq_rentalId.nextval, sysdate, 'in store', sysdate, null, '05/nov/10', '01/nov/10', null, null, 2, '545GVC', 1005, 'N', 700,2);

    trigger 1:
    create or replace
    TRIGGER rental_Hire
    BEFORE INSERT ON rental
    FOR EACH ROW
    DECLARE
    rental_cost number;
    period NUMBER;
    total NUMBER;
    s_retail NUMBER;
    days_diff number;
    BEGIN
    SELECT rental_period_days, rental_cost INTO period, total FROM movie, releasetype WHERE movie.releasetype = release_type_id AND movie.id = :new.id;
    SELECT retail INTO s_retail from snacks where snacksId = :new.snacksId;




    update snacks
    set qty_on_hand = qty_on_hand-1 where snacksId = :new.snacksId;
    :new.duedate := sysdate+period;
    :new.total_price := total+s_retail;

    END;

    trigger2:

    create or replace trigger penalty
    before update on rental
    for each row
    declare
    rental_cost number;
    period number;
    total number;
    days_diff number;
    penalty number;
    begin
    select rental_period_days, rental_cost into period, total from movie, releasetype where movie.releasetype_id=release_type_id and movie.Id=:new.rentalId;
    if :new.checkin > :eek:ld.dueDate then
    days_diff := ceil(days_diff/period) * total;
    end if;
    if days_diff > 0 then
    penalty := ceil(days_diff / period) * total;
    end if;
    :new.total_price := :eek:ld.total_price + penalty;
    end;

    cheers
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This can't do anything since days_diff is NULL:

    Code (SQL):
     
    IF :NEW.checkin > :OLD.dueDate THEN
    days_diff := CEIL(days_diff/period) * total;
    END IF;
     
    Null divided by anything is still NULL so you are getting an update but with a NULL value. I think your second trigger should be coded like this:

    Code (SQL):
     
    CREATE OR REPLACE TRIGGER penalty
    BEFORE UPDATE ON rental
    FOR each ROW
    DECLARE
    rental_cost NUMBER;
    period NUMBER;
    total NUMBER;
    days_diff NUMBER;
    penalty NUMBER;
    BEGIN
    SELECT rental_period_days, rental_cost INTO period, total FROM movie, releasetype WHERE movie.releasetype_id=release_type_id AND movie.Id=:NEW.rentalId;
    IF :NEW.checkin > :OLD.dueDate THEN
    days_diff := CEIL(:new_checkin - :OLD.duedate);
    ELSE
    days_diff := 0;
    END IF;
    IF days_diff > 0 THEN
    penalty := CEIL(days_diff / period) * total;
    END IF;
    :NEW.total_price := :OLD.total_price + penalty;
    END;
     
    so that days_diff has a non-null value.
     
  5. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    yes your logic does make more sense than mine however, it is still not adding the price of the days_diff to the total_price which leads me to believe either there is no data in days_diff or my insert is incorrect or it has something to do with the auto increment.

    insert into rental(rentalid, bookingdate, bookingmethod, checkout, rentdays, checkin, duedate, historylevel, damage, payment_id, id, customerid, insuranceid, total_price, snacksid)
    values(seq_rentalId.nextval, sysdate, 'in store', sysdate, null, '05/nov/10', null, null, null, 2, '545GVC', 1000, 'N', null,7);

    olddog
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your 'troublesome' trigger is an UPDATE trigger; it won't fire until you UPDATE the rental table.
     
  7. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Problem fixed thanks
    the select statement was the problem I was calling :NEW.Id should have been OLD.Id
    thanks for the help