September 29, 2003 at 7:10 am
Hi all, have been reading the forum for a while but Im a bit of a lurker normally..
Got an interest situation that I'd like some viewpoints on..
All of our tables have a uniqueidentifier coulmn, named ROWID. This has a default of newid() placed on it. Every table has a clustered primary key.
On an update trigger we have a statement that says
if not update(ROWID)
update <Table> set ROWID = newid()
where ROWID = inserted.ROWID
To facilitate this we have a unique index on the ROWID column.
Someone has suggested that the trigger should do:
if not update(ROWID)
update <Table> set ROWID = newid()
where <Primary Key Cols> = inserted.<Primary Key Cols>
so if we had CODE and NAME in the primary key it'd read:
if not update(ROWID)
update <Table> set ROWID = newid()
where CODE = inserted.CODE
and NAME = inserted.NAME
The application will do this update:
update <Table> set COL1 = 'xxx'
where CODE = 'ABC'
and NAME = 'XYZ'
and ROWID = :RowidVar
RowidVar will have the rowid that was selected when the user entered the data.
I think our strategy here is wrong, firstly the update fired from the application only needs to do:
update <Table> set COL1 = 'xxx'
where ROWID = :RowidVar
therefore the trigger should do what it does now ( where ROWID = inserted.ROWID ).
Comments/Suggestions please..
And before you say it yes its a bit over complicated.. but such is life..
September 29, 2003 at 7:24 am
My suggestion is to use the timestamp (AKA rowversion in SQL Server 2000) instead of the uniqueidentifier data type for this column.
--Jonathan
--Jonathan
September 29, 2003 at 8:36 am
Hi Jonathan,
We originally did use timestamp, but were led to believe that the data type was to be dropped from Yukon ?
September 29, 2003 at 9:01 am
quote:
Hi Jonathan,We originally did use timestamp, but were led to believe that the data type was to be dropped from Yukon ?
No, it's being renamed rowversion.
--Jonathan
--Jonathan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply