update header and detail from master

[chamilz]

My processes described below extract data from a Master Table and manupulate as below....

Step-1: ----------------------
DTS process will import text files into Master_Table in following order. From the Master_table
i have to create Header_Table and Details_Table to generate output text file. (Tables are described underneath)

Step:2 ----------------------

Header_Table PO_Number is an Identity column and henader records are extracted from Master_Table
group by DATE/PRODUCT/SUPP.

Step3:-----------------

Detail_Table contain all the product details selected from Master_Table and PO_Number is updated
using inner join with Header_Table.
-----------------------
I have implemented all these steps without using Triggers. But i feel it was not the proper way to
do this since i cant cover the following scenario.

If i insert a new records to Master_Table on the same Date/Product/Supp, i have problem in updating
my Header_Table and Detail_Line.

In order to achieve that I need to develop somthing like this,

------------------------------------
Fetch Master_Table(Date/Product/Supp/Size/Qty) where PO_Number ='   '

IF Master_Table(Date) = Heder_Table(Date) and
   Master_Table(Product) = Heder_Table(Product) and
   Master_Table(Supp) = Heder_Table(supp)  then

   Insert into Detail_Table
   Select  Master_Table(Product/size/qty)

ELSE

  Insert into Header_Table
  Select  MAster_Table(Date/Product/Supp)

  
  Insert into Detail_Table
  Select  Master_Table(Product/size/qty)


  Update DEtail_Table
  Po_number = Header_Table(po_number)
END

Tables:

1. MAster_Table

Date Product Supp Size Qty PO_Number
1/1/05 12345 AAA 10 2 88888
1/1/05 12345 AAA 20 2 88888
1/1/05 56789 BBB 10 4 88889
1/1/05 56789 BBB 30 5 88889
1/1/05 12345 AAA 30 8 88888

---------------
2.Header_Table

PO Number Date Product Supp
88888 1/1/05 12345 AAA
88889 1/1/05 56789 BBB


po_number int identity(88888,1)


3.Detail_Table

PO_Number PO_Line Product size Qty
88888 1 12345 10 2
88888 2 12345 20 2
88888 3 12345 30 8
88889 1 56789 10 4
88889 2 56789 30 5



Appriciate your advice and help.

Thanks