Blog ala Vidar

When doing QA on SQL-objects, I’ve come over some very common mistakes. Number one has to be people forgetting that the Inserted and Deleted views inside triggers may contain more than more record.
This is a very good (and simplyfied) example of code from an insert trigger:

DECLARE @Login AS NVARCHAR(128)

SELECT @Login = Login
	FROM Inserted

IF NOT EXISTS (SELECT * FROM stbl_System_Users WITH (NOLOCK) WHERE Login = @Login)
BEGIN
	INSERT INTO stbl_System_Users (Login) VALUES (@Login)
END

This will only work if you’re inserting one record, but what if you’re inserting several? It will only do the code you’ve added for ONE (random) record.
The solution for this is very simple:

INSERT INTO stbl_System_Users
  (Login)
SELECT
  I.Login
	FROM Inserted AS I
	WHERE NOT EXISTS (SELECT *
				FROM stbl_System_Users WITH (NOLOCK)
				WHERE Login = I.Login)

In some cases you might want to execute a SP. Instead of INSERT, then EXEC:

DECLARE @Login AS NVARCHAR(128)

SELECT @Login = Login
    FROM Inserted

IF NOT EXISTS (SELECT * FROM stbl_System_Users WITH (NOLOCK) WHERE Login = @Login)
BEGIN
    EXEC astp_Some_SP @Login = @Login
END

Solution to this:

DECLARE @Login AS NVARCHAR(128)

SELECT  I.Login
	INTO #LoopLogins
	FROM Inserted AS I
	WHERE NOT EXISTS (SELECT *
				FROM stbl_System_Users WITH (NOLOCK)
				WHERE Login = I.Login)

WHILE EXISTS (SELECT * FROM #LoopLogins)
BEGIN
	SELECT TOP 1
	  @Login = Login
		FROM #LoopLogins

	EXEC astp_Some_SP @Login = @Login

	DELETE
		FROM #LoopLogins
		WHERE Login = @Login
END