How Can We Help?
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