Incrementing or updating a TIMESTAMP field.

[Robin Tucker]

Hi,

I'm in the process of implementing a multi-user system containing an
adjacency list (tree structure). I'm using a TIMESTAMP field on each
record in the adjacency list in order to tell when a node has been changed
since the last read. Sometimes though, it is useful to flag a "parent" (or
all ancestors or a node) as being changed if any of its children have. Is
there any way I can force an update to the parent TIMESTAMP field without
actually modifying any of the other fields in the record? Something like
this (assuming I have a field called [Timestamp]):

UPDATE Adjacency SET [Timestamp] = [Timestamp] + 1 WHERE ID = @_In_ID

Now, will the timestamp be incremented here by 1, or will SQL server get
confused as in theory it must "update the timestamp after updating the
timestamp" for this record?

Thanks,

Robin

[David Portas]

You can't update a timestamp. You could do this:

UPDATE Adjacency
SET some_column = some_column
WHERE ...

Be aware that update triggers will also fire if you have them.
--
David Portas
SQL Server MVP
--

[--CELKO--]

Tables are files; rows are not records; columns are not fields. You
are still thinking in the terms of a 1950's file system, which is
probably what lead to the adjacency list model -- it looks like pointer
chains instead of sets.

I'm using a TIMESTAMP fie*ld [sic] on each record [sic] in the

adjacency list in order to tell when a node ha*s been changed since
the last read. Sometimes though, it is useful to flag* a "parent" (or
all ancestors or a node) as being changed if any of its chil*dren
have. Is there any way I can force an update to the parent TIMESTAMP
*field [sic] without actually modifying any of the other fields [sic]
in the record [sic] ? <<

Want to move over to the Nested sets model and make life easier?
First put the nodes in their own table and the tree structure in
another.

--update all superiors
UPDATE Nodes
SET last_change_date = CURRENT_TIMESTAMP
WHERE node-id
IN (SELECT T2.node_id
FROM Tree AS T1, Tree AS T2
WHERE T1.lft BETWEEN T2.lft AND T2.rgt
AND T1.node_id = @:my_node_id;

--update imediate superior only
UPDATE Nodes
SET last_change_date = CURRENT_TIMESTAMP
WHERE node-id
IN (SELECT B.node_id AS boss
FROM Tree AS E
LEFT OUTER JOIN
Tree AS B
ON B.lft
= (SELECT MAX(lft)
FROM Tree AS S
WHERE E.lft > S.lft
AND E.lft < S.rgt));

Get a copy of my book TREES & HIERARCHIES IN SQL for details and code
samples.

[Robin Tucker]

Hi Celko,

I followed your ideas and actually tried out an implementation of nested
sets at some point in the past (about 1 year ago). It was a neat solution
but suffered from not being quite as comprehensible as simply storing a path
string to each node and also if suffers from the requirement that
modifications to a node (insert for example) results in the potential
renumbering of many nodes in the subtree.

In the interests of future maintenance I wanted something basic and didn't
care too much about performance (within reason). I also had a go at
Tropashenkos (I think) materialized path (using binary fractions)! This was
a miserable failure due to numerical accuracy and unbelievably slow
performance (on SQL Server).

Anyway, interesting stuff.

Robin

"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
Tables are files; rows are not records; columns are not fields. You
are still thinking in the terms of a 1950's file system, which is
probably what lead to the adjacency list model -- it looks like pointer
chains instead of sets.

I'm using a TIMESTAMP fie*ld [sic] on each record [sic] in the

adjacency list in order to tell when a node ha*s been changed since
the last read. Sometimes though, it is useful to flag* a "parent" (or
all ancestors or a node) as being changed if any of its chil*dren
have. Is there any way I can force an update to the parent TIMESTAMP
*field [sic] without actually modifying any of the other fields [sic]
in the record [sic] ? <<

Want to move over to the Nested sets model and make life easier?
First put the nodes in their own table and the tree structure in
another.

--update all superiors
UPDATE Nodes
SET last_change_date = CURRENT_TIMESTAMP
WHERE node-id
IN (SELECT T2.node_id
FROM Tree AS T1, Tree AS T2
WHERE T1.lft BETWEEN T2.lft AND T2.rgt
AND T1.node_id = @:my_node_id;

--update imediate superior only
UPDATE Nodes
SET last_change_date = CURRENT_TIMESTAMP
WHERE node-id
IN (SELECT B.node_id AS boss
FROM Tree AS E
LEFT OUTER JOIN
Tree AS B
ON B.lft
= (SELECT MAX(lft)
FROM Tree AS S
WHERE E.lft > S.lft
AND E.lft < S.rgt));

Get a copy of my book TREES & HIERARCHIES IN SQL for details and code
samples.