StoreProc OUTPUT parameter is DBNull when a trigger is defined.

I have a Stored Procedure (SQL code below) that inserts a record and returns the id (int) of the inserted record through an OUTPUT parameter.

It worked fine untill we defined a trigger on the same table. Now the result of the output parameter is always dbNull. Looks like he inserts the record correctly, runs the trigger correctly and somehow manages to delete or forget the contents of the OUTPUT parameter.

So the insert works fine, the trigger updates all the values in the correct tables . No exception or errors reported but the output param gets overwritten somewhere;

Any help, hint or tips on this? Thanks!

--N


Simplified SQL code...

<code>
CREATE PROCEDURE sp_SaveCallData4
(
@Key nvarChar(20),
@UserName nvarChar(20),
@Result int OUTPUT
)
AS

/* SOME INSERT STATEMENT GOES HERE*/


/* Filling the @result variable */
select @Result = callId from calldata
      where userName = @username
      AND Avayakey = @Key
GO



CREATE TRIGGER trigINS_o757_calldata ON [dbo].[CallData]
FOR INSERT
AS

select * into #inserted from inserted

COMMIT TRAN

  declare
      @inserted_Key nvarchar(50),
      @inserted_status nvarchar(50),
      @inserted_callId int

  select @inserted_Key = key from #inserted
  select @inserted_status = CompletionCode from #inserted
  select @inserted_callId = CallID from #inserted

            
              begin tran triple_update


                  /* 3  update statements go here...      */
            


              commit tran triple_update
</code>
nekrecartAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

imran_fastCommented:
when are you running the store procedure before the insert on call data or after that?
imran_fastCommented:
and please post the complete code so that i can help you out.
imran_fastCommented:
and change your trigger to be
===================
CREATE TRIGGER trigINS_o757_calldata ON [dbo].[CallData]
FOR INSERT
AS

  declare
     @inserted_Key nvarchar(50),
     @inserted_status nvarchar(50),
     @inserted_callId int

declare c cursor for select [key],CompletionCode,CallID from inserted
open c
fetch next from c into @inserted_Key,@inserted_status,@inserted_callId
while @@fetch_status = 0 begin
 /* 3  update statements go here...     */
fetch next from c into @inserted_Key,@inserted_status,@inserted_callId
end

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
well please do as imran asks ,
but immediately your apparant is the fact that your trigger is written incorrectly...

these lines
 select @inserted_Key = key from #inserted
  select @inserted_status = CompletionCode from #inserted
  select @inserted_callId = CallID from #inserted


assume that only 1 row will ever be on the inserted table...

in many standard scenarios that may not be the case ...
triggers always must be written to deal with mulitple row
conditions...

...

in the @result cancluation SQL are some of the
conditions identity columns retrieved from a previous update
in which case was the scope_identity() function used instead of @@Identity
to capture the value?


hth


nekrecartAuthor Commented:
The @result simply returns the key column (identity column, auto-increment) of the newly inserted record.

I guess we can also use @result = select scope_indentity() to retreive it. In fact it's a better than the solution posted above. The one above is the result of an afternoon going beserk because of one unexpected behaviour. ;-)

I found a post that describes excatly the same situation as we have :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp

After reading the article, my conclusion is, it is possible but we are doing something wrong in the trigger itself.

Is it possible that the COMMIT TRAN statement 'empties' the scope and @@identity parameters?

Anyway I will try the posted solutions and give some feedback.

thx.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.