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
(SELECT [ID] FROM inserted)
returns more than one IDCreatedBy
column is updated with the ID of the user doing the insert. It collects this ID from the sameTb_Users
table. I.e. John (ID=6) inserts a row for user Bill (New ID=12), theCreatedBy
column is updated with Johns ID of 6.