February 6, 2013 at 10:51 am
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
February 6, 2013 at 11:12 am
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?
February 6, 2013 at 11:28 am
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) :).
February 6, 2013 at 11:32 am
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
February 6, 2013 at 12:05 pm
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
February 6, 2013 at 12:13 pm
what is the name of the identity column in the table [tb_lkp_material_prod_version]?
Lowell
February 6, 2013 at 12:29 pm
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.
February 6, 2013 at 12:41 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2013 at 1:28 pm
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
February 6, 2013 at 2:22 pm
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
February 6, 2013 at 2:50 pm
Thanks 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?
February 6, 2013 at 3:31 pm
oops, dup.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply