Data integrity

Hi,
Does any one know how to apply/enhance the database's integrity.
During study, i learn the theory of how to make sure the data retrieve and
update is correct. But i dont know how to apply in database development.
For example: I have a table. 2 persons are accessing the table in the
following sequence:
Person A, access the table.
Person B, access the table.
Person A update new value (such as rental_fee)
Person B also update the rental_fee value.
Person A exit
Person B also exit
At this moment, record updated by person A has been overwrited by person B.
How to prevent it?
Thanks in advance.
Best regards,
GLOne method is to use optimistic concurrency. This technique checks to see
if the original value was modified by another user:

> Person A, access the table.
SELECT @.OldRentalFee = RentalFee
FROM RentalProperties
WHERE RentalPropertID = 1

> Person B, access the table.
SELECT @.OldRentalFee = RentalFee
FROM RentalProperties
WHERE RentalPropertID = 1

> Person A update new value (such as rental_fee)
UPDATE RentalProperties
SET RentalFee = @.NewRentalFee
WHERE RentalPropertID = 1 AND
RentalFee = @.OldRentalFee
IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Data was updated by another user', 16, 1)
END
--the above succeedes

> Person B also update the rental_fee value.
UPDATE RentalProperties
SET RentalFee = @.NewRentalFee
WHERE RentalPropertID = 1 AND
RentalFee = @.OldRentalFee
IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Data was updated by another user', 16, 1)
END
--the above raises an error
A common practice to use a rowversion data type (formally timestamp) for the
concurrency check. This simplifies concurrency checking because the value
is automatically changed by SQL Server whenever any row value changes and
you don't need special handling of NULLs.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:E6002470-2152-41AC-AE2F-D33A1676CCFA@.microsoft.com...
> Hi,
> Does any one know how to apply/enhance the database's integrity.
> During study, i learn the theory of how to make sure the data retrieve and
> update is correct. But i dont know how to apply in database development.
> For example: I have a table. 2 persons are accessing the table in the
> following sequence:
> Person A, access the table.
> Person B, access the table.
> Person A update new value (such as rental_fee)
> Person B also update the rental_fee value.
> Person A exit
> Person B also exit
> At this moment, record updated by person A has been overwrited by person
> B.
> How to prevent it?
> Thanks in advance.
> Best regards,
> GL|||Thanks for ur help.
I have another question, do i need to configure the sqlserver 2000 in order
to do that?
In addition, creating a column (auto generate number) act as one of the
primary key is a good practice?
"Dan Guzman" wrote:

> One method is to use optimistic concurrency. This technique checks to see
> if the original value was modified by another user:
>
> SELECT @.OldRentalFee = RentalFee
> FROM RentalProperties
> WHERE RentalPropertID = 1
>
> SELECT @.OldRentalFee = RentalFee
> FROM RentalProperties
> WHERE RentalPropertID = 1
>
> UPDATE RentalProperties
> SET RentalFee = @.NewRentalFee
> WHERE RentalPropertID = 1 AND
> RentalFee = @.OldRentalFee
> IF @.@.ROWCOUNT = 0
> BEGIN
> RAISERROR('Data was updated by another user', 16, 1)
> END
> --the above succeedes
>
> UPDATE RentalProperties
> SET RentalFee = @.NewRentalFee
> WHERE RentalPropertID = 1 AND
> RentalFee = @.OldRentalFee
> IF @.@.ROWCOUNT = 0
> BEGIN
> RAISERROR('Data was updated by another user', 16, 1)
> END
> --the above raises an error
> A common practice to use a rowversion data type (formally timestamp) for t
he
> concurrency check. This simplifies concurrency checking because the value
> is automatically changed by SQL Server whenever any row value changes and
> you don't need special handling of NULLs.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Daniel" <Daniel@.discussions.microsoft.com> wrote in message
> news:E6002470-2152-41AC-AE2F-D33A1676CCFA@.microsoft.com...
>
>|||Dan's code will work straight out of the box. There is no extra SQL
Server config needed. Timestamp is a native SQL datatype, so you don't
need to reconfigure anything to use the timestamp for the row versioning
either.
With regards to using an identity column (auto generated sequential
integer) as a primary key for a table, there are various debates about
that. Many people would say that's often the best way to define primary
keys in real life, while others (the most vocal being one Joe Celko, who
has spent some time on the ANSI committee and published some material on
the subject) would disagree and advise you to use a natural key as your
primary key for the table (like a SSN or VIN or ActionType/ActionDate
combination for example).
I won't get into the whole debate (it's a rather raging topic) except to
say both sides of the argument can be justified. If you do create a
clustered index on an identity column, however, you can substantially
reduce pages splits (and therefore I/O) on inserts because all new data
will be located at the end of the index and no reordering within the
index will be necessary. This will create a "hotspot" in effect at the
end of the index. But there are many other factors to consider, not the
least of which is personal preference, when declaring one or more
columns to be the primary key of a table.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Daniel wrote:
>Thanks for ur help.
>I have another question, do i need to configure the sqlserver 2000 in order
>to do that?
>In addition, creating a column (auto generate number) act as one of the
>primary key is a good practice?
>
>"Dan Guzman" wrote:
>
>|||> I have another question, do i need to configure the sqlserver 2000 in
> order
> to do that?
There's no special SQL Server configuration needed since optimistic
concurrency is handled by the application.

> In addition, creating a column (auto generate number) act as one of the
> primary key is a good practice?
This subject is often discussed in this forum so you can fund many pros and
cons with a google search. It's a common practice to use a surrogate value,
such as an IDENTITY column, as the primary key. Whether or not this
practice is good depends on the specific situation. There are those who
believe religiously that one should always use natural keys and others who
always employ surrogate keys as a knee-jerk reaction. Personally, I
evaluate each situation individually.
Hope this helps.
Dan Guzman
SQL Server MVP|||ok. i got it..thanks for ur respond.
"Dan Guzman" wrote:

> There's no special SQL Server configuration needed since optimistic
> concurrency is handled by the application.
>
> This subject is often discussed in this forum so you can fund many pros an
d
> cons with a google search. It's a common practice to use a surrogate valu
e,
> such as an IDENTITY column, as the primary key. Whether or not this
> practice is good depends on the specific situation. There are those who
> believe religiously that one should always use natural keys and others who
> always employ surrogate keys as a knee-jerk reaction. Personally, I
> evaluate each situation individually.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
>