Why INSERT AFTER trigger failing with multiple rows?
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?
An AFTER INSERT Trigger means that MySQL will fire this trigger after the INSERT operation is executed. You get the error due you're trying to insert more than one row, keep in mind that trigger can be called once for multiple inserts.
ALTER TRIGGER [dbo].[Trig_Tb_Users_Created] ON [dbo].[Tb_Users] AFTER INSERT AS BEGIN SET NOCOUNT ON; DECLARE @CreatedBy INT = 0; SELECT @CreatedBy = [ID] FROM [Tb_Users] WHERE [ADUsername] = SUSER_NAME(); IF COALESCE(@CreatedBy, 0) = 0 BEGIN INSERT INTO [Tb_Users] ([ADUsername]) VALUES (SUSER_NAME()); SET @CreatedBy = SCOPE_IDENTITY(); -- THIS SHOULD BE REPLACED BY AN OUTPUT SENTENCE END UPDATE usr SET [CreatedBy] = @CreatedBy FROM [Tb_Users] usr JOIN inserted ON usr.[ID] = inserted.[ID]; INSERT INTO [Tb_Permissions] ([LnkUserID]) SELECT [ID] FROM inserted; END; To insert multiple rows, select the same number of rows that you want to insert. To select multiple rows hold down the "shift" key on your keyboard on a Mac or PC. For example, if you want to insert six rows, select six rows while holding the "shift" key.