|
tsequal()
The
system function
tsequal()
compares the value of the
column
timestamp
of a
table
with a stored timestamp, in order to avoid
updates
on a row of a table, which has changed since the time it was first viewed ("select for browse").
This method is also called
optimistic locking.
Usually, the system function tsequal() is used in the
where
clause of
update
statements. The function tsequal() should not be used in
select queries, but only in update or
insert
statements, if the result of the query returns exactly one unambiguous
row.
To make use of the system function tsequal,
tables must be altered to contain a timestamp column, if not already the case.
The system function "tsequal()" is a
T-SQL
extension. It can be executed by any
user.
Syntax of the System Function tsequal()
tsequal(timestamp1, timestamp2)
The parameters of the System Function tsequal()
"timestamp1"
The parameter "timestamp1" is the timestamp column of the row to be
changed.
"timestamp2"
The parameter "timestamp2" is the stored timestamp value.
Example for the System Function tsequal()
alter table publishers add timestamp
go
-- Every row needs a timestamp
-- This is done by updating each row with its current value
update publishers set pub_id = pub_id
go
update publishers
set city = "Springfield"
where pub_id = "0736"
and tsequal(timestamp, 0x0001000000002ea8)
Compares the stored timestamp value (0x0001000000002ea8) with the current value of the
timestamp column of the table publishers.
If both values are equal, the row is updated with the new value.
If no, this error message is returned:
The timestamp (changed to 0x0000000000015c0e) shows that the row has been
updated by another user.
Command has been aborted.
(0 rows affected)
See also:
ASE T-SQL - Aggregate Functions,
audit_event_name(),
col_length(),
col_name(),
curunreservedpgs(),
datachange(),
datalength(),
data_pages(),
data_pgs(),
db_id(),
db_name(),
host_id(),
host_name(),
identity(),
index_col(),
index_colorder(),
isnull(),
lct_admin(),
license_enabled(),
lockscheme(),
next_identity(),
object_id(),
object_name(),
pagesize(),
pointer_size(),
ptn_data_pgs(),
reserved_pages(),
reserved_pgs(),
rowcnt(),
row_count(),
showplan_in_xml(),
syb_sendmsg(),
syb_sendmsg(),
System Functions,
tempdb_id(),
tran_dumpable_status(),
used_pages(),
used_pgs().
|
