Trigger on multiple insert

How Can We Help?

You are here:
< Back

I had a case where I needed to insert multiple rows in a single query. A trigger is only executed once per insert, hence I only got it to trigger on a single record.

The solution i found was to use a Cursor even though it isn’t recommended: https://stackoverflow.com/questions/2178889/sql-server-a-trigger-to-work-on-multiple-row-inserts

ALTER TRIGGER [license].[WatchInsert]
   ON  [license].[Watch]
   AFTER Insert
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @bu nvarchar(25) = ''
	DECLARE @dep nvarchar(25) = ''
	DECLARE @id int
	DECLARE my_Cursor CURSOR FOR SELECT ID FROM INSERTED;

	OPEN my_Cursor
	FETCH NEXT FROM my_Cursor INTO @id
	WHILE @@FETCH_STATUS = 0
	BEGIN
	
		SELECT @bu = en.[bu], @dep = en.[team] FROM [common].sc.EmployeesNew en, license.Watch w
		WHERE w.ID = @id AND en.initials = w.Initials

		IF @bu != '' 
			UPDATE license.Watch
			SET
				license.Watch.Bu = @bu, -- nvarchar
				license.Watch.Department = @dep -- nvarchar
			WHERE license.Watch.ID = @id
		
		FETCH NEXT FROM my_Cursor into @id
	END
	CLOSE my_Cursor
	DEALLOCATE my_Cursor
END
GO