asked on

I am not able to retrieve the values from a Trigger calling a stored procedure?

Please help! Hi, I'm trying to generate retrieve default values from a master table when I insert a detail table.

I am trying to do the following:

A. using a Stored Procedure to retrieve the values from and updating the master table and update
B. using a 'Trigger' to insert default data into a detail table by calling my stored procedure with return values.

The problem is that I am not able to retrieve the values?

Trigger

ALTER TRIGGER Citations.InsertCaseNo_LogNo
ON Citations.LogHeader
AFTER INSERT
AS
DECLARE
       @trgLogNo AS NVARCHAR(10),
       @trgLogType AS NVARCHAR(10),
       @trgLogTypeLogNo AS NVARCHAR(10),
       @retval int
       ;
BEGIN
       SET NOCOUNT ON;
       EXEC Citations.usp_RegNextCaseNo @trgLogNo OUTPUT,@trgLogType OUTPUT; --Execute Stored Procedure to retreive Defaluts
       SELECT @trgLogTypeLogNo = i.LogNo From Inserted I
       SET @trgLogTypeLogNo = @trgLogType + @trgLogNo
END;

Stored Procedure
The Stored Procedure returns values when executed separately

USE [TR_CASEMANAGER]
GO
/****** Object: StoredProcedure [Citations].[usp_RegNextCaseNo]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Citations].[usp_RegNextCaseNo]
@DefaultCaseNo NVARCHAR(10) OUTPUT,
@DefaultCaseType NVARCHAR(10) OUTPUT
AS

SELECT
@DefaultCaseNo = DefaultCaseNo,
@DefaultCaseType = (SELECT CitationID FROM Maintenance.LogTypeCodes WHERE INT_ID = DefaultLogTypeCode)
FROM SysConfiguration.MasterConfiguration
WHERE INT_ID = 1

UPDATE SysConfiguration.MasterConfiguration
SET DefaultCaseNo = @DefaultCaseNo
WHERE INT_ID = 1

RETURN @DefaultCaseNo
RETURN @DefaultCaseType

Nakul Vachhrajani

In order to use the values returned by the OUTPUT parameters, you need to store them in the variables in your triggers. I do not have access to SSMS right now, but it would be something like this:

ALTER TRIGGER Citations.InsertCaseNo_LogNo
    ON  Citations.LogHeader  
    AFTER INSERT    
AS      
       DECLARE
         @trgLogNo AS NVARCHAR(10),
         @trgLogType AS NVARCHAR(10),
         @trgLogTypeLogNo AS NVARCHAR(10),
         @retval int
         ;
    BEGIN        
         SET NOCOUNT ON;
         EXEC Citations.usp_RegNextCaseNo @trgLogNo   = @trgLogNo OUTPUT, 
                                          @trgLogType = @trgLogType OUTPUT; --Execute Stored Procedure to retreive Defaluts
         SELECT @trgLogTypeLogNo = i.LogNo From Inserted I
         SET @trgLogTypeLogNo = @trgLogType + @trgLogNo  
     END;

Open in new window

However, I do have a couple of concerns with this implementation:
#1. The RETURN clause is not required in the stored procedure definition
#2. I belive your design is based on the assumption that you will always be updating one record. This may or may not be the case. The implementation will fail when you update multiple records at the same time
#3. Please note that you cannot "return" data from a trigger to a calling code. Hence, I assume that @trgLogTypeLogNo and @retval are being used in the trigger down the line

MICHAEL REESE

ASKER

Hi Nakul

This trigger will be used to update a single record. I was using the @trgLogNo and @trgLogType as the variables to store the returned data from the stored procedure. Not sure what you are saying regarding #3 but I would like to have the trigger populate required columns only.

MICHAEL REESE

ASKER

Ok the trigger retrieving the data from the stored procedure, However, an additional row is generated when I perform an insert and the trigger fires.

Trigger
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO
ALTER TRIGGER Citations.InsertCaseNo_LogNo
ON Citations.LogHeader
AFTER INSERT
AS
DECLARE
@trgLogNo AS NVARCHAR(10),
@trgLogType AS NVARCHAR(10),
             @trgLogTypeLogNo AS NVARCHAR(10),
@INT_ID INT
;
BEGIN
SET NOCOUNT ON;
       SELECT @INT_ID = i.INT_ID, @trgLogNo = i.LogNo From Inserted I
       EXEC Citations.usp_RegNextCaseNo @trgLogNo OUTPUT,@trgLogType OUTPUT; --Execute Stored Procedure and Return Values
       SET @trgLogTypeLogNo = @trgLogNo + @trgLogType; --Concatenate VALUES returned FROM stored procedures

             INSERT INTO Citations.LogHeader (LogNo) VALUES (@trgLogTypeLogNo); --Insert Concatenated value in Table

             END;

ASKER CERTIFIED SOLUTION

MICHAEL REESE

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MICHAEL REESE

ASKER

I was able to resolve issue