1

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

  • It seems that (SELECT [ID] FROM inserted) returns more than one ID – McNets Nov 28 '18 at 9:43
  • I should describe what it is doing better sorry, On an insert the CreatedBy column is updated with the ID of the user doing the insert. It collects this ID from the same Tb_Users table. I.e. John (ID=6) inserts a row for user Bill (New ID=12), the CreatedBy column is updated with Johns ID of 6. – Gary Evans Nov 28 '18 at 9:43
  • @McNets It does return more that 1 ID, this trigger should handle that but isn't. – Gary Evans Nov 28 '18 at 9:44
  • Is [Tb_Users].[ID] an IDENTITY column? – McNets Nov 28 '18 at 9:47
  • Yes with an increment of 1, has been working for single inserts. – Gary Evans Nov 28 '18 at 9:48
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;
| improve this answer | |
1

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.