[dba-SQLServer] tsql help - propogate that pk

Billy Pang tuxedo_man at hotmail.com
Tue Jul 13 22:34:38 CDT 2004


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




More information about the dba-SQLServer mailing list