KEVHOUSTON740 (8/20/2012)

---

I have 3 tables (1 parent and a child table) that are updated using one form. The child table is a "many to one" relationship with the parent. I am updating the child table first (if data has changed), then updating the parent. On the parent table I have a trigger that creates an entry into its history table. Then I want to copy the child table data into its history table. I am passing in 2 parameters from the Parent History trigger, then attempting to loop through the child tables to input their history. The looping is working, but I am not able to glean the data from the existing child table. Here is my code:

ALTER PROCEDURE [dbo].[uspNHistory]
-- Add the parameters for the stored procedure here
@NH_ID INT,
@N_ID INT
AS
DECLARE
@CNID INT,
@CID INT,
@CA NVARCHAR(50)

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


DECLARE thisLoop CURSOR FOR
SELECT CNID = @CNID,
CID = @CID, CA = @CA
FROM dbo.tblC2N WHERE NID = @N_ID

OPEN thisLoop
FETCH NEXT FROM thisLoop INTO @CNID, @CID, @CA
WHILE @@FETCH_STATUS = 0
BEGIN

-- Insert statements for thisLoop
INSERT INTO tblC2NHistory (NH_ID, C2NID,
CID, N_ID, CA)

VALUES (@NH_ID, @CNID, @CID, @N_ID, @CA)

FETCH NEXT FROM thisLoop INTO @CNID, @CID, @CA
END
CLOSE thisLoop
DEALLOCATE thisLoop

END

What I end up with is the number of rows of the child table into the child history table (Looping is correct) but only the values @NH_ID INT and @N_ID INT are entered. What am I doing wrong with @CNID, @CID, @CA?

Thanks!

KEVHOUSTON740 (8/20/2012)

---

The data does exist. I am looping through the data because there may be 1 or 20 child data sets, per parent, and i need to have the same number of rows in the history table. When I didn't loop through the child table I only saw one row being added to the child history table.

KEVHOUSTON740 (8/20/2012)

---

Sorry if I confused you. The looping was working as it was entering in the passed values from the trigger, but not entering the child data into the child history table.

With that being said your assistance/example was spot on and it is now working as it should. Thank you! I see now where I failed in getting the values for the 3 columns.

thanks so much!

KevHouston

KEVHOUSTON740 (8/20/2012)

---

Yes, just as you wrote it.

<HappyDANCE>
Dance_Jig()
</HappyDANCE>