Calling a Procedure from a Trigger

  • 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!

  • 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/

  • 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/

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


    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/

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


    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/

  • Yes, just as you wrote it.

    <HappyDANCE>

    Dance_Jig()

    </HappyDANCE>

  • 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