August 20, 2012 at 2:24 pm
I have 2 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!
August 20, 2012 at 2:27 pm
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!
The data does not yet exist in your table because you are calling this from the trigger. It is inside of an as yet uncommitted transaction.
For what it is worth you do not need a loop for this. Just do a select of the rows you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2012 at 2:33 pm
This seems to be the same thing as your loop.
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;
-- Insert statements for thisLoop
INSERT INTO tblC2NHistory (
NH_ID
,C2NID
,CID
,N_ID
,CA
)
SELECT @NH_ID
,CNID
,CID
,@N_ID
,CA
FROM dbo.tblC2N
WHERE NID = @N_ID
END
I would recommend that whenever possible you name your columns more verbosely. The super short abbreviations make it incredibly hard to work with and incredibly easy to make mistakes. It is possible these are all primary keys or something but they could still stand some clarity. Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2012 at 2:38 pm
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.
August 20, 2012 at 2:45 pm
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.
OK well now you have me confused. In your first post you said your looping was working but you didn't get data. Now you are saying the looping isn't working.
You really don't need a loop for this at all. There is no need for a loop. Loops in t-sql are terrible for performance and are more prone to coding errors because they are more complex than a simple select.
If the version I posted above still doesn't work you are going to have to provide some more details. We would need to have ddl (create table scripts), sample data (insert statements) and a clear explanation of desired output. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2012 at 2:52 pm
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
August 20, 2012 at 3:07 pm
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
You are welcome. Were you able to get rid of the loop altogether too?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2012 at 4:05 pm
Yes, just as you wrote it.
<HappyDANCE>
Dance_Jig()
</HappyDANCE>
August 21, 2012 at 7:05 am
KEVHOUSTON740 (8/20/2012)
Yes, just as you wrote it.<HappyDANCE>
Dance_Jig()
</HappyDANCE>
LOL 😀 Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply