Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I return an identity value to be used in an insert trigger on a table?


Can I return an identity value to be used in an insert trigger on a table?

Author
Message
njfuller
njfuller
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
Something along the lines of ....
create TRIGGER [dbo].[trg_tb1_Insert]
ON [dbo].[tb_tb1]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.tb2
SET tb2_id = SCOPE_IDENTITY()
FROM dbo.tb1
INNER JOIN Inserted ON Inserted.tb1_id = dbo.tb1.tb1_id
END
go
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18077
One of the issues you need to account for is that you can't assume that a single row is being inserted. The trigger fires once per insert operation, not one per row inserted.

Can you explain what you would actually intend in the case where more than one row is being inserted?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
njfuller
njfuller
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
In this case, the applications group is inserting only one tb1 record in a transaction.
1 or more tb2 records could be inserted.
I would like the identity value from the tb1 insert returned so I can use it to update on all the tb2 records.
(It would be nice if the appl would do this for me, but not in their scope) Smile.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38958
njfuller (2/6/2013)
In this case, the applications group is inserting only one tb1 record in a transaction.
1 or more tb2 records could be inserted.
I would like the identity value from the tb1 insert returned so I can use it to update on all the tb2 records.
(It would be nice if the appl would do this for me, but not in their scope) Smile.



i know you abstracted everything out (tbl1/tbl2) to simplify, but it doesn't look right.
if i insert into tbl1, you KNOW there is already a matching row in tbl2? or should that be an insert into tbl2 when tbl1 gets inserted?


create TRIGGER [dbo].[trg_tb1_Insert]
ON [dbo].[tb_tb1]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.tb2
SET tb2_id = Inserted.ColumnWithIdentity
FROM Inserted
WHERE Inserted.tb1_id = dbo.tb2.tb1_id
END



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

njfuller
njfuller
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
Sorry, here is the actual trigger (again sorry, I don't know how to put the code in a nice box like you did).
I'd like to replace the 'select' with the identity.

CREATE TRIGGER [dbo].[trg_tb_lkp_material_prod_version_Insert2]
ON [dbo].[tb_lkp_material_prod_version]
AFTER INSERT
AS

IF @@rowcount = 0 RETURN

BEGIN
SET NOCOUNT ON;

UPDATE dbo.tb_lkp_material_prod_version
SET bis_material_id =
(select m.material_id from tb_lkp_material m
where m.source_system_nbr = tb_lkp_material_prod_version.source_system_nbr
and m.source_material_nbr = tb_lkp_material_prod_version.source_material_nbr)
FROM dbo.tb_lkp_material_prod_version
INNER JOIN Inserted ON Inserted.material_prod_version_id = dbo.tb_lkp_material_prod_version.material_prod_version_id

END

GO
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38958
what is the name of the identity column in the table [tb_lkp_material_prod_version]?

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

njfuller
njfuller
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
I'll back up a bit.
In a single transaction, a material and a material_prod_version record will be inserted.
Each record has its own identity column.
The material_prod_version record has a column for the identity value of the material, but it is not filled.
I would like that column to be filled with the identity of the material so that I can do a join based on the pk, rather than the business keys.

It seems that since this is all done in a single transaction, that the data would be available to me somehow in the trigger.
The only reason I'd like to get the identity at the time of insert, is that it saves me a read to the database to retrieve the data.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
njfuller (2/6/2013)
I'll back up a bit.
In a single transaction, a material and a material_prod_version record will be inserted.
Each record has its own identity column.
The material_prod_version record has a column for the identity value of the material, but it is not filled.
I would like that column to be filled with the identity of the material so that I can do a join based on the pk, rather than the business keys.

It seems that since this is all done in a single transaction, that the data would be available to me somehow in the trigger.
The only reason I'd like to get the identity at the time of insert, is that it saves me a read to the database to retrieve the data.



The problem is the trigger code you posted will only work for single row inserts. It doesn't matter if today you have code that only does this one at a time. The time will come when multiple row inserts will happen and your trigger will leave you helpless. Triggers must be able to handle multiple row transactions. All the data is available you just have to learn how to best handle it. It is hard for us to help with the code because we don't have the table structure, sample data or desired output. Please take a few minutes and look at the first link in my signature.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
njfuller
njfuller
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
ok, thanks for the tips on posting.
Here's some code that I hope will help with my problem.
Also, I'm not understanding your comment on multiple inserts -- I want all child records to have the same parent key.

CREATE TABLE [dbo].[tb1_parent](
[tb1_parent_id] [int] IDENTITY(1001,1) NOT NULL,
[tb1_parent_bus_key] nvarchar(10))

CREATE TABLE [dbo].[tb2_child](
[tb2_child_id] [int] IDENTITY(2001,1) NOT NULL,
[tb2_parent_id] [int],
[tb2_child_bus_key] nvarchar(10))

BEGIN TRANSACTION
insert into tb1_parent (tb1_parent_bus_key) values ('A0001')
insert into tb2_child (tb2_child_bus_key) values ('A0001')
COMMIT

select * from tb1_parent
select * from tb2_child

-- note the null parent_id in the child record

create TRIGGER [dbo].[trg_tb1_parent_Insert]
ON [dbo].[tb2_child]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE tb2_child
SET tb2_parent_id =
(select tb1_parent_id from tb1_parent
where tb1_parent.tb1_parent_bus_key = tb2_child.tb2_child_bus_key)
FROM tb2_child
INNER JOIN Inserted ON Inserted.tb2_child_id = tb2_child.tb2_child_id
END

BEGIN TRANSACTION
insert into tb1_parent (tb1_parent_bus_key) values ('A0002')
insert into tb2_child (tb2_child_bus_key) values ('A0002')
COMMIT

select * from tb1_parent
select * from tb2_child

-- this is the result that I want, I just thought I might avoid the select statement

BEGIN TRANSACTION
insert into tb1_parent (tb1_parent_bus_key) values ('A0003')
insert into tb2_child (tb2_child_bus_key) values ('A0003')
insert into tb2_child (tb2_child_bus_key) values ('A0003')
COMMIT

select * from tb1_parent
select * from tb2_child

-- this is also the result on want, on multiple inserts to the child record
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38958
excellent job providing the sample DDL and data;
it made it very easy to change.

check out the changes i made to your trigger, i think it does exactly what you wanted:


CREATE TABLE [dbo].[tb1_parent](
[tb1_parent_id] [int] IDENTITY(1001,1) NOT NULL,
[tb1_parent_bus_key] nvarchar(10))

CREATE TABLE [dbo].[tb2_child](
[tb2_child_id] [int] IDENTITY(2001,1) NOT NULL,
[tb2_parent_id] [int],
[tb2_child_bus_key] nvarchar(10))

BEGIN TRANSACTION
insert into tb1_parent (tb1_parent_bus_key) values ('A0001')
insert into tb2_child (tb2_child_bus_key) values ('A0001')
COMMIT

select * from tb1_parent
select * from tb2_child

-- note the null parent_id in the child record
GO
create TRIGGER [dbo].[trg_tb1_parent_Insert]
ON [dbo].[tb2_child]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE tb2_child
SET tb2_parent_id = tb1_parent.tb1_parent_id
FROM Inserted
INNER JOIN tb1_parent
ON tb1_parent.tb1_parent_bus_key = Inserted.tb2_child_bus_key
where Inserted.tb2_child_id = tb2_child.tb2_child_id
END

BEGIN TRANSACTION
insert into tb1_parent (tb1_parent_bus_key) values ('A0002')
insert into tb2_child (tb2_child_bus_key) values ('A0002')
COMMIT

select * from tb1_parent
select * from tb2_child

-- this is the result that I want, I just thought I might avoid the select statement

BEGIN TRANSACTION
insert into tb1_parent (tb1_parent_bus_key) values ('A0003')
insert into tb2_child (tb2_child_bus_key) values ('A0003')
insert into tb2_child (tb2_child_bus_key) values ('A0003')
COMMIT

select * from tb1_parent
select * from tb2_child



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search