[dba-SQLServer] tsql help - propogate that pk

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
>