none
Trigger to insert multiple records into related table RRS feed

  • Question

  • 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.

     

     

     

    Friday, January 12, 2007 5:00 PM

Answers

  • 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

    Friday, January 12, 2007 8:27 PM
    Moderator

All replies

  • 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

    Friday, January 12, 2007 6:35 PM
    Moderator
  • 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

     

     

    Friday, January 12, 2007 7:49 PM
  • 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

    Friday, January 12, 2007 8:27 PM
    Moderator
  • 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

    Friday, January 12, 2007 8:40 PM
    Moderator
  • 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.

     

     

    Friday, January 12, 2007 9:09 PM
  • 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

     

    Tuesday, January 23, 2007 7:07 PM