Raiserror problem inside trigger

  • Hi

    I heve created trigger, and want display message for user in some cases. I did try use Raiserror et the end of this trigges and for some reason its not working. I know for sure that trigger is working and @err has something, because print @err working fine. And I know that RAISERROR(@err,10,1) WITH LOG working, becuse Its creating record in windows Application log. My problem - NO Message displayed for USER.

    Thank you for help

    Alex

     

    CREATE TRIGGER PhaseIIPhone2Upd

    ON dbo.RefPhysician

    AFTER INSERT, UPDATE AS

    DECLARE @Ph2 nvarchar (20)

    DECLARE @Res nvarchar (20)

    DECLARE @LastNm nvarchar (200)

    DECLARE @Pos smallint

    Declare @SixDigitID varchar(10), @DocName varchar(200), @err varchar(250)

    -- error message for user, by default no errors

    SET @err = ''

    -- getting current value of RefPhone2 and RefLastName

    set @Ph2 = (select i.RefPhone2 from ccdb.dbo.RefPhysician r, inserted i where i.RefID = r.RefId)

    set @LastNm = (select i.RefLastName from ccdb.dbo.RefPhysician r, inserted i where i.RefID = r.RefId)

    -- checking if 6 digit Phys number is in Last name field

    SET @SixDigitID = ''

    EXEC ccwtestlog.dbo.SortRefDocs @LastNm, @docID = @SixDigitID OUTPUT, @doclastname= @DocName OUTPUT

    -- result value for RefPhone2(PhaseII) field

    set @Res = ''

    IF @ph2 is Null SET @Res = 'print,read' -- set print,read as default value if nothing entered

     ELSE SET @Res = @Ph2

    SET @res = REPLACE(@res,' ','') -- deleting all spaces

    SET @res = LOWER(@res) -- put in lower case

    -- checking if Reflastname has 6 digit physician ID

     IF @SixDigitID <> '' -- if getting 6 digit ID from whole string

      BEGIN

       update ccdb.dbo.RefPhysician

        SET RefPhone2 = @res, RefLastName = @SixDigitID + ' ' + @docname

        FROM ccdb.dbo.RefPhysician r, inserted i

        where i.RefID = r.RefId

      END

     ELSE BEGIN SET @err = @err + 'Put 6 digit PhysID space Phys Last name.' END

    -- checking proper values for distribution methods

    if @res NOT IN ('print,read','print,noread','noprint,read','noprint,noread','fax,read','fax,noread','mail,read','mail,noread')

      SET @err = @err + 'Type distrib method in Phone2 field.(Ex. print,read)'

    --print @err

    IF @err <> '' RAISERROR(@err,10,1) WITH LOG

    GO

  • RAISERROR inside trigger will rollback whole trigger transaction.

    That's why you cannot see any result. It works as prescribed.

    _____________
    Code for TallyGenerator

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply