Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Unhappy Unanswered: difference of :old.date and current date in days

    hi

    i want to create a trigger, where a record is deleted from installation, the instcost is refunded to the owner, ONLY if the deletion occurs within 30 days of the initial installation.
    the owner of the machine is the depart and the balance will be increased by the instcost. if the machine does not exist in depart, it is assumed to belong to the employee and his/her balance will be increased by the instcost.

    i've written the following...

    create or replace trigger refund
    before delete on installation
    for each row
    declare
    v_diff number;
    v_pctagnum number(5);
    v_mtagnum number(5);
    begin

    v_diff := sysdate - :old.instdate;
    select tagnum into v_pctagnum
    from pc
    where :old.tagnum NOT IN (select tagnum from machine);
    select tagnum into v_mtagnum
    from machine
    where :old.tagnum = tagnum;
    if v_diff <= 30 then
    if :old.tagnum = v_mtagnum then
    update depart
    set balance = balance + :old.instcost
    where deptno = (select deptno from machine where tagnum = :old.tagnum);
    elsif :old.tagnum = v_pctagnum then
    update employee
    set balance = balance + :old.instcost
    where empnum = (select empnum from pc where tagnum = :old.tagnum);
    end if;
    end if;
    end;

    i get the error...
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at "S214856.REFUND", line 8
    ORA-04088: error during execution of trigger 'S214856.REFUND'

    can someone tell me what i've done wrong? or how i can find the difference between dates where one is :old?

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Declare

    begin

    select XXXX in YYYY from tableName where KKKK=UUUU;
    Update....
    Delete...

    exception

    when no_data_found then
    Update...
    end;



    In the above example ..if select statement is not returing any data ..the control will directly go to exception handle ...without executing Update and delete statments.
    Your select ..into statement is not returning any data...So the No data found exceptiong is raising.


    Thanks,
    Pagnint
    (No need to search web before posting new question)

Posting Permissions

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