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