[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


>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: Wed, 14 Jul 2004 23:15:55 +0400
>
>Billy,
>
>I think that "Instead of Insert" trigger should help you to do the
>trick.
>
>Shamil
>
>----- Original Message -----
>From: "Billy Pang" <tuxedo_man at hotmail.com>
>To: <dba-SQLServer at databaseadvisors.com>
>Sent: Wednesday, July 14, 2004 7:34 AM
>Subject: [dba-SQLServer] tsql help - propogate that pk
>
>
> > Hello:
> >
> > I am wondering, is possible to propogate the values of primary key
>into a
> > foreign key of another table from a view insert.
> >
> > To illustrate, please see following illustration:
> >
> > /* ---- cut here ---- */
> >
> > use tempdb
> > go
> > begin tran
> > set nocount on
> > go
> > create table tablea(tablea_id int not null identity(1,1) primary key,
> > tablea_value varchar(50)
> > );
> > go
> >
> > create table tableb(tableb_id int not null identity(1,1) primary key,
> > tableb_tablea_id int constraint fk_tableb_tablea_id foreign key
>references
> > tablea(tablea_id),
> > tableb_value varchar(50)
> > );
> > go
> > create view dd1 as
> > select tablea_id, tablea_value, tableb_id, tableb_value from tablea
>inner
> > join tableb on tablea_id = tableb_tablea_id
> > go
> > insert into dd1(tablea_value, tableb_value) values('eee1b','eee1b');
> >
> > /* above insert results with "Server: Msg 4405, Level 16, State 2,
>Line 1"
> > */
> >
> > /* View or function 'dd1' is not updatable because the modification
>affects
> > multiple base tables. */
> > go
> > rollback;
> > /* ---- cut here ---- */
> >
> > In the above example, tableb has tablea_id fk.  I have a view that
>joins
> > those two tables together.  When a record is insert into the view, I
>would
> > like it to automatically populate both tablea and tableb (yes, that
>includes
> > having the tableb_table_id column populated with the value of the
>tablea_id
> > column that was just created).  Sometimes, the insert statement that
> > populates the view might use a SELECT as a source.  Can this be done
> > (without using a db cursor)?
> >
> > thanks in advance
> >
> > Billy
> >
> > _________________________________________________________________
> > MSN Premium: Up to 11 personalized e-mail addresses and 2 months FREE*
> >
>http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
>
>_______________________________________________
>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