Как обновить больше одной строки в триггере?

Question

[DRIKS]

Есть триггер:

ALTER TRIGGER [dbo].[WorkTimeUPD_Warning] ON [dbo].[Workers]
INSTEAD OF UPDATE
AS
IF(CURRENT_TIMESTAMP BETWEEN TRY_PARSE('09:00:00' AS DATETIME) AND TRY_PARSE('21:00:00' AS DATETIME))
	BEGIN
		UPDATE dbo.Workers SET  [WORKERS_ID] = (SELECT WORKERS_ID FROM inserted),
								[NAME] = (SELECT NAME FROM inserted),
								[ADDRESS] = (SELECT ADDRESS FROM inserted),
								[DEPT_ID] = (SELECT DEPT_ID FROM inserted),
								[INFORMATION] = (SELECT INFORMATION FROM inserted)
		WHERE [WORKERS_ID] = (SELECT WORKERS_ID FROM deleted);
		print('Данные обновлены!');
	END
ELSE
	BEGIN
		print('Обновление данных в не рабочее время - НЕ ДОСТУПНО!');
		INSERT INTO dbo.WorkersUPDLogs(WorkerId,ModifyDate)
		VALUES ((SELECT WORKERS_ID FROM inserted), GETDATE())
	END

При обновлении одной строки, триггер проходит. Пример запроса: UPDATE Workers SET [NAME] = 'lala' WHERE [WORKERS_ID] = 22 (работает)
А при обновлении нескольких строк вылетает ошибка. Пример запроса: UPDATE Workers SET [NAME] = 'lala' (не работает, выдаёт ошибку)

Как исправить, помогите пожалуйста! П.с. не судите строго, еще совсем "зелёный" в этом направлении, спасибо :)

Answer 1

[Константин Цветков]

Использовать связывание:

CREATE TRIGGER PersonFormatUpdate ON [PersonFormat] FOR UPDATE AS 
BEGIN
  SET NOCOUNT ON 
  UPDATE FilmAttributeValue
    SET [Value] = inserted.[Format]
        FROM FilmAttributeValue 
          INNER JOIN inserted ON FilmAttributeValue.[ValueFormat] = inserted.ID
END

Оператор IN

CREATE TRIGGER PersonDelete ON [Person] FOR DELETE AS 
BEGIN
  SET NOCOUNT ON 
  DELETE FROM Internet WHERE (Internet.Kind = 'P') 
     AND (Internet.[Object] IN ( SELECT ID FROM deleted))
END
GO

Курсор

CREATE TRIGGER AlbumRoleInsert ON AlbumRole INSTEAD OF INSERT AS 
BEGIN
  SET NOCOUNT ON
  DECLARE @ALB INT, @PRF INT, @ROL VARCHAR(500), @mas INT, @pla BIT, @mem BIT, @gue BIT
  DECLARE @ID INT, @RLR VARCHAR(500)
  DECLARE @Role TABLE(Role VARCHAR(100))
  
  DECLARE LISTROLE CURSOR LOCAL FAST_FORWARD FOR
    SELECT Album, Master, Performer, Play, Member, Role, SpecialGuest
      FROM Inserted
  OPEN LISTROLE
  FETCH LISTROLE INTO @ALB, @mas, @PRF, @pla, @mem, @ROL, @gue
  WHILE @@FETCH_STATUS = 0 BEGIN
    -- Есть запись?
    IF EXISTS( SELECT * FROM AlbumRole WHERE (Album = @ALB) AND (Performer = @PRF)) BEGIN
      SELECT @ID = ID, @RLR = [Role] FROM AlbumRole WHERE (Album = @ALB) AND (Performer = @PRF)
      INSERT INTO @Role SELECT TRIM( value ) FROM STRING_SPLIT( @RLR, ',' )
      INSERT INTO @Role SELECT TRIM( value ) FROM STRING_SPLIT( @ROL, ',' ) 
                                        WHERE TRIM( value ) NOT IN ( SELECT [Role] FROM @Role )
      DELETE @Role WHERE (TRIM( [Role] ) = '')
      SELECT @RLR = STRING_AGG ( [Role], ', ' ) FROM @Role
      -- Обновление списка.
      UPDATE AlbumRole SET Role = @RLR WHERE ID = @ID
    END ELSE BEGIN
      -- Добавление: 
      INSERT INTO AlbumRole ( Album, Master, Performer, Play, Member, Role, SpecialGuest ) 
             VALUES( @ALB, @mas, @PRF, @pla, @mem, @ROL, @gue )
    END
    -- Следующее поле
    FETCH LISTROLE INTO @ALB, @mas, @PRF, @pla, @mem, @ROL, @gue
  END
  CLOSE LISTROLE
  DEALLOCATE LISTROLE
END
GO