InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 12:01:40 GMT20http://www.sqlservercentral.com/Forums/Topic1416643-23-1.aspxoops, dup.Wed, 06 Feb 2013 15:31:06 GMTnjfullerhttp://www.sqlservercentral.com/Forums/Topic1416643-23-1.aspxThanks for your help.I'll admit the 'join' is much cleaner than the 'select'.However, is this saving me an I/O to the database?I was hoping that returning the identity value from the insert to the parent table, and using it as the data to update the child table would save me I/O.Or is an I/O performed because the parent and child inserts are in the same transaction?Wed, 06 Feb 2013 14:50:16 GMTnjfullerhttp://www.sqlservercentral.com/Forums/Topic1416643-23-1.aspxexcellent 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:[code]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')COMMITselect * from tb1_parentselect * from tb2_child-- note the null parent_id in the child recordGOcreate TRIGGER [dbo].[trg_tb1_parent_Insert] ON [dbo].[tb2_child] AFTER INSERTASBEGINSET 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_keywhere Inserted.tb2_child_id = tb2_child.tb2_child_idENDBEGIN TRANSACTION insert into tb1_parent (tb1_parent_bus_key) values ('A0002')insert into tb2_child (tb2_child_bus_key) values ('A0002')COMMITselect * from tb1_parentselect * from tb2_child-- this is the result that I want, I just thought I might avoid the select statementBEGIN 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')COMMITselect * from tb1_parentselect * from tb2_child[/code]Wed, 06 Feb 2013 14:22:10 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1416643-23-1.aspxok, 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')COMMITselect * from tb1_parentselect * from tb2_child-- note the null parent_id in the child recordcreate TRIGGER [dbo].[trg_tb1_parent_Insert] ON [dbo].[tb2_child] AFTER INSERTASBEGINSET 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_idENDBEGIN TRANSACTION insert into tb1_parent (tb1_parent_bus_key) values ('A0002')insert into tb2_child (tb2_child_bus_key) values ('A0002')COMMITselect * from tb1_parentselect * from tb2_child-- this is the result that I want, I just thought I might avoid the select statementBEGIN 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')COMMITselect * from tb1_parentselect * from tb2_child-- this is also the result on want, on multiple inserts to the child recordWed, 06 Feb 2013 13:28:32 GMTnjfullerhttp://www.sqlservercentral.com/Forums/Topic1416643-23-1.aspx[quote][b]njfuller (2/6/2013)[/b][hr]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.[/quote]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.Wed, 06 Feb 2013 12:41:18 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1416643-23-1.aspxI'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.Wed, 06 Feb 2013 12:29:33 GMTnjfullerhttp://www.sqlservercentral.com/Forums/Topic1416643-23-1.aspxwhat is the name of the identity column in the table [tb_lkp_material_prod_version]?Wed, 06 Feb 2013 12:13:41 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1416643-23-1.aspxSorry, 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 INSERTASIF @@rowcount = 0 RETURNBEGIN 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_idENDGOWed, 06 Feb 2013 12:05:37 GMTnjfullerhttp://www.sqlservercentral.com/Forums/Topic1416643-23-1.aspx[quote][b]njfuller (2/6/2013)[/b][hr]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) :).[/quote]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?[code]create TRIGGER [dbo].[trg_tb1_Insert] ON [dbo].[tb_tb1] AFTER INSERTASBEGINSET NOCOUNT ON;UPDATE dbo.tb2SET tb2_id = Inserted.ColumnWithIdentityFROM Inserted WHERE Inserted.tb1_id = dbo.tb2.tb1_idEND[/code]Wed, 06 Feb 2013 11:32:34 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1416643-23-1.aspxIn 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) :).Wed, 06 Feb 2013 11:28:28 GMTnjfullerhttp://www.sqlservercentral.com/Forums/Topic1416643-23-1.aspxOne 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?Wed, 06 Feb 2013 11:12:28 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic1416643-23-1.aspxSomething along the lines of ....create TRIGGER [dbo].[trg_tb1_Insert] ON [dbo].[tb_tb1] AFTER INSERTASBEGIN SET NOCOUNT ON; UPDATE dbo.tb2 SET tb2_id = SCOPE_IDENTITY() FROM dbo.tb1 INNER JOIN Inserted ON Inserted.tb1_id = dbo.tb1.tb1_idENDgoWed, 06 Feb 2013 10:51:46 GMTnjfuller