Replace cursor with join in SQL Server insert trigger

Question

How do I improve performance by replacing the cursor in the insert trigger with join in SQL Server 2012?

create trigger TR-test
on [dbo].[temp]
instead of insert as
begin
   declare @id int

   declare cur cursor fast_forward for 
      select id 
      from inserted 

   open cur
   fetch next from cur into @id

   while @@fetch_status = 0   
   begin
       Print cast(@id as varchar(10))+ ':id number'
       fetch next from cur into @id
   end

   close cur
   deallocate cur
end

Answer 1

Below is one method to identify missing tables as a set-based operation, and display the list in the error message. Note that the table list in the error message will be truncated to 2044 characters followed by "..." if the list size exceeds 2047 characters. You may be able to leverage set-based processing for the other operations in the trigger but we will need to know the specifics to help.

--get list of missing tables
DECLARE @tablelist nvarchar(MAX) =
    (
    SELECT 
        CURRENT_TABLE + ' '
        FROM inserted
        WHERE OBJECT_ID(CURRENT_TABLE, 'U') IS NULL
        FOR XML PATH('')
    );

IF @tablelist IS NOT NULL
BEGIN
    RAISERROR('All tables do not exist. missing tables: %s', 16, 1, @tablelist);
END;

The example below shows how you might validate multiple rows and columns, logging errors to a table and raising an error if errors are found.

CREATE TABLE dbo.ErrorLog(
    ErrorTimestamp datetime2(7) NOT NULL
    ,ErrorMessage nvarchar(200)
    );
CREATE CLUSTERED INDEX cdx_ErrorLog ON dbo.ErrorLog(ErrorTimestamp);

DECLARE @ErrorCount int = 0;
DECLARE @ErrorLog TABLE(
    ErrorTimestamp datetime2(7) NOT NULL DEFAULT SYSDATETIME()
    ,ErrorMessage nvarchar(200)
    );

--get list of missing tables
INSERT INTO @ErrorLog(ErrorMessage)
    SELECT 
        'Missing table: ' + CURRENT_TABLE
        FROM inserted
        WHERE OBJECT_ID(CURRENT_TABLE, 'U') IS NULL;
SET @ErrorCount = @ErrorCount + @@ROWCOUNT;

INSERT INTO @ErrorLog(ErrorMessage)
    SELECT 
        'Missing Column1: ' + QUOTENAME(CURRENT_TABLE) + '.' + QUOTENAME(Column1)
        FROM inserted
        WHERE COLUMNPROPERTY(OBJECT_ID(CURRENT_TABLE, 'U'), Column1, 'ColumnId') IS NULL
    UNION ALL SELECT 
        'Missing Column2: ' + QUOTENAME(CURRENT_TABLE) + '.' + QUOTENAME(Column2)
        FROM inserted
        WHERE COLUMNPROPERTY(OBJECT_ID(CURRENT_TABLE, 'U'), Column1, 'ColumnId') IS NULL;
SET @ErrorCount = @ErrorCount + @@ROWCOUNT;

INSERT INTO @ErrorLog(ErrorMessage)
    SELECT
        'Invalid Column1Value: ' + CAST(Column1Value AS varchar(11))
        FROM inserted
        WHERE Column1Value < 0
    UNION ALL SELECT
        'Invalid Column2Value: ' + CAST(Column2Value AS varchar(11))
        FROM inserted
        WHERE Column2Value < 0;
SET @ErrorCount = @ErrorCount + @@ROWCOUNT;

IF @ErrorCount > 0
BEGIN
    INSERT INTO dbo.ErrorLog(ErrorMessage, ErrorTimestamp)
        SELECT ErrorMessage, ErrorTimestamp
        FROM @ErrorLog;
    RAISERROR('%d errors occured', 16, 1, @ErrorCount);
END;