Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    11

    Error in connecting tables

    Code:
     CREATE TABLE  "FACTORY_ORDER_LINE" 
       (	"ORDER_NUMBER" NUMBER, 
    	"FACTORY_ITEM_NUMBER" NUMBER, 
    	"QUANTITY" NUMBER, 
    	"PRICE" NUMBER, 
    	 CONSTRAINT "FACTORY_ORDER_LINE_CON" PRIMARY KEY ("ORDER_NUMBER", "FACTORY_ITEM_NUMBER") ENABLE
       )
    /
    
    CREATE OR REPLACE TRIGGER  "bi_FACTORY_ORDER_LINE" 
      before insert on "FACTORY_ORDER_LINE"              
      for each row 
    begin  
      if :new."ORDER_NUMBER" is null then
        select "FACTORY_ORDER_LINE_SEQ".nextval into :new."ORDER_NUMBER" from dual;
      end if;
    end;
    
    /
    ALTER TRIGGER  "bi_FACTORY_ORDER_LINE" ENABLE
    /
    
    
    
    CREATE TABLE  "FACTORY_ORDER" 
       (	"ORDER_NUMBER" NUMBER, 
    	"FACTORY_NUMBER" NUMBER, 
    	"DATE_ORDERED" DATE, 
    	"DEADLINE_DATE" DATE, 
    	"TOTAL_VALUE" NUMBER, 
    	 CONSTRAINT "FACTORY_ORDER_PK" PRIMARY KEY ("ORDER_NUMBER") ENABLE
       )
    /
    
    CREATE OR REPLACE TRIGGER  "bi_FACTORY_ORDER" 
      before insert on "FACTORY_ORDER"              
      for each row 
    begin  
      if :new."ORDER_NUMBER" is null then
        select "FACTORY_ORDER_SEQ".nextval into :new."ORDER_NUMBER" from dual;
      end if;
    end;
    
    /
    ALTER TRIGGER  "bi_FACTORY_ORDER" ENABLE
    /
    For some reason the fields factory number

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,627
    ERROR?
    What Error?
    I don't see any error.

    is COPY & PASTE broken for you.
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,006
    After I reformatted your code, no problem at all. You should REALLY think twice before using mixed case when creating objects. In my opinion, that's just stupid when working with Oracle. Therefore, I removed all double quotes you used and - here's the result.

    Code:
    SQL> CREATE SEQUENCE factory_order_line_seq;
    
    Sequence created.
    
    SQL> CREATE SEQUENCE factory_order_seq;
    
    Sequence created.
    
    SQL> CREATE TABLE factory_order_line
      2    (
      3       order_number        NUMBER,
      4       factory_item_number NUMBER,
      5       quantity            NUMBER,
      6       price               NUMBER,
      7       CONSTRAINT factory_order_line_con PRIMARY KEY (order_number,
      8       factory_item_number) ENABLE
      9    )
     10  /
    
    Table created.
    
    SQL> CREATE OR replace TRIGGER bi_factory_order_line
      2    BEFORE INSERT ON factory_order_line
      3    FOR EACH ROW
      4  BEGIN
      5      IF :new.order_number IS NULL THEN
      6        SELECT factory_order_line_seq.NEXTVAL
      7        INTO   :new.order_number
      8        FROM   dual;
      9      END IF;
     10  END;
     11  /
    
    Trigger created.
    
    SQL> ALTER TRIGGER bi_factory_order_line ENABLE
      2  /
    
    Trigger altered.
    
    SQL> CREATE TABLE factory_order
      2    (
      3       order_number   NUMBER,
      4       factory_number NUMBER,
      5       date_ordered   DATE,
      6       deadline_date  DATE,
      7       total_value    NUMBER,
      8       CONSTRAINT factory_order_pk PRIMARY KEY (order_number) ENABLE
      9    )
     10  /
    
    Table created.
    
    SQL> CREATE OR replace TRIGGER bi_factory_order
      2    BEFORE INSERT ON factory_order
      3    FOR EACH ROW
      4  BEGIN
      5      IF :new.order_number IS NULL THEN
      6        SELECT factory_order_seq.NEXTVAL
      7        INTO   :new.order_number
      8        FROM   dual;
      9      END IF;
     10  END;
     11  /
    
    Trigger created.
    
    SQL> ALTER TRIGGER bi_factory_order ENABLE
      2  /
    
    Trigger altered.
    
    SQL>

  4. #4
    Join Date
    Feb 2013
    Posts
    11
    I was using the sql workshop in built so was relying on the application to handle most of the code.

    However, thank you i will keep that in mind.

Posting Permissions

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