[dba-SQLServer] tsql help - propogate that pk

Hi Billy,

You didn't ask about inserting several rows using instead of insert
trigger, did you? :)

Well, I think it can be done without cursors but we have to assume that
there is no UNIVERSAL solution for such a case - I mean that for each
certain case the cursor-less solution with instead of triggers could
have quite different implementation - as always in programming an
"optimal" solution is a balance of interests, a compromise:

So if we assume that when several rows are inserted against my sample
view then this means that:

1. they all belong to one order
2. product name is unique

Then one of the the solutons is:

- insert all rows into tblProduct from inserted if there is no yet such
product,
- insert one row into tblOrder from inserted,
- insert all rows into tblOrdItem from inserted joined to tblProduct on
ProdName

See SQL script in P.S. of this message.

If you need something more complicated - e.g. inserting several orders
against view like in the current sample avoiding cursors' usage by all
means - if you need that - then you're welcome to hire me to do that and
other tricky work! :)

HTH,
Shamil

P.S. Solution:


CREATE TRIGGER [qselInsert] ON [dbo].[qselOrder]
INSTEAD OF INSERT
AS
declare @ordID int

Set NoCount On

-- /////////////////////////////////////////////////
Insert Into [dbo].[tblProduct]
(
   [prodName]
 , [prodCurPrice]
)
select
   prodName
 , prodCurPrice
from inserted where (prodName not in (select prodName from tblProduct))

-- /////////////////////////////////////////////////
Insert Into [dbo].[tblOrder]
(
   [ordDate]
 , [custId]
 , [ordCurTotal]
)
select top 1
   ordDate
 , custId
 , ordCurTotal
from inserted

Set @ordID = Cast(SCOPE_IDENTITY() As [int])
-- /////////////////////////////////////////////////
Insert Into [dbo].[tblOrderItem]
(
   [ordId]
 , [prodId]
 , [oitAmount]
)
select
   @ordId
 , p.prodId
 , oitAmount
from inserted i inner join tblProduct p on i.prodName = p.prodName
-- /////////////////////////////////////////////////

Set NoCount Off
----- Original Message ----- 
From: "Billy Pang" <tuxedo_man at hotmail.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Friday, July 16, 2004 1:18 AM
Subject: Re: [dba-SQLServer] tsql help - propogate that pk


> Hi Shamil:
>
> Thanks for your assistance.  Your solution will only work based on the
> assumption that records can only be inserted into tblOrder one at a
time; if
> insert statement for tblOrder table has "select" as source, then the
> tblorderid fk in tblorderitem table may contain incorrect value.
>
> Using below example as example, execute the following insert
statements:
>
> <--- cut here --->
> insert into qselOrder(ordDate, custId, ordCurTotal, prodName,
prodCurPrice,
> oitAmount)
> values(getdate(),1,2,'product name',4,5)
>
> insert into qselOrder(ordDate, custId, ordCurTotal, prodName,
prodCurPrice,
> oitAmount)
> select ordDate, custId, ordCurTotal, prodName, prodCurPrice, oitAmount
from
> qselOrder
>
> insert into qselOrder(ordDate, custId, ordCurTotal, prodName,
prodCurPrice,
> oitAmount)
> select ordDate, custId, ordCurTotal, prodName, prodCurPrice, oitAmount
from
> qselOrder
> <--- cut here --->
>
> assuming tblorder table is empty from very beginning, it will insert
four
> records into tblorder table as expected and four records into
tblorderitems
> table as expected.  however, the fourth record in tblorder table does
not
> have any related records in tblorderitems.
>
> One way around this problem is to load the values within the
"inserted"
> pseudo table into a table variable (assuming ss2k) or temporary table
and
> loop through them using database cursor.  However, I am wondering if
there
> is another way.
>
> sincerely,
> Billy
>