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!
ASKED:
Jun 12 2009 3:14 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _