Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: Calling stored procedure in trigger

    Hi

    I have a problem calling stored procedure in trigger..

    When no exception occures stored procedure returns the value but if any exception occures executing that stored procedure then stored procedure will not return any value..

    I have handled exception by returning values in case if any..

    Here is the stored procedure

    CREATE PROCEDURE BidAllDestinations
    (
    @ITSPID int,
    @DestinationID int,
    @BidAmount decimal (18,4),
    @BidTime datetime,
    @intErrorCode int out
    )

    AS
    DECLARE @GatewayID int
    DECLARE @GatewayExist int
    SET @GatewayID = 0
    SET @GatewayExist = 0
    SET @intErrorCode = 0

    UPDATE BID FOR CORRESPONDING GATEWAY
    DECLARE GatewayList CURSOR FOR

    SELECT Gateways.GatewayID
    FROM Gateways INNER JOIN
    GatewayDestinations ON Gateways.GatewayID = GatewayDestinations.GatewayID INNER JOIN
    ITSPs ON Gateways.ITSPID = ITSPs.ITSPID
    Where Gateways.ITSPID = @ITSPID AND DestinationID = @DestinationID

    OPEN GatewayList

    FETCH NEXT FROM GatewayList INTO @GatewayID

    IF (@GatewayID = 0)

    SET @intErrorCode = 1
    ELSE
    BEGIN
    -- CHECK @@FETCH_STATUS TO SEE IF THERE ARE ANY MORE ROWS TO FETCH
    WHILE @@FETCH_STATUS = 0

    BEGIN


    SELECT @GatewayExist = Gatewayid
    FROM TerminationBids
    WHERE Gatewayid = @Gatewayid AND DestinationID = @DestinationID

    IF @GatewayExist > 0

    UPDATE TerminationBids
    SET BidAmount = @BidAmount,
    BidTime = getdate()

    WHERE GatewayID = @Gatewayid AND DestinationID = @DestinationID

    ELSE

    INSERT INTO TerminationBids (GatewayID, DestinationID, BidAmount)
    VALUES (@GatewayID,@DestinationID,@BidAmount)

    IF @@ERROR <> 0
    BEGIN
    GOTO PROBLEM
    CLOSE GatewayList
    DEALLOCATE GatewayList
    END

    FETCH NEXT FROM GatewayList INTO @GatewayID

    END
    CLOSE GatewayList
    DEALLOCATE GatewayList

    END
    PROBLEM:
    BEGIN

    SET @intErrorCode = 100


    END
    RETURN @intErrorCode
    GO


    TRIGGER CODE:::

    CREATE TRIGGER TR_TerminationBid
    ON dbo.TerminatorBidHistory FOR INSERT

    AS

    DECLARE @ITSPID int
    DECLARE @DestinationID int
    DECLARE @BidAmount decimal (18,4)
    DECLARE @BidTime datetime
    DECLARE @intErrorCode INT
    DECLARE @DistinationList varchar (8000)
    DECLARE @DestinationLevel varchar (100)
    SET @intErrorCode = 0
    SET @ITSPID = 0
    SET @DistinationList = ''
    -- CHECK ITPSID' S VALIDITY

    SELECT @ITSPID = i.ITSPID, @DestinationID= i.DestinationID,
    @BidAmount = i.BidAmount, @BidTime = i.BidTime
    FROM Inserted i
    INNER JOIN ITSPS ON ITSPS.ITSPID = i.ITSPID
    INNER JOIN Destinations ON Destinations.DestinationID = i.DestinationID

    EXEC BidAllDestinations @ITSPID,@DestinationID,@BidAmount,@BidTime, @intErrorCode = @intErrorCode output
    SELECT @intErrorCode
    Following should return value for @intErrorCode if any exception occures

    Any one can help what is wrong with it?

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    1
    Hi shahidmhd,

    I also have a problem like this one and been trying to solve since yesterday Should you already found the solution, can you be so kind to share it to me? I would really appreciate your help if ever.

    Thanks in advance.

    By the way here's a little background of my problem for everyone's info:
    The handling of error message on SP level is fine meaning i can get the returned error if there's exceptions but when I use trigger to invoke the SP the statement is being halted on child SP's level making the transaction incomplete.

    I tried to add SET XACT_ABORT OFF inside the trigger but that doesn't solve my issue. I thought also that it's a permission related problem but the Profiler shows that I'm using "sa" account to call the trigger. and Distributed Trans is not applicable also because it's sunning on single instance of DB only.

    Guys, really need your help on this.

    =)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •