none
How to automatically insert records into Child Table with Insert Trigger and UDF RRS feed

  • Question

  • I have SQL Server 2008 and Visual Studio 2008 and am trying to automatically insert new records into a child table based on new inserts into its parent table.
    I know that this should be simple: via an insert trigger.  However, the complexity is that the new records into the child table need to be a parsed version of one of the parent table's fields.

    To achieve this, I developed a table function which parses this one field in the parent table.
    Originally I had the Foreign Key defined below, but this wasn't automatically inserting new records into my child table, so I commented it out.
    I have tried writing both recursive and non-recursive insert triggers, but I have received errors on almost every attempt.  I was able to partly get non-recursive triggers to work...but without the parsing function.  I think the trick is that this is a table function.

    How should I design this?  Some of my pseudo code as follows:

    CREATE TABLE ParentTable
    (
    ParentID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    strInput VARCHAR(8000) NULL
    )

    CREATE TABLE ChildTable
    (
    ChildID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    ParentID INT,
    FullRow VARCHAR(8000)
    --I removed the following FK cause it wasn't automatically inserting new records:
    --CONSTRAINT AddParentID FOREIGN KEY ParentID REFERENCES ParentTable(ParentID)
    )

    Sample output of what I want is:
    ParentTable:
    ParentID strInput
    1  'AAA|BB|CCCCCC|D|EE'
    2  'FFF|R|BC|D|EE'
    3  'GG|BB|CCHC|D|EE'

    ChildTable:
    ChildID  ParentID strInput
    1  1  'AAA'
    2  1  'BB'
    3  1  'CCCCCC'
    4  1  'D'
    5  1  'EE'
    6  2  'FFF'
    7  2  'R'
    8  2  'BC'
    9  2  'D'
    10  2  'EE'
    ...

    My table-function removes the "|" delimiter with strInput as its input.  How can I achieve the above results?

    Thanks!

     

    Friday, June 12, 2009 3:16 PM

Answers

  • Try:


    CREATE TABLE ParentTable
    (
    ParentID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    strInput VARCHAR(8000) NULL
    );
    GO
    
    CREATE TABLE ChildTable
    (
    ChildID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    ParentID INT,
    strInput VARCHAR(50)
    );
    GO
    
    CREATE TRIGGER tr_ParentTable_Ins ON ParentTable
    FOR INSERT
    AS
    INSERT INTO ChildTable (ParentID, strInput)
    SELECT
    	t1.ParentID, t2.value
    FROM
    	(
    	SELECT
    		ParentID, CAST('<i>' + REPLACE(CAST(strInput AS VARCHAR(MAX)), '|', '</i><i>') + '</i>' AS XML) AS x
    	FROM
    		inserted
    	) AS t1
    	CROSS APPLY
    	(
    	SELECT
    		c.value('.', 'VARCHAR(50)')
    	FROM
    		t1.x.nodes('//i') AS T(c)
    	)  AS t2([value])
    GO
    
    INSERT INTO ParentTable (strInput) VALUES ('AAA|BB|CCCCCC|D|EE');
    INSERT INTO ParentTable (strInput) VALUES ('FFF|R|BC|D|EE');
    INSERT INTO ParentTable (strInput) VALUES ('GG|BB|CCHC|D|EE');
    GO
    
    SELECT * FROM ChildTable;
    GO
    
    DROP TABLE ChildTable, ParentTable;
    GO

    You can find more info about how to split a delimited string, here in this link.

    Arrays and Lists in SQL Server


    Just for curiosity, can you explain what is it that you are trying to model?


    AMB
    Friday, June 12, 2009 4:04 PM
    Moderator

All replies

  • Try:


    CREATE TABLE ParentTable
    (
    ParentID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    strInput VARCHAR(8000) NULL
    );
    GO
    
    CREATE TABLE ChildTable
    (
    ChildID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    ParentID INT,
    strInput VARCHAR(50)
    );
    GO
    
    CREATE TRIGGER tr_ParentTable_Ins ON ParentTable
    FOR INSERT
    AS
    INSERT INTO ChildTable (ParentID, strInput)
    SELECT
    	t1.ParentID, t2.value
    FROM
    	(
    	SELECT
    		ParentID, CAST('<i>' + REPLACE(CAST(strInput AS VARCHAR(MAX)), '|', '</i><i>') + '</i>' AS XML) AS x
    	FROM
    		inserted
    	) AS t1
    	CROSS APPLY
    	(
    	SELECT
    		c.value('.', 'VARCHAR(50)')
    	FROM
    		t1.x.nodes('//i') AS T(c)
    	)  AS t2([value])
    GO
    
    INSERT INTO ParentTable (strInput) VALUES ('AAA|BB|CCCCCC|D|EE');
    INSERT INTO ParentTable (strInput) VALUES ('FFF|R|BC|D|EE');
    INSERT INTO ParentTable (strInput) VALUES ('GG|BB|CCHC|D|EE');
    GO
    
    SELECT * FROM ChildTable;
    GO
    
    DROP TABLE ChildTable, ParentTable;
    GO

    You can find more info about how to split a delimited string, here in this link.

    Arrays and Lists in SQL Server


    Just for curiosity, can you explain what is it that you are trying to model?


    AMB
    Friday, June 12, 2009 4:04 PM
    Moderator
  • You can use your table valued function with CROSS APPLY:

    CREATE TRIGGER ParentTableInsert
    ON ParentTable
    FOR INSERT
    AS

    INSERT INTO ChildTable (ParentID, FullRow)
    SELECT I.ParentID, F.FullRow
    FROM Inserted AS I
    CROSS APPLY dbo.MyParseFunction(I.strInput) AS F;

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Friday, June 12, 2009 4:11 PM
    Moderator
  • Thank you so much, Hunchback!  Your solution worked for me.  I spent a couple days on this problem. 

    What I am trying to ultimately achieve is to use C# Web form to insert new records into a SQL database where the user can select the input file and map each input file column with a different table/field in the database.  And then my program has to parse each line to the appropriate area.  Make sense?

    I have most of the non-SQL part working now. 
    Friday, June 12, 2009 4:34 PM
  • Ryan,

    I simulated the split function, but you can use yours, as Plamen stated.


    AMB
    Friday, June 12, 2009 5:38 PM
    Moderator
  • Thanks also to Plamen!  I am going to use Hunchback's solution, but it looks like I need to learn about the Cross Apply statement.
    Friday, June 12, 2009 6:33 PM
  • Yes, the APPLY operator is neat. Unfortunately not well documented. If
    you read BOL you can get the impressions it is intended only for use
    with functions. As Alejandro's example shows it can be used with
    queries, and also when parsing XML nodes, to calculate some values based
    on the main query, etc. There are many examples on the web...

    BTW, one catch with the XML solution to parse delimited list is if there
    are invalid XML characters in the data. Try this:

    INSERT INTO ParentTable (strInput) VALUES ('AA&A|BB|CCCCCC|D|EE');

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Saturday, June 13, 2009 2:19 AM
    Moderator