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:
- insert into t1 and remember the inserted id's in a separate table using
OUTPUT
clause - insert into t2 and remember the mapping
(t1.id, t2.id)
again withOUTPUT
- update
external_id
column using the mapping
But there are some issues which I cannot figure how to fight with:
开发者_StackOverflow社区- t1 is not a table actually, it's a view with
INSTEAD OF
trigger (but that's not a problem) - t2 is also a view with a trigger an that's a problem because I cannot get the inserted id's from it
- 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
精彩评论