Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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>
0
nekrecart
Asked:
nekrecart
  • 3
2 Solutions
 
imran_fastCommented:
when are you running the store procedure before the insert on call data or after that?
0
 
imran_fastCommented:
and please post the complete code so that i can help you out.
0
 
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

0
 
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


0
 
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now