Renumbering Column Values Based on Values in Another Column

[larrydavid]

Hello, I have the trigger for renumbering values in a numeric field. It is a non-PK int column. Here is the trigger:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER dbo.trgUpdateNumField ON tblProjects FOR UPDATE
AS 
BEGIN
	SET NOCOUNT ON
	DECLARE @curValue as varchar
	DECLARE @preValue as varchar

    SET @curValue = (SELECT numField FROM INSERTED)
	SET @preValue = (SELECT numField FROM DELETED)

	IF (@curValue <> @preValue)
	
	BEGIN
		create table #temp1
		(
			[AutoID] [int] IDENTITY(1,1) NOT NULL,
			[numField] int
		)
		insert into #temp1
		(
			numField
		)

		select numField 
		from tblProjects 
		where numField <> 0 and numProjID <> 1 
		order by numField 

		select * from #temp1

		update tblProjects 
		set tblProjects.numField = #temp1.AutoID 
		from tblProjects inner join #temp1 on 
		tblProjects.numField = #temp1.numField 

		drop table #temp1
	END
END
GO

So now, there is one more criteria (field value) I need to base the renumbering on, a Rating field:

numField Rating
1 985
2 845
3 748
4 647
5 536

So, as you can see the list is sorted by numField ASC and Rating DESC. So, the needed behavior is, if the Rating value changes, the numField changes along with it, according to how the Rating is ordered. You can assume these are all sequential to begin with and never any duplicates. If say for instance, (numField) with value of 3 has a Rating that is updated to be > 2, then this record becomes 2 and 2 becomes 3. Likewise, if 3 has a Rating that is updated to be < 4 then this record becomes 4 and 4 becomes 3. If a new record is entered, then its Rating (and therefore its numField value, need to be ordered within the existing list accordingly, with the list sorted by numField ASC and Rating DESC (the higher the Rating the lower the numField number). What I'm having trouble getting my head around is that this applies to other records above and below - i.e. if 3 < 5 this value becomes 5 and 5 becomes 4.

Do you think it would be possible to add this to my trigger? If not, could someone suggest another way to handle renumbering the numField value according to the Rating? Hope this makes sense.

Thank you,
Lawrence

 

[bborissov]

NEVER! I SAID NEVER program a trigger with assumption that ONLY ONE records will be updated at a time.
That is just not true.
Remember triggers are fired after the whole job is done and both meta tables contain ALL affected records.

And it didn't matter that your software didn't allow to be updated more that one record at a time.

Now back to your question
Why not use calculated column or create that column during the SELECT using ROW_NUMBER() OVER .... (if you use SQL Server 2005 or above)?


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[larrydavid]

Hi Boris,

OK, thank you for the advice. Can you please show me an example of how I can implement your code suggestion? SELECT using ROW_NUMBER() OVER ....

Thank you,
Lawrence