Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can I return an identity value to be used in an insert trigger on a table? Expand / Collapse
Author
Message
Posted Wednesday, February 6, 2013 10:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 2:26 PM
Points: 7, Visits: 25
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
Post #1416643
Posted Wednesday, February 6, 2013 11:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:24 PM
Points: 7,139, Visits: 15,191
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?
Post #1416652
Posted Wednesday, February 6, 2013 11:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 2:26 PM
Points: 7, Visits: 25
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) :).
Post #1416658
Posted Wednesday, February 6, 2013 11:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 12,910, Visits: 32,015
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) :).



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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1416660
Posted Wednesday, February 6, 2013 12:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 2:26 PM
Points: 7, Visits: 25
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
Post #1416676
Posted Wednesday, February 6, 2013 12:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 12,910, Visits: 32,015
what is the name of the identity column in the table [tb_lkp_material_prod_version]?

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1416679
Posted Wednesday, February 6, 2013 12:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 2:26 PM
Points: 7, Visits: 25
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.

Post #1416686
Posted Wednesday, February 6, 2013 12:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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)
Post #1416691
Posted Wednesday, February 6, 2013 1:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 2:26 PM
Points: 7, Visits: 25
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
Post #1416710
Posted Wednesday, February 6, 2013 2:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1416735
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse