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!