SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calling a Procedure from a Trigger


Calling a Procedure from a Trigger

Author
Message
KEVHOUSTON740
KEVHOUSTON740
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65330 Visits: 17980
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.

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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65330 Visits: 17980
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.

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)
KEVHOUSTON740
KEVHOUSTON740
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65330 Visits: 17980
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.

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)
KEVHOUSTON740
KEVHOUSTON740
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65330 Visits: 17980
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.

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)
KEVHOUSTON740
KEVHOUSTON740
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
Yes, just as you wrote it.

<HappyDANCE>
Dance_Jig()
</HappyDANCE>
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65330 Visits: 17980
KEVHOUSTON740 (8/20/2012)
Yes, just as you wrote it.

<HappyDANCE>
Dance_Jig()
</HappyDANCE>


LOL :-D 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.

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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search