Errata

http://oreilly.com/catalog/transqlcook/chapter/ch08.html

The SQL to calculate a 90% exponential moving average is not giving the correct result. The use of x.Sales is wrong, and the weights are used in the wrong order.

Change this:

SUM(CASE WHEN y.Id=x.Id THEN 0.9*y.Sales ELSE 0.1*x.Sales END) exponential_average

To this:

SUM(CASE WHEN y.Id=x.Id THEN 0.1*y.Sales ELSE 0.9*y.Sales END) exponential_average

The figure attempts to describe the set difference. However the result set is
missing one element. As set difference is defined as : S1 S2 = { x : x in S1, x
not in S2 } the result of {a, b, c, d} {a, d, e, f } = {b, c}, as opposed to the
set {c} as shown in the diagram.

Your audit logging method will not record an update to the ProductID. On line 15 of
the UpdateStock trigger your WHERE clause is "d.ProductId=i.ProductId" (p 157). If a
user changes the ProductID the cursor will never fetch the row pair from the deleted
and inserted tables and therefore the 'E'/'N' pair will not appear in the log table.
I coded this, changed "Bananas" to "Yes we have no bananas" and verified the absence
from the StockLog table. The rest of the audit log functions (e.g., snapshots) will
not work reliably when rows are missing from StockLog.

We all know that updating what is basically a rowid is trouble-in-the-making, but you
should explicitly disallow this with the following snippet at the beginning of the
UpdateStock trigger:

IF UPDATE(ProductID)
BEGIN
RAISERROR('You cannot update ProductID because the action will not be logged.', 16,
1)
ROLLBACK TRANSACTION
RETURN
END

Please let me know what you think.

Otherwise, I like this method.

This is a trigger to record Updates to a table that I modified from O'Reilly's
"Transact-SQL Cookbook". The goal is to save space in the audit table by only storing
values that have actually changed. When I tried the original version, I discovered
that the trigger would not record the value of a column that was updated from a Null
value to an actual value. I then added some additional logic which records the
changed value regardless of whether the original value was a Null value or an actual
value.

CREATE TRIGGER AuditUpdate
ON [Table]
FOR UPDATE
AS
BEGIN

INSERT INTO AuditTrail(TableName, Action, NetUserID, Node, ActionDate, EventID,
[Column_Name])
SELECT 'TableName', 'O', system_user, host_name(), current_timestamp, newid(),
(CASE WHEN d.[ColumnName]!=i.[ColumnName] THEN d.[ColumnName] ELSE NULL END)
FROM deleted d, inserted i
WHERE d.[ColumnName]=i.[ColumnName]
/* 'O' indicates this row is storing Old values.*/

INSERT INTO AuditTrail(TableName, Action, NetUserID, Node, ActionDate, EventID,
[Column_Name])
SELECT 'TableName', 'N', system_user, host_name(), current_timestamp, newid(),
/* Here is the logic which overcame the problem of not recording a change from a Null
value to an actual value */
(CASE WHEN d.[ColumnName] IS NULL THEN i.[ColumnName] WHEN
d.[ColumnName]!=i.[ColumnName] THEN i.[ColumnName] ELSE NULL END)
FROM deleted d, inserted i
WHERE d.[ColumnName]=i.[ColumnName]
/* 'N' indicates this row is storing New values.*/

END

/* The original logic was as follows: */
/*
(CASE WHEN d.[ColumnName]!=i.[ColumnName] THEN i.[ColumnName] ELSE NULL END)
*/

Adding the case statement to the SELECT will not properly return the median when there is an even number (n) of observations and the two middle observations (n/2 & (n/2+1)) are equal.

Example:
series = {1,2,3,3,4,5}
median = 3
financial median = 3
Transact-SQL Cookbook financial median = 3.5