SQL Server : function not working in trigger

Question

I have trigger on insert like that :

ALTER TRIGGER [dbo].[trTblNameSetRefNo]    
ON [dbo].[TblName]   
AFTER INSERT 
AS BEGIN
    UPDATE TblName
    SET RefNumber = dbo.GetNextRefNo(i.SomeField)
    FROM TblName
    INNER JOIN inserted i on i.ID = TblName.ID
END

But it doesn't work. When I 'hardcode' field RefNumber , for example : SET RefNumber = 'test', it works correctly.

Also when I call function outside of the trigger, it return proper result.

And this is my function that must return value in trigger :

ALTER FUNCTION [dbo].[GetNextRefNo] 
    (@ValueField INT) 
RETURNS NVARCHAR(250) 
AS 
  BEGIN 
      DECLARE @lastId INT; 
      DECLARE @result NVARCHAR(25); 
      DECLARE @CurrentIdentifier NVARCHAR(25); 

      SELECT TOP 1 
          @lastId = CAST(Substring(RefNumber, Charindex('-', RefNumber) + 4, Len(RefNumber )) AS INT) + 1 
      FROM   
          TblName 
      ORDER BY 
          ID DESC

      IF @@ROWCOUNT < 1 --if empty table , set start number
      BEGIN 
          SET @lastId = 1000
      END

      SELECT @CurrentIdentifier = 'SIT'

      SET @result = @CurrentIdentifier + '-' + Substring ( Cast(Year(Getdate()) AS NVARCHAR), 3, 2) + '-' + Cast(@lastId AS NVARCHAR)

      RETURN @result 
  END

Any ideas what I'm doing wrong?

Yes , function works from outside.. I think i mantion it in my question : 'Also when i call function outside of the trigger , it return proper result.' .. I dont receive any error , i dont know how to debug trigger or something ? — Harry Birimirski
– Harry Birimirski, Commented May 16, 2016 at 6:33

Alex · Accepted Answer · 2016-05-16 06:57:23Z

1

Your function returns what is probably an incorrect result when RefNumber = '' and it returns NULL when RefNumber = NULL, as would happen for a newly inserted record.
The design of the above code is beyond bad.
Suggested approach:
Use ID field as the counter and generate RefNumber based on that, while concatating Year as needed. Example function:

ALTER FUNCTION [dbo].[GetNextRefNo] (@ID INT) 
returns NVARCHAR(250) 
AS 
BEGIN 
    DECLARE @lastId INT; 
    DECLARE @result NVARCHAR(25); 
    DECLARE @CurrentIdentifier NVARCHAR(25); 

    SELECT @CurrentIdentifier = 'SIT'
    SET @ID = @ID + 1000

    SET @result = @CurrentIdentifier + '-' + Substring ( Cast(Year(Getdate()) AS NVARCHAR), 3, 2) + '-' + Cast(@ID AS NVARCHAR)
    RETURN @result 
END

Example Trigger:

ALTER TRIGGER [dbo].[trTblNameSetRefNo]    ON  [dbo].[TblName]   
AFTER INSERT AS  BEGIN

    UPDATE TblName
    SET RefNumber = dbo.GetNextRefNo(i.ID)
    FROM TblName
    INNER JOIN inserted i on i.ID = TblName.ID

END

Alternatively create another table to hold the current RefNumber seed.

edited May 16, 2016 at 6:57

answered May 16, 2016 at 6:39

Alex

5,2123 gold badges24 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Harry Birimirski Over a year ago

Thanks for explanation .. You are probably right .. and RefNumber is null.. so in my function i just added this : WHERE RefNumber is not null .. and this solve my problem ..

Alex Over a year ago

@Harry Birimirski, I am glad I solved your immediate problem with the function. I do advise you to rewrite it though. There are several solutions to your requirement you can choose from see: stackoverflow.com/questions/282943/…

Collectives™ on Stack Overflow

SQL Server : function not working in trigger

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related