Transact-SQL Cookbook
by Jonathan Gennick, Ales Spetic

The unconfirmed error reports are from readers.  They have not yet been 
approved or disproved by the author or editor and represent solely the
opinion of the reader.

Here's a key to the markup:
        [page-number]: serious technical mistake
        {page-number}: minor technical mistake
        <page-number>: important language/formatting problem
        (page-number): language change or minor formatting problem
        ?page-number?: reader question or request for clarification

This page was updated June 12, 2008.


UNCONFIRMED  errors and comments from readers: 


[12]  Figure 2.7;
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.


{22}  Solution;
The query reads "GROUP BY CourseId, StudentName", but the result is ordered as if the
clause were "GROUP BY StudentName, CourseId".


[157]  top of page, inside "CREATE TRIGGER UpdateStock";
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.


[169]  Bottom of page, and continuing to the next page;
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)
*/


{242} You claim a 95% confidence value for the t-distribution table, whereas you
are actually using 97.5% confidence value.


[258] 8.5, calculating the financial median;
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


(2.2)  ONLINE VERSION 3rd paragraph;
****
(About the page no. I'm reading this online.http://safari.informit.com)
****
2.2 The Students Example
After Create Table states:
"If you execute the ""ch01.ImplementingSetDifference.objects.sql"" script, all the
tables needed for the recipes in this chapter will be created and populated with
data."
The downloaded file is "ch02".ImplementingSetDifference.objects.sql