INSERT AFTER trigger failing with multiple rows

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

Answer 1

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;

Answer 2

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;