Sql-server – INSERT AFTER trigger failing with multiple rows

insertsql servertrigger

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

Best Answer

Try taking out the VALUES clause in your final insert (See the commented line below which give your error) and replace it with a simple insert (see the final insert below). 

Declare @Tb_Permissions table (LnkUserID int)
Declare @Inserted table (ID int)
insert into @Inserted(id) values(1),(2)
--INSERT INTO @Tb_Permissions ([LnkUserID]) VALUES ((SELECT [ID] FROM @Inserted));
INSERT INTO @Tb_Permissions ([LnkUserID]) SELECT [ID] FROM @Inserted;
Related Question