Home » SQL & PL/SQL » SQL & PL/SQL » order and order_item trigger to send email
icon7.gif  order and order_item trigger to send email [message #201999] Tue, 07 November 2006 15:05 Go to next message
Messages: 1
Registered: November 2006
Junior Member
Hi all: Embarassed
I am stuck with a order trigger to send out email. Hope you guys' profession can do me a favor. I have two tables to store the orders and order_item detail.
The orders table is the header for order_no, customer_no, customer_name, address, order_date and etc. The other table is the order_item that has the real detail of the item info, and its schema is order_no, item_no, item_name, qty, and unit_price. So, these two tables join by the order_no. I have an after insert trigger on orders to send out the order confirmation email to our customer. So far, the trigger fires to send the email to the customer whenever the new order coming in, but there is a problem there is no order detail (like item_no, name, qty, and unit_price) info inside the email.
Our order entry form will save the info at the same time into the orders and order_item table. So when the new order is in, the trigger fires to send the email, but the problem is that at the time the trigger fires, the order detail (item_no, item_name, qty, and unit_price are not in the order_item table yet), so the email doesn't have order's detail. If I modify the trigger to be after insert on order_item, then it will send out multiple email to the customer, because the customer may order several items in one order_no, so it will save as many rows as the customer orders in the order_item and trigger fires as many as the new rows are in.
I've tried many different ways to solve this problem, but either there is no order detail in the email or it sends out multiple emails. I hope you guys can understand what I try to explain here and hope you can share your professional experience to help me out. Thank you very much. Razz
Re: order and order_item trigger to send email [message #202111 is a reply to message #201999] Wed, 08 November 2006 04:08 Go to previous message
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Do you really want to implemet the trigger for sending the email after palcing an order ?

Options you are having are

1. Sending email as part of scheduler where it is picking from another table with master detail data.
2. calling the Email sending script after inserting into Order Item table.
3. Or Even you can call the procedure to populate the Order item and then prrcedure to send the email (that purely depends on your application.

Previous Topic: Why Primary Key is always selected as an Integer ?
Next Topic: Help with sql query
Goto Forum:

Current Time: Tue Jul 25 02:10:09 CDT 2017

Total time taken to generate the page: 0.11622 seconds