Trigger to insert multiple records into related table

I'm very new to triggers (and stored procedures), I've done a bit of searching and can't seem to find the right direction. Any help would be greatly appreciated.

Here is my situation: Thanks in advance!

I have 2 tables: Cat_Control (parent), Det_Control (child). They are related via an auto-incrementing ID (Cat_Control_ID)

When the user enters a new record into the Cat_Control table, I would like to automatically fill in several default records into the Det_Control table. These default records will be pre-defined based on the Cat_Control.Name field from the record that was just added to the Cat_Control table.

I'm sure this is way off base but I hope it will help to clarify what I'm looking to do:

1) For Each Cat_Control record added

2) insert record into Cat_Control table

3) set Cat_Control_ID = @@Identity (for record just added)

4) set Cat_Control_Name = Cat_Control.Name (for record just added)

5) Insert record into Det_Control table

5a)

Select Case Cat_Control_Name

case 'x'

insert record

case 'y'

insert record

case 'z'

insert record

6) loop back to step 5, to create 3 more "default records" into Det_Control table

7) loop back to Step 1 for next record

Thanks again guys (and gals) for your help.

A few things:

• It looks to me like you are blindly performing cursor-based inserts; normally, this should be replaced with a set-based process in which a single INSERT command is responsible for inserting all of the child records
• It looks like you are propagating the same "cat_control_name" from the parent column to the child column; redundant data such as this should not be retained
• Adding a second "cursor loop" layer is usually a very bad practice; again, use as set based process whenever possible
• There should be no need to use @@identity in the trigger; your key data should come from the INSERTED table; in addition, it is normally a bad practice to use @@identity; normal practice for situations in which you might otherwise use @@identity normally call for the use of the SCOPE_IDENTITY function; look up these features in books online
• Look up the examples for triggers in books online
• How do the child and parent tables differ from each other Is all of the data for the child table gleaned from the parent table

Dave

Thanks for the response Dave. The table structures are as follows:

Parent Table: Cat_Control

Cat_Control_ID (primary key, int)

Cat_Control_Name (nVarChar)

Child Table: Det_Control

Det_Control_ID (primary key, int)

Cat_Control_ID (foreign key, int)

Cat_Control_Name (nVarChar)

Det_Control_Answer (nVarChar)

I appreciate your points---I thought I needed to use the @@Identity (or Scope_Identity) or one of it's breathren in order to properly associate the related tables.

There are a total of 20 different predefined entries for Cat_Control.Cat_Control_Name (these are predetermined from a combobox), the related records in the Det_Control table are slightly different depending on the Cat_Control_Name.

Essentially the user will select a "Cat_Control_Name" from a combobox on the windows form, this action creates several records in the Cat_Control Table. For each record in the Cat_Control table I would like to insert several related Det_Control records.

Again, I appreciate your help, I'm certainly not looking for handouts (or someone to write the code) just a few pointers on where to start. As I mentioned, I'm new to Triggers and Stored Procedures so I'll look into "set based process" as you suggest. I'll keep digging in the forums and books too.

Thanks again!

Pat

You might be able to get by with something like this:

create trigger dbo.iTr_cat_control
on dbo.Cat_Control
for insert
as

begin

create trigger dbo.iTr_cat_control
on dbo.Cat_Control
for insert
as

begin

-- ------------------------------------------------------------
-- This trigger works by taking the cross product of each
-- row inserted into the cat_control table with each of the
-- 4 possible answers. This avoids a looping or 2-level
-- level looping process.
-- ------------------------------------------------------------
insert into dbo.det_control
( cat_control_id,
cat_control_name,
det_control_answer
)
select i.cat_control_id,
i.cat_control_name,
a.answer
from inserted i
cross join
( -- replace the 'Case' with whatever it should be
select 1 as caseId, 'First Answer' as answer union all
select 2 as caseId, 'Second Answer' as answer union all
select 3 as caseId, 'Third Answer' as answer union all
select 4 as caseId, 'Fourth Answer' as answer
) a

end

go

insert into cat_control values (1, 'This is a test.')
select * from det_control where cat_control_id = 1

-- ----------- Sample Output: ------------

-- det_control_id cat_control_id cat_control_name det_control_answer
-- -------------- -------------- ------------------------------ ------------------------------
-- 1 1 This is a test. First Answer
-- 2 1 This is a test. Second Answer
-- 3 1 This is a test. Third Answer
-- 4 1 This is a test. Fourth Answer

Another thought is that if this looks right to you it might be a good idea to change DET_CONTROL from a table into a view.

Dave

Dave---thank a million for your help, I can't tell you how much I appreciate it.

Your advice and patience has been incredibly helpful. I hope to return the favor someday.

Hi Waldrop,

Thanks for the code snippet, it is perfectly suits to my need.

I have a scenario

1) I have 2 Databases

a) Db1

b) Db2

On Db1 Database I have a table Db1_Table, having a column Name1 varchar (20)

On Db2 Database I have another table Db2_Table, having similar column as Name1.

(Rather any table containing any structure)

With the code you have provided, I could able to insert a record across the database.

But I have a doubt, If insert on Second Table ( Db2_Table of the Db2 Database) fails,

how can I prevent inserting into First Table (Test_Table of Db1) and throws error message back to user.

Am I clear to you

Thanks in advance

Bapu