Posted by Erland Sommarskog on 10/02/35 11:44

js (androidsun@yahoo.com) writes:
> Thank you. It works with minor modification. Now I would like to use
> a trigger so that upon insert the elapsed time will be posted in Table
> A column (int) "TimeLapse". However, it would not accept the value.
> Can you help?

You must correlate the computation of elapsed with a row in A. The
easiest way is to use the proprietary FROM/JOIN syntax supported by
MS SQL Server:

Update A
set TimeLapse = Btot.elapsed
FROM A
JOIN (SELECT B.ID, elapsed = SUM(elapsed)
FROM (SELECT B1.ID, B1.[TimeStamp] - B2.[TimeStamp] AS elapsed,
B2.ChgCode, B2.description
FROM B B1
JOIN B B2 ON B1.ID = B2.ID
AND B2.[TimeStamp] =
(SELECT MAX(B3.Timestamp)
FROM B B3
WHERE B3.ID = B1.ID
AND B3.TimeStamp < B1.TimeStamp)
WHERE not (B1.ChgCode = 'S' and
(b1.description like '%resume%' or
b1.description like '%don''t care%'))
OR (b1.description IS NULL)) B
GROUP BY B.ID) AS Btot = A.ID = B.ID

However, neither this is entierly satisfactory, as you are reading the
entire B table a couple of times on each insert, and this could be
expensive. SQL Server offers the the virtual tables "inserted" and
"deleted" which holds after-image and before-images of the rows
affected by the statement. (For an INSERT, there are only rows in
"inserted" obviously.)

Rewriting the trigger to look at inserted is not trivial, least of all
if rows can be inserted out of order. (What if a "don't care" row is
inserted in the middle of it all?)

Not knowing the exact scenario where this appears I prefer to not suggest
a solution.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]