Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calling a Procedure from a Trigger Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 2:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 20, 2012 4:03 PM
Points: 4, Visits: 5
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!
Post #1347471
Posted Monday, August 20, 2012 2:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1347473
Posted Monday, August 20, 2012 2:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1347476
Posted Monday, August 20, 2012 2:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 20, 2012 4:03 PM
Points: 4, Visits: 5
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.
Post #1347479
Posted Monday, August 20, 2012 2:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1347481
Posted Monday, August 20, 2012 2:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 20, 2012 4:03 PM
Points: 4, Visits: 5
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

Post #1347485
Posted Monday, August 20, 2012 3:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1347491
Posted Monday, August 20, 2012 4:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 20, 2012 4:03 PM
Points: 4, Visits: 5
Yes, just as you wrote it.

<HappyDANCE>
Dance_Jig()
</HappyDANCE>
Post #1347501
Posted Tuesday, August 21, 2012 7:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1347763
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse