How to remove cursor from this trigger

Transact-SQL
1

Hi,

I've been given this trigger to see if the performance can be improved.
Immediately what jumps out at me is the use of a cursor, which i've always read is not a good idea.
I don't think creating a temp table to store a set of the updated records will work - though i'm happy to be shown otherwise.
If anyone has any pointers on how to eliminate this cursor, that would be great.

ALTER TRIGGER [dbo].[trg_TableA_rollbk_complt]
ON [dbo].[TableA]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

	declare @generated_invoice int,
			@message varchar(200),
			@del_status_id int,
			@ins_status_id int,
			@status_id_rollbk int,
			@status_id_complt int

	BEGIN
	
		select @status_id_complt = errm_id from error_mess
			where errtype = 'SS' and errcode = 'COMPLT'
		select @status_id_rollbk = errm_id from error_mess
			where errtype = 'SS' and errcode = 'ROLLBK'
		
		declare rollback_cursor cursor for
			select deleted.status_id, inserted.status_id, inserted.generated_invoice
				from inserted 
					left outer join deleted
						on deleted.generated_invoice = inserted.generated_invoice
		open rollback_cursor
		fetch rollback_cursor into @del_status_id, @ins_status_id, @generated_invoice
		
		while @@fetch_status = 0

		begin
			if @del_status_id <> @ins_status_id
			begin
				if @ins_status_id = @status_id_rollbk exec [dbo].[usp_suin_rollbk] @generated_invoice, @del_status_id
				if @ins_status_id = @status_id_complt exec [dbo].[usp_suin_complt] @generated_invoice
			end
			fetch rollback_cursor into @del_status_id, @ins_status_id, @generated_invoice
		end
		
		close rollback_cursor
		deallocate rollback_cursor

	END		

END

Many Thanks
Shane

2

Since you are running two procs for each row retrieved in the cursor, using a cursor is probably the best way to do it. In any case, there is no set-based alternative.

1 Like
3

Thanks for responding. I was beginning to get the same feeling about this cursor too.

4

It would be normal make the processing asynchronous by writing:

deleted.status_id, inserted.status_id, inserted.generated_invoice

into some sort of queue and let another process call the SPs. You could look at Service Broker or just write to another table which a background process could poll.

You could also alter the SPs to accept table valued parameters but again I would be inclined to call them asynchronously.

The trick with triggers is to keep the amount of work they do to a minimum.