How to automatically update Child Table with Insert Trigger and Table-UDF

5 pts.
Tags:
SQL Server 2008
SQL Server triggers
T-SQL 2008
UDF
Visual Studio 2008
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!

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following