-
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)
-
Bet go for a TRIGGER
Just add a WHEN clause to your TRIGGER
"WHEN order_header = 'SHIPPING' "
Cheers!
-
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
-
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.
-
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>
-
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;
