March 4, 2006 at 8:24 pm
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
March 5, 2006 at 4:01 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy