DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: trying to delete a row from a table

  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Smile trying to delete a row from a table

    Hi,

    I am trying to delete a row from a table
    delete from tasks where no = xxxxx
    and get the error:
    ORA-20411: Residual of 1 found. Insufficient data to unallocate.
    ORA-04088: error during execution of trigger 'SONICA.TASKS_AF_DEL'. I'm trying to understand the FK referential contraints. Here is a query to show the dependencies from the TASKS table that I'm trying to delete the rows from:

    TABLE_NAME: AREA_ZONE
    KEY_NAME: AREA_ZONE_PK
    REFERENCING TABLE: TASKS
    FOREIGN_KEY_NAME: FK_AREA_ZONE_TASKS
    FK_STATUS: ENABLED

    and the trigger that executes is:

    CREATE OR REPLACE TRIGGER "SONICA".tasks_af_del
    after delete on tasks
    for each row
    declare
    sLicNo tasks.lic_no%type;
    iInvKey tasks.inv_key%type;
    begin
    if :old.type in (TYPE_PK.rTaskType.put,TYPE_PK.rTaskType.trx,
    TYPE_PK.rTaskType.repl) then
    if :old.ord_key is not null and :old.ord_type = 'T' then
    if :old.unit > 0 then
    XORD_PK.Alloc(:old.ord_key,:old.ord_line_no,-:old.unit);
    else
    XORD_PK.UnAlloc(:old.no);
    end if;
    elsif :old.dst_location is not null then
    if :old.pick_no is not null then
    if :old.lic_no != :old.curr_location then
    sLicNo := :old.lic_no;
    end if;
    ORD_ALLOC_PK.UnAlloc(:old.no,:old.pick_no,:old.unit,sLicNo);
    else
    iInvKey := :old.inv_key;
    if iInvKey is not null and :old.ord_key is not null and
    :old.ord_type = 'C' then
    iInvKey := null;
    end if;
    end if;
    if :old.dst_location != :old.src_location then
    LOCATION_PK.Task_Capacity
    (:old.dst_location,:old.container,iInvKey,
    :old.uom_key,-1,
    :old.qty,:old.unit, :old.uom);
    end if;
    end if;
    TASKS_DETL_PK.Del(:old.no);
    elsif :old.type = TYPE_PK.rTaskType.pick then
    if :old.pick_no is null then
    TASKS_DETL_PK.Del(:old.no);
    else
    if :old.lic_no != :old.curr_location then
    sLicNo := :old.lic_no;
    end if;
    ORD_ALLOC_PK.UnAlloc(:old.no,:old.pick_no,:old.unit,sLicNo);
    end if;
    if :old.grp_no is not null and RULE_PK.IsAutoRemovePickGrp then
    PG_PK.WhenDelTask(:old.grp_no);
    end if;
    end if;
    if INTF_MFCS_FUNC and
    TASK_PK.bVoid and
    :old.p_no is null and
    :old.intf_no < 0 then
    TASK_PK.Cancel(:old.no,:old.type,:old.intf_no);
    end if;
    exception
    when others then
    ERROR_PK.Raise('TASKS_AF_DEL',sqlcode,'row=' || :old.rowid);
    raise;
    end;

    Can someone help me try to understand what is happening?

    Thanks!
    Bea Isabelle :)

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The ORA-20411 error isbeing raised through a call to RAISE_APPLICATION_ERROR in one of the procedures being called by the trigger -- you'll have to find the code that raises it, maybe by searching through user_source for "20411" or the associated text "Insufficient data to unallocate"
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width