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

Thread: Database/table Trigger or procedure

  1. #1
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    Need your help:

    I altered order_header and added shipping information fields: street_1, street_2, city,
    state_province, county, zip, country, and phone. I need help with a trigger or procedure
    that will take the record of the shipping fields from cust_address table and populate the order_header.
    The address_type_cd field in cust_address can be either 'SHIPPING' or ‘BILLING'.
    I want only to store in order_header only 'SHIPPING' records. We do not want to alter the constraint
    in order_header and so I am having problem writing a trigger or procedure to solve the problem.
    Your help will
    be highly appreciated.

    Here are the desc. of both tables.


    SQL> desc cust_address;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    CUSTOMER_ID VARCHAR2(15)
    ADDRESS_ID NOT NULL VARCHAR2(15)
    CATEGORY_TYPE NOT NULL VARCHAR2(25)
    ADDRESS_TYPE_CD NOT NULL VARCHAR2(25)
    ADDRESS_NAME NOT NULL VARCHAR2(25)
    IS_DEFAULT NUMBER(5)
    COMPANY VARCHAR2(50)
    FIRST_NAME VARCHAR2(25)
    LAST_NAME VARCHAR2(25)
    STREET_1 VARCHAR2(50)
    STREET_2 VARCHAR2(50)
    CITY VARCHAR2(25)
    STATE_PROVINCE VARCHAR2(25)
    COUNTY VARCHAR2(25)
    ZIP VARCHAR2(11)
    COUNTRY VARCHAR2(50)
    PHONE VARCHAR2(15)
    IS_RESIDENTIAL NOT NULL NUMBER(3)
    ACTIVE NUMBER(5)
    CHANGED_BY NOT NULL VARCHAR2(25)
    CHANGED_DATE NOT NULL DATE
    CELL_PHONE VARCHAR2(15)
    WORK_PHONE VARCHAR2(15)
    FAX VARCHAR2(15)


    SQL> desc order_header;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ORDER_ID NOT NULL VARCHAR2(25)
    ORDER_DATE NOT NULL DATE
    CUSTOMER_ID NOT NULL VARCHAR2(25)
    BILLING_ADDRESS_ID NOT NULL VARCHAR2(25)
    SHIPPING_ADDRESS_ID NOT NULL VARCHAR2(25)
    PAYMENT_INFO_ID NOT NULL VARCHAR2(25)
    SITE_ID VARCHAR2(25)
    ORDER_TOTAL NUMBER(7,2)
    STATUS VARCHAR2(25)
    CONFIRMATION_NUM VARCHAR2(25)
    PAYMENT_METHOD VARCHAR2(25)
    SALES_TAX NUMBER(5,2)
    SHIPPER_ID VARCHAR2(25)
    DESTINATION_ID VARCHAR2(25)
    SHIPPING_HANDLING NUMBER(5,2)
    PO_NUMBER VARCHAR2(25)
    HTTP_REFERER VARCHAR2(255)
    GC_NUMBER VARCHAR2(25)
    GC_AMOUNT NUMBER(7,2)
    PROMO_CODE VARCHAR2(10)
    PROMO_AMOUNT NUMBER(19,4)
    GIFT_WRAPYN NUMBER(5)
    GIFT_MESSAGE VARCHAR2(150)
    SEND_GIFT_CARD NUMBER(10)
    COMMENTS VARCHAR2(255)
    CHANGED_BY NOT NULL VARCHAR2(25)
    CHANGED_DATE NOT NULL DATE
    PROMO_ID VARCHAR2(15)
    ORDER_TYPE VARCHAR2(20)
    STREET_1 VARCHAR2(50)
    STREET_2 VARCHAR2(50)
    CITY VARCHAR2(25)
    STATE_PROVINCE VARCHAR2(25)
    COUNTY VARCHAR2(25)
    ZIP VARCHAR2(25)
    COUNTRY VARCHAR2(50)
    PHONE VARCHAR2(25)


  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Bet go for a TRIGGER

    Just add a WHEN clause to your TRIGGER
    "WHEN order_header = 'SHIPPING' "
    Cheers!
    Cheers!
    OraKid.

  3. #3
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    Hi balajiyes,

    Thanks. Can you give me the trigger syntax even though the company wants to go with a procedure instead? How would you pass values to NOT NULL fields in order_header which are not in cust_address table?
    Thanks for your help


    CK

  4. #4
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    Hi balajiyes,
    The NOT NULL values will be passed from website fields but I have to place them as variables on the procedure. The procedure will be called by the web Administrator.

  5. #5
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175

    Table trigger

    Someone help with this please: Basically, I am trying to write ON INSERT trigger that fetch shipping records from cust_address table and populate order_header the moment order information are entered through the website or other means. The trigger will kick off. Customer_id is the unique identifier.
    Thanks.

    1 CREATE OR REPLACE TRIGGER ORD_SHIPPING
    2 AFTER INSERT ON ORDER_HEADER
    3 FOR EACH ROW
    4 DECLARE
    5 v_order_id varchar2(25);
    6 V_order_date date;
    7 v_customer_id varchar2(15);
    8 v_billing_address_id varchar2(25);
    9 v_shipping_address_id varchar2(25);
    10 v_changed_by varchar2(25);
    11 v_changed_date varchar2(25);
    12 err_num NUMBER;
    13 err_msg VARCHAR2(250);
    14 msg_date DATE;
    15 duplicate_info EXCEPTION;
    16 PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
    17 BEGIN IF (
    18 v_order_id =:new.order_id;
    19 v_order_date =:new.order_date;
    20 v_customer_id =:new.customer_id;
    21 v_billing_address_id =:new.billing_address_id;
    22 v_shippping_address_id =:new.shipping_address_id;
    23 v_changed_by =:new.changed_by;
    24 v_changed_date =:new.changed_date);
    25 --We need to make sure that oder info matches with shipping address info
    26 begin
    27 SELECT CUSTOMER_ID INTO V_CUSTOMER_ID
    28 FROM CUST_ADDRESS
    29 WHERE CUSTOMER_ID =:NEW.CUSTOMER_ID);
    30 END IF;
    31 --if the reords matches then
    32 IF INSERTING THEN
    33 INSERT INTO ORDER_HEADER (
    34 street_1,
    35 street_2,
    36 zip,
    37 city,
    38 county,
    39 state_province,
    40 country,
    41 phone)
    42 select
    43 street_1,
    44 street_2,
    45 zip,
    46 city,
    47 county,
    48 state_province,
    49 country,
    50 phone
    51 FROM CUST_ADDRESS
    52 where customer_id =:new.customer_id
    53 and address_type_cd ='SHIPPING';
    54 WHEN OTHERS THEN
    55 err_num := SQLCODE;
    56 err_msg := SUBSTR(SQLERRM, 1, 250);
    57 msg_date:= SYSDATE;
    58 INSERT INTO user_exc_hdl
    59 VALUES (err_num, err_msg, msg_date);
    60 --EXCEPTION
    61 WHEN duplicate_info THEN
    62 RAISE_APPLICATION_ERROR (num=> -20107,msg=> 'Duplicate customer ID');
    63 end;
    64* END;
    QL> /

    arning: Trigger created with compilation errors.

    QL> show errors;
    rrors for TRIGGER ORD_SHIPPING:

    INE/COL ERROR
    ------- -----------------------------------------------------------------
    5/26 PLS-00103: Encountered the symbol ";" when expecting one of the
    following:
    . ( ) , * @ % & - + / at mod rem and or ||
    indicator

    QL>

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Read more carifully about syntax of pl/sql:

    17 BEGIN IF (
    18 v_order_id =:new.order_id;
    19 v_order_date =:new.order_date;
    20 v_customer_id =:new.customer_id;
    21 v_billing_address_id =:new.billing_address_id;
    22 v_shippping_address_id =:new.shipping_address_id;
    23 v_changed_by =:new.changed_by;
    24 v_changed_date =:new.changed_date);
    25 --We need to make sure that oder info matches with shipping address info
    26 begin
    27 SELECT CUSTOMER_ID INTO V_CUSTOMER_ID
    28 FROM CUST_ADDRESS
    29 WHERE CUSTOMER_ID =:NEW.CUSTOMER_ID);
    30 END IF;

    statment like this doesn't exists in pl/sql probably in anothers languages too.

    BEGIN
    IF v_order_id = :new.order_id and
    v_order_date =:new.order_date and
    v_customer_id =:new.customer_id and
    ...
    then
    begin
    SELECT CUSTOMER_ID INTO V_CUSTOMER_ID
    FROM CUST_ADDRESS
    WHERE CUSTOMER_ID =:NEW.CUSTOMER_ID);
    exception when no_data_found then ....; -- for example
    end;
    END IF;

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