Trigger & record number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger & record number

Author

Topic

mathmax
Yak Posting Veteran

95 Posts

Posted - 2007-10-16 : 21:22:53

Hello,

I have a table with a field that contains the record number. This field is an identity column that is needed to be compatible with old Clipper programs. I've to ensure continuity in the numbering. So I have to update those numbers each time one or several records are deleted. I was thinking of using a trigger but I'm quite novice in this domain and don't know how to do it. Does anyone have a solution for this problem ?

Thank you in advance.

Regards,

mathmax

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-16 : 22:09:46

Check out this article by Jeff:

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

Future guru in the making.

mathmax
Yak Posting Veteran

95 Posts

Posted - 2007-10-17 : 23:17:12

I don't understand how this article could solve my problem. Could you clarify ?

Thank you for your help.

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 00:32:47

You could have a delete trigger that renumbers the records.

If I have understood you correctly you need a "row number" column that is always contiguous, but it is not important that a given record retains the SAME number, over time.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 05:25:33

Why do you worry about gaps in numbers?
If you want serial number to display at front end, do numbering there
Though there are gaps, your order by number column will work perfectly

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 05:40:11

"Why do you worry about gaps in numbers?"

Op says "Clipper", so maybe that App. needs a row number to maintain some sort of State when displaying grids. Mind you, if the renumber kicks in whilst you are looking at some rows I'm not sure what Clipper would do next!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 06:25:39

Well. In that case OP needs Renumber procedure which can probably have

SET IDENTITY_INSERT table ON

Update table
set rownumber=rownumber-1
where rownumber>@number_which_has_been_deleted

SET IDENTITY_INSERT table OFF

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 06:33:36

or maybe, assuming multi-row deletes are possible:


SET IDENTITY_INSERT table ON

Update U
set rownumber = NewRowNumber
FROM MyTable AS U
    JOIN
    (
        SELECT rownumber AS [OldRowNumber],
               (
                   SELECT COALESCE(MAX(rownumber), 0) + 1 
                   FROM MyTable AS T2 
                   WHERE T2.rownumber < T1.rownumber
               ) AS [NewRowNumber]
        FROM MyTable AS T1
    ) AS X
        ON X.OldRowNumber = U.rownumber 
       AND X.OldRowNumber <> X.NewRowNumber -- Skip if unchanged

SET IDENTITY_INSERT table OFF

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 06:44:00

Very Clever approach Kristen

EDIT : I thought it would take long time to run for a bulky table until I noticed your second join condition

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 07:03:08

Yeah, better to delete one towards the higher numbers

mathmax
Yak Posting Veteran

95 Posts

Posted - 2007-10-18 : 10:08:19

Hello,

Yes that's Clipper that force me to use a such row.
The problem is that "SET IDENTITY_INSERT table ON" don't allow to to update identity column but only to insert new row with the value you want for the identity column...
I was thinking of creating a new identity column and remove the old one via a trigger, but it might utilize excessive server resource...
Or maybe do you have a solution to update an identity column ?

Thanks,

mathmax

mathmax
Yak Posting Veteran

95 Posts

Posted - 2007-10-18 : 10:08:21

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 10:36:57

No, you are right, you cannot update an identity column. You would have to have a trigger to allocate you "next available number" on new records, and then something to renumber when an item is deleted (delete trigger will do for that)

mathmax
Yak Posting Veteran

95 Posts

Posted - 2007-10-18 : 10:44:40

quote:
and then something to renumber when an item is deleted (delete trigger will do for that)

Yes but how to renumber if I cannot update this column?

I find this article http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=4...
Unfortunately I get the following error:
"Ad hoc updates to system catalogs are not allowed."
Although I wrote this:
EXEC sp_configure 'allow updates', 1
Do you have a solution to this problem or any other solutions ?

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 11:28:11

"Yes but how to renumber if I cannot update this column?"

I was meaning that you would NOT use an Identity column, and thus you would be able to update it

Kristen

mathmax
Yak Posting Veteran

95 Posts

Posted - 2007-10-18 : 11:40:49

I have no choice. I use xHarbour SQLRDD. It emits sql requests according Clipper instructions and needs that my column is identity...
In you mind, will it be slow to create a new identity column each time a record is deleted ? Will it be to much work for the server ?

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 01:19:11

Yes, to change a column to IDENTITY the server will have to:

Drop all foreign keys
Create new temporary table (with the IDENTITY attribute)
Create PK
Insert ALL rows into temporary table
Drop original table
Rename temporary table
Re-Create all constraints, indexes
Re-Create all foreign keys

Level of disruption to any other application, which tries to access this table whilst this is going on, will be total!

Why does the application need an Identity? Can't it cope with a guaranteed sequentially contiguously numbered column? You don't need Identity to achieve that (although it is usually the easiest way to get that result).

And how do all the users applications cope when all the rows in the table are renumbered? That's the bit that foxes me. Also why the application cannot cope with a gap in the numbers

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-19 : 01:41:48

If the numbering column also is PRIMARY KEY, what will happen to all foreign contraints when renumber is done?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 02:44:09

Indeed. It needs to be a "rownumber" column, and that's all

mathmax
Yak Posting Veteran

95 Posts

Posted - 2007-10-19 : 11:10:32

The numbering column is not a PRIMARY KEY.

Why creating a new table? Will it not be more efficient to remove the identity column and create a new one? Is it possible to do that? What does a such script look like?

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 11:32:58

"Is it possible to do that?"

Not that I know of.

"What does a such script look like?"

If you create a table, and then change its definition, you can then generate a script of the changes for you [rather than just saving and letting SQL Server make the change], and that script will show you what changes are necessary. If SQL Server can just change an attribute then that is all the script will show.

As I said above to do a "maintained contiguous row-number" column you need an INSERT trigger to put the next number in sequence into new records, and a delete trigger to sort out gaps caused by deletion. You don't use IDENTITY for this.

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources