[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


>From: "Shamil Salakhetdinov" <shamil at users.mns.ru>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] tsql help - propogate that pk
>Date: Fri, 16 Jul 2004 00:04:28 +0400
>
>Here is a sample, Billy:
>
>/// tables
>CREATE TABLE [dbo].[tblOrder] (
>  [ordID] [int] IDENTITY (1, 1) NOT NULL ,
>  [ordDate] [datetime] NOT NULL ,
>  [custId] [int] NOT NULL ,
>  [ordCurTotal] [money] NULL
>) ON [PRIMARY]
>GO
>
>CREATE TABLE [dbo].[tblOrderItem] (
>  [oitID] [int] IDENTITY (1, 1) NOT NULL ,
>  [ordId] [int] NOT NULL ,
>  [prodId] [int] NOT NULL ,
>  [oitAmount] [int] NOT NULL
>) ON [PRIMARY]
>GO
>
>CREATE TABLE [dbo].[tblProduct] (
>  [prodID] [int] IDENTITY (1, 1) NOT NULL ,
>  [prodName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
>  [prodCurPrice] [money] NOT NULL ,
>  [prodCategoryID] [int] NULL
>) ON [PRIMARY]
>GO
>
>/// view
>CREATE VIEW dbo.qselOrder
>AS
>SELECT     o.ordDate, o.custId, o.ordCurTotal, p.prodName,
>p.prodCurPrice, i.oitAmount
>FROM         dbo.tblOrder o INNER JOIN
>                       dbo.tblOrderItem i ON o.ordID = i.ordId INNER JOIN
>                       dbo.tblProduct p ON i.prodId = p.prodID
>
>// instead of insert trigger
>
>--
>////////////////////////////////////////////////////////////////////////
>///////
>
>CREATE TRIGGER [qselInsert] ON [dbo].[qselOrder]
>INSTEAD OF INSERT
>AS
>declare @ordID int, @prodId int
>
>Set NoCount On
>
>-- /////////////////////////////////////////////////
>Insert Into [dbo].[tblProduct]
>(
>    [prodName]
>  , [prodCurPrice]
>)
>select
>    prodName
>  , prodCurPrice
>from inserted
>
>Set @prodID = Cast(SCOPE_IDENTITY() As [int])
>-- /////////////////////////////////////////////////
>Insert Into [dbo].[tblOrder]
>(
>    [ordDate]
>  , [custId]
>  , [ordCurTotal]
>)
>select
>    ordDate
>  , custId
>  , ordCurTotal
>from inserted
>
>Set @ordID = Cast(SCOPE_IDENTITY() As [int])
>-- /////////////////////////////////////////////////
>Insert Into [dbo].[tblOrderItem]
>(
>    [ordId]
>  , [prodId]
>  , [oitAmount]
>)
>select
>    @ordId
>  , @prodId
>  , oitAmount
>from inserted
>-- /////////////////////////////////////////////////
>
>Set NoCount Off
>
>HTH,
>Shamil
>
>P.S. See BOL for more details...
>
>----- Original Message -----
>From: "Billy Pang" <tuxedo_man at hotmail.com>
>To: <dba-sqlserver at databaseadvisors.com>
>Sent: Thursday, July 15, 2004 1:24 AM
>Subject: Re: [dba-SQLServer] tsql help - propogate that pk
>
>
> > how?  the "instead of insert" trigger can insert a record into tablea
>first
> > but how can I get the tablea_id of the records just inserted and
>insert
> > those values into tableb_tablea_id column?
> >
> > thanks in advance,
> > Billy
> >
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
Free yourself from those irritating pop-up ads with MSn Premium. Get 2months 
FREE*  
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines