Insert Trigger for Parent/Child

I am having problems creating a trigger in SQL Server? I have 2 tables (parent and child) with one to many relationship. When I save a record, one row gets inserted in the parent and one to many gets inserted in the child. The trigger is on the parent table and it is trying to select the number of new records just inserted in the child table that meets a certain criteria. Since the transaction hasn't been committed I can not select the number of records from the child. Does anyone know how to handle this? My manager insists this be done in a trigger.

Thanks,
James

Where did you put the insert logic to the child table? I mean did you insert 1~N row(s) into child table in the insert trigger of parent table? Anyways I suppose you did like I say, as it helps to matainence the data consistency. Then you can add a column to parent table, which is used to record effected rows in child by the row. And in the insert trigger of parent table, let's declare a INT variable with initial value 0, every time an insert to child table will cause the variable increased by 1. After all required rows have been inserted into child, update the row in parent table with the INT variable. Something like this:

create trigger trg_ins_Parent on tbl_Parent for insert
as
begin
declare @.i int
set @.i=0
while(...)
begin
insert into tbl_Child select val1,val2,...
set @.i=@.i+1
end
update tbl_Parent setChildCnt=@.i
where rowid=inserted.rowid
end
go

Insert single Parent and multiple Children

I am working on a project where I have a page that will have a parent record (Product) and then 1 or more children (options available for the product, user enters text to define) displayed in a table/gridview. There is a relationship defined in the database between the product and options table).

My question is how can I allow the user to add the product info and then within the same page also add the options and only then save it all? The options will added to a table.

Thanks for any help

You may use triggers, I mean you can create triggers on the main table, in which you can insert records to other tables. You can start from here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4nxu.asp

|||I don't see how a trigger will help. I need to have a way to add the parent and all its children at once. How will a trigger help?

Insert Parent Child records...

Hello,

We have a complex functionality of migrating data from a single record into multiple parent child tables.

To give you an example, lets us assume that we have a single table src_orders in the source database. We have a parent Order table and a child OrderDetails table in the target database. We need to pick one row from src_orders and insert this row in the Order table, pick up its PK (which is an identity column) and then use this to insert rows (say 5) in the OrderDetails table.

Again, we go back to the source, take a row, insert it into Orders, pick up the Orders PK and insert n rows in OrderDetails.

As of now, we are using the following approach for achieving this functionality.

1. Get the identity generated from the target table and store both the source table id and the target table id in a recordset.

2. Use the recordset as the source to a foreachloop , using foreachADO enumerator

3. Use data flow tasks to get the fields from the parent table for the source id, that needs to be inserted into the target child table

In case I have not ended up confusing everyone, can anyone validate this or suggest a better approach? :)

Thanks,

Satya

If it aint broke, don't fix it. There's no one correct way of acheiving something.

if it were me I would want to process all of the OrderDetails at the same time and it sounds like that's not what you're doing. Like I say though, if it works for you and performs in the required time, stick with it!

-Jamie

|||

As Jamie says, SSIS is a platform tool meaning that the infrastructure typically provides many ways to solve the same problem. However, here are a couple of alternatives that may work for you if you are having issues with the current approach.

1) Wrap this functionality in a stored procedure, and call the SP for every row. The SP would insert the parent item, retrieve the @.@.identity (actually, scope_identity()) and then insert the child. Perf may suck since you're calling the SP for every single row instead of bulk operations.

2) Wrap this functionality in a ScriptTransform that does pretty much the same as the previous bullet, only in VB.Net instead of TSQL. Once again, be careful of perf.

3) Wrap this functionality in a view that utilizes an InsteadOf trigger for the inserts. There are many contraints when going down this route however so your mileage may vary. For instance I cannot recall offhand if table joins are supported in this scenario; BOL has a wealth of info.