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!

ORA-02291 integrity issue

Discussion in 'General' started by lerono, Jan 5, 2012.

  1. lerono

    lerono Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I am working on a project that handles customer orders and inventory. I am having trouble with placing the order, I keep getting oracle error ora-02291. The issue lies with the CustOrderID. Any assistance is greatly appreciated.

    I am working within APEX and the formatting comes from there

    Create Tables:
    Code (Text):

    CREATE TABLE  "CUSTORDER"
       (    "CUSTORDERID" NUMBER(6,0) NOT NULL ENABLE,
        "ORDERDATE" DATE,
        "CUSTID" NUMBER(6,0) NOT NULL ENABLE,
        "DATECOMPLETE" DATE,
        "ORDERTOTAL" NUMBER(8,2),
         PRIMARY KEY ("CUSTORDERID") ENABLE
       ) ;ALTER TABLE  "CUSTORDER" ADD CONSTRAINT "CUSTORDER_FK" FOREIGN KEY ("CUSTID")
          REFERENCES  "CUSTOMER" ("CUSTID") ENABLE;


    CREATE OR REPLACE TRIGGER  "CUSTORDER_TRG"
        before insert on CustOrder
        for each row
        BEGIN
        select CustOrder_seq.nextval INTO :new.CustOrderID from dual;
        END;

    /
    ALTER TRIGGER  "CUSTORDER_TRG" ENABLE;


    CREATE TABLE  "ORDERLINE"
       (    "CUSTORDERID" NUMBER(6,0) NOT NULL ENABLE,
        "SKU" VARCHAR2(10) NOT NULL ENABLE,
        "LINEQTY" NUMBER(3,0) NOT NULL ENABLE,
        "UNITPRICE" NUMBER(4,2) NOT NULL ENABLE,
        "ORDERLINEID" NUMBER(8,0) NOT NULL ENABLE,
         CONSTRAINT "ORDERLINE_PK" PRIMARY KEY ("ORDERLINEID") ENABLE
       ) ;ALTER TABLE  "ORDERLINE" ADD CONSTRAINT "ORDERLINE_FK" FOREIGN KEY ("CUSTORDERID")
          REFERENCES  "CUSTORDER" ("CUSTORDERID") ENABLE;ALTER TABLE  "ORDERLINE" ADD CONSTRAINT "ORDERLINE_FK2" FOREIGN KEY ("SKU")
          REFERENCES  "PRODUCT" ("SKU") ENABLE;


    CREATE OR REPLACE TRIGGER  "ORDERLINE_TRG"
        before insert on OrderLine
        for each row
        BEGIN
        select OrderLine_seq.nextval INTO :new.OrderLineID from dual;
        END;


    /
    ALTER TRIGGER  "ORDERLINE_TRG" ENABLE;
     
    Create Sequence:
    Code (Text):

    CREATE SEQUENCE   "CUSTORDER_SEQ"  
    MINVALUE 1
    MAXVALUE 9999999999999999999999999999
    INCREMENT BY 1
    START WITH 1320
    CACHE 20
    NOORDER  
    NOCYCLE;
     
    Get new CustOrderID
    Code (Text):

    Select CustOrder_seq.nextval into l_CustOrderID from dual;
     
    Insert into CustOrder
    Code (Text):


    insert into CustOrder (CustOrderID, OrderDate, CustID, DateComplete, OrderTotal)
                    values(l_CustOrderID, sysdate, l_CustID, NULL, NULL);
     
    Insert into OrderLine
    Code (Text):

      insert into OrderLine
          (OrderLineID, CustOrderID, SKU, LineQty, UnitPrice)
          values (NULL, l_CustOrderID, x.c001, x.c004, x.c003);
     
     
  2. lerono

    lerono Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I found the error.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please post it as we're all interested in why this failed. I did notice that you failed to provide code for the custorder_seq sequence.
     
  4. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    try with the below insert stmt:

    insert into custorder(orderDate,custid,datecomplete,ordertotal) values(sysdate,l_custid,null,null);

    Refer the below example i think it wil be useful (I get it from Internet only)
    ------------------------------------------------------------------------
    Create Before Insert Trigger in SQL


    The Create Before Insert Trigger in SQL fires the trigger before we insert the value into the table.

    Understand with Example

    The given Tutorial describe you a code on 'Create Before Insert Trigger in SQL'. To grasp this example, we create a table 'Stu_Table'. The createtable is used to create a table 'Stu_Table' with field attribute and data type respectively.

    Create Table Stu_Table

    Create Table Stu_Table
    (Stu_Id int,Stu_Name Varchar(15),Sub1 int,Sub2 int,Sub3 int,
    Sub4 int,Sub5 int,total int,per float,status varchar(15));

    Create Trigger Stu_Insert

    Now, we create a Trigger 'Stu_Insert' on table stu_table. The Before Insert ON trigger is fired first before adding a records or rows to the table 'Stu_Table'.

    delimiter $$CREATE TRIGGER stu_insertBefore Insert ON stu_table FOR EACH ROW
    BEGIN set new.total = new.sub1 + new.sub2 +new.sub3 +
    new.sub4 +new.sub5;
    set new.per = new.total/5; if new.per<33 then
    set new.status="fail"; elseif new.per>=33 and new.per<45 then
    set new.status="3rd Div"; elseif new.per>=45 and new.per<60 then
    set new.status="2nd Div"; else
    set new.status="1st Div";
    end if;
    END$$
    delimiter ;

    Insert Data Into Stu_Table

    insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
    values (1, 'AAA', 6, 6, 6, 6, 6);

    insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
    values (2, 'BBB', 33, 33, 33, 33, 40);

    insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
    values (2, 'CCC', 45, 45, 45, 45, 50);

    insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
    values (2, 'DDD', 67, 67, 67, 67, 67);

    Stu_Table

    +------+--------+----+----+----+----+----+-----+------+----------+
    |Stu_Id|Stu_Name|Sub1|Sub2|Sub3|Sub4|Sub5|total| per | status |
    +------+--------+----+----+----+----+----+-----+------+----------+
    | 1 | AAA | 6 | 6 | 6 | 6 | 6 | 30 | 6 | fail |
    | 2 | BBB | 33 | 33 | 33 | 33 | 40 | 172 | 34.4 | 3rd Div |
    | 2 | CCC | 45 | 45 | 45 | 45 | 50 | 230 | 46 | 2nd Div |
    | 2 | DDD | 67 | 67 | 67 | 67 | 67 | 335 | 67 | 1st Div |
    +------+--------+----+----+----+----+----+-----+------+----------+


    Regards
    Samba
    samba.reddy@exora.com
     
    Sadik likes this.
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The original code includes before insert triggers; it's also been stated that the problem was solved by the person who posted it. As of yet we haven't seen the solution.