Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a stored proc that creates an entery in a table.

INSERT INTO tbl_CaseDetails
        (
            Case_Id
            , Client_Id
            , Client_HO_Id
            , Client_Case_Ref
            , Client_Cust_Ref
            , Access_Id
            , Product_Id
            , Status
        )
        Values(

            @CaseId
            , @Client_Id
            , isnull(@Head_Office_Id,@Client_Id)
            , @Client_Case_Reference
            , @Client_Customer_Reference
            , @Access_Id
            , @Product_Id 
            , 'pre-instr')

But in the trigger the status is appearing as null and the insert fails as i have a check for a null status.

  If Update(Status)
        Begin
              If @Status Is Null
        Exec [usp_CaseWithNullStatus] @Case_Id
                 Begin
                       RaisError ( 'Cannot insert null status'
              ,16
              ,16 )
                 End
        End

Any ideas why this is happening? Sp

share|improve this question

1 Answer 1

Shouldn't your inner begin / end block be set this way:

  If Update(Status)
  Begin
      If @Status Is Null
      Begin   -- <---------- block starts here
           Exec [usp_CaseWithNullStatus] @Case_Id
           RaisError ( 'Cannot insert null status'
              ,16
              ,16 )
      End
  End

In your code the raiserror will be always called, because the line following your if statement is considered as execution block and the next line will be called even if the if-statement isn't true.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.