I am using a trigger to create the primary key(Order_ID) for the orders table and one for the primary Key(Order_item_ID) of the Order_Items table.
I have two problems Here
A) I need to fire the Order_ID from the Orders table in to the Order_ID of the Order_Items table. In oracle I have set-up that Order_id in the Order_Items table to be a foreign Key but it does not work?
Is there anything else in Oracle or my asp script that is missing?
B) Once the order has gone though I would like to display the Order_ID to the customer, but this does not work because of the same problem.
Bellow is a copy of my ASP code. I hope you can help.
Because the order_id is being assigned in a database trigger, your ASP application has no way of knowing what value got assigned. A good way to overcome this would be to move the insert statement into a stored procedure, which would look something like this:
( p_Order_Date DATE,
p_order_id OUT NUMBER
INSERT INTO orders( Order_Date, Delivery_Address, CC_Details, ...)
VALUES (p_order_date, p_delivery_address, p_cc_details, ...)
RETURNING order_id INTO p_order_id;
Now you can call that procedure from ASP, passing the values as parameters, and it will return the new Order_ID.
Actually, the procedure should be made part of a package, as you will have other procedures like add_order_line, delete_order, ...
This approach also means you will be using BIND VARIABLES, which are crucial in all Oracle applications, rather than concatenating the values into the insert statement which is very bad practise (kills performance).