[dba-SQLServer] tsql help - propogate that pk

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
>