SQL Server: функция не работает в триггере

У меня есть триггер на вставке:

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

Но это не работает. Когда я жестко запрограммировал поле RefNumber, например: SET RefNumber = 'test', оно работает правильно.

Также, когда я вызываю функцию вне триггера, она возвращает правильный результат.

И это моя функция, которая должна возвращать значение в триггере:

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

Есть идеи, что я делаю не так?


sql-server triggers sql-function
person Harry Birimirski    schedule 16.05.2016    source источник
comment
работает ли функция вне триггера?   -  person Squirrel    schedule 16.05.2016
comment
Что за сообщение об ошибке?   -  person ydoow    schedule 16.05.2016
comment
Да, функция работает извне ... Думаю, я упомянул об этом в своем вопросе: «Также, когда я вызываю функцию вне триггера, она возвращает правильный результат». .. Я не знаю, как отлаживать триггер или что-то в этом роде?   -  person Harry Birimirski    schedule 16.05.2016

Ответы (1)


arrow_upward
1
arrow_downward

Ваша функция возвращает то, что, вероятно, является неправильным результатом, если RefNumber = '', и возвращает NULL, когда RefNumber = NULL, как это произошло бы для недавно вставленной записи.
Дизайн приведенного выше кода совершенно плох.
Предлагаемый подход:
Используйте поле ID в качестве счетчика и сгенерируйте RefNumber на его основе, при необходимости объединяя год. Пример функции:

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

Пример триггера:

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

В качестве альтернативы создайте другую таблицу для хранения текущего начального числа RefNumber.

person Alex    schedule 16.05.2016
comment
Спасибо за объяснение .. Вы, вероятно, правы .. и RefNumber имеет значение null .. поэтому в моей функции я просто добавил это: WHERE RefNumber не равно нулю .. и это решает мою проблему .. - person Harry Birimirski; 16.05.2016
comment
@Harry Birimirski, я рад, что решил вашу непосредственную проблему с функцией. Я все же советую вам его переписать. Есть несколько решений для ваших требований, из которых вы можете выбрать: stackoverflow.com/questions/282943/ - person Alex; 16.05.2016