About this question
I have a trigger (AFTER INSERT) that has been working in single inserts, and I believed it would work for multiples but its not. I researched it and am currently stuck as what I'm reading implies it should work. Any advice on the below would be appreciated: -
ALTER TRIGGER [dbo].[Trig_Tb_Users_Created] ON [dbo].[Tb_Users] AFTER INSERT AS BEGIN SET NOCOUNT ON; DECLARE @CreatedBy INT IF (SELECT COUNT([ID]) FROM [Tb_Users] WHERE [ADUsername] = SUSER_NAME()) = 0 INSERT INTO [Tb_Users] ([ADUsername]) VALUES (SUSER_NAME()); SELECT @CreatedBy = (SELECT TOP 1 [ID] FROM [Tb_Users] WHERE [ADUsername] = SUSER_NAME()); UPDATE [Tb_Users] SET [CreatedBy] = @CreatedBy WHERE [ID] in (SELECT [ID] FROM inserted); INSERT INTO [Tb_Permissions] ([LnkUserID]) VALUES ((SELECT [ID] FROM inserted)); END;I referred to this article (Create DML Triggers) and to my understanding I am following the syntax correctly. Bu I get the below error message when trying to insert multiple rows in to Tb_Users that refers to this trigger saying: - Msg 512, Level 16, State 1, Procedure Trig_Tb_Users_Created, Line 28 [Batch Start Line 0] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. This is in MS SQL Server 13.0.4522.0
What is sql server trigger after insert?
