July 13, 2004 at 9:38 pm
Hello:
I am wondering, is it 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
July 16, 2004 at 8:00 am
This was removed by the editor as SPAM
July 16, 2004 at 11:47 am
Hi - Your "b" table has a field "tableb_tablea_id" that needs to be populated with a value related to your "a" table. Your
insert into dd1(tablea_value, tableb_value)
values('eee1b','eee1b');
does not include puting the primary Key of table "a" into the field "tableb_tablea_id" of table "b"
George
July 16, 2004 at 2:13 pm
Hi George:
You are correct. The "insert into dd1(tablea_value, tableb_value) values('eee1b','eee1b');" does not include primary Key of table "a". However, that is the essense of the question. Primary Key of Table "a" is not known at the time of insert. That is problem. I am wondering if there is a way to take the value and populate tableb_tablea_id automatically (since tablea_id value is automatically generated by the system).
Billy
July 18, 2004 at 9:36 pm
Billy - I would use a stored procedure to do this. Every insert and update I do is through a stored procedure. You can save the first table, then query for the PK of that table and then save the second table using the variable. I am sure you have a good reason to do an insert using a view, but I can't think of a good reason.
George
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply