开发者

Is there a way to get the inserted indentities in views?

So suppose we a table t1 and we need to copy every inserted row into t2 and put the foreign key link between them. Let me show what I mean:

We have table t1

-----------------------------
| id |  value  | external_id|
-----------------------------
|    |         |            |
-----------------------------

and table t2

----------------
| id |  value  |
----------------
|    |         |
----------------

where id columns have IDENTITY mark and external_id is a link to t2.id (I'll explain below)

We are going to insert row (value = 'TEST') into table t1. The expected result is something like this:

-----------------------------
| id |  value  | external_id|
-----------------------------
| 123| TEST    | 345        |
-----------------------------

and table t2

----------------
| id | value   |
----------------
| 345| TEST    |
----------------

The main idea of algorythm is based on triggers: in INSTEAD OF trigger on table1 I do:

  1. insert into t1 and remember the inserted id's in a separate table using OUTPUT clause
  2. insert into t2 and remember the mapping (t1.id, t2.id) again with OUTPUT
  3. update external_id column using the mapping

But there are some issues which I cannot figure how to fight with:

开发者_StackOverflow社区
  1. t1 is not a table actually, it's a view with INSTEAD OF trigger (but that's not a problem)
  2. t2 is also a view with a trigger an that's a problem because I cannot get the inserted id's from it
  3. moreover t2 is a remote view - and that's a big problem because there some restrictions in using remote views

So. Maybe I was moving in a wrong direction? Is there a way to solve my problem without using cursors and stored procedure in a trigger on t1? (I mean I know there's a way to put all my data into t2 with a stored procedure and get the inserted id's in OUTPUT parameter of that procedure but maybe there's more elegant way?)


Seems to me that if t1 needs the ID from t2 and not the other way around, you should make the INSTEAD of trigger on t1 insert into t2 first, then t1. You can use the OUTPUT clause on the insert into t2.


Use a Stored Procedure and the @@Identity from the first insert.


What about just getting the max ID on the table?

DECLARE @SomethingID INT
SELECT @SomethingID = MAX(SomethingID) FROM SomethingTable
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜