March 10, 2010 at 9:39 am
Paul great stuff. I will add in the remaining 65 columns :).
Many Thanks,
Phil.
PS: If I add all additional columns from the table to which sections of your code must they be added?
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 10, 2010 at 7:23 pm
2Tall (3/10/2010)
Paul great stuff. I will add in the remaining 65 columns :). If I add all additional columns from the table to which sections of your code must they be added?
I thought there might be more columns. Add columns to the Selection CTE and the UNPIVOT list.
I would encourage you to run each CTE section of the query separately and experiment to make sure you fully understand how it works 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 7:50 pm
Demonstration code:
-- For demonstration purposes
USE tempdb;
GO
-- Drop tempdb objects
IF OBJECT_ID(N'tempdb.dbo.Products', N'U')
IS NOT NULL
DROP TABLE tempdb.dbo.Products;
IF OBJECT_ID(N'tempdb.dbo.ProductsHistory', N'U')
IS NOT NULL
DROP TABLE tempdb.dbo.ProductsHistory;
IF OBJECT_ID(N'tempdb.dbo.ShowProductAudit', N'P')
IS NOT NULL
DROP PROCEDURE dbo.ShowProductAudit;
GO
-- Create tables
CREATE TABLE [dbo].[Products](
[Product] [bigint] IDENTITY(1,1) NOT NULL,
[ProductId] [nvarchar](100) NOT NULL CONSTRAINT [DF_Products_Product] DEFAULT (''),
[ProductDescription] [nvarchar](300) NOT NULL CONSTRAINT [DF_Products_ProductDescription] DEFAULT (''),
[Type] [char](1) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [DF_Products_Type] DEFAULT (''),
[StandardPrice] [decimal](17, 5) NOT NULL CONSTRAINT [DF_Products_StandardPrice] DEFAULT ((0)),
[EngineeringOnly] [bit] NOT NULL CONSTRAINT [DF_Products_EngineeringOnly] DEFAULT ((0)),
[StructureVersion] [bigint] NULL,
CONSTRAINT [PK_Products] PRIMARY KEY NONCLUSTERED ([Product] ASC)
);
GO
CREATE TABLE [dbo].[ProductsHistory]
(
[ProductHistory] [bigint] IDENTITY(1,1) NOT NULL,
[Product] [bigint] NOT NULL,
[ProductId] [nvarchar](100) NOT NULL CONSTRAINT [DF_ProductHistory_Product] DEFAULT (''),
[ProductDescription] [nvarchar](300) NOT NULL CONSTRAINT [DF_ProductHistory_ProductDescription] DEFAULT (''),
[Type] [char](1) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [DF_ProductHistory_Type] DEFAULT (''),
[StandardPrice] [decimal](17, 5) NOT NULL CONSTRAINT [DF_ProductHistory_StandardPrice] DEFAULT ((0)),
[EngineeringOnly] [bit] NOT NULL CONSTRAINT [DF_ProductHistory_EngineeringOnly] DEFAULT ((0)),
[StructureVersion] [bigint] NULL,
[HistoricUser] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
[HistoricDate] [datetime] NOT NULL,
CONSTRAINT [PK_ProductsHistory] PRIMARY KEY NONCLUSTERED ([ProductHistory] ASC)
)
GO
-- Create the update audit trigger
-- UPDATEs cause the pre-update data to be written to the history table
CREATE TRIGGER [dbo.Products AIU]
ON dbo.Products
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
SET ROWCOUNT 0;
INSERT ProductsHistory
(Product, ProductId, ProductDescription, Type, StandardPrice, EngineeringOnly, StructureVersion, HistoricUser, HistoricDate)
SELECT Product, ProductId, ProductDescription, Type, StandardPrice, EngineeringOnly, StructureVersion, SUSER_SNAME(), CURRENT_TIMESTAMP
FROM deleted;
END
GO
--Insert a sample product
SET IDENTITY_INSERT dbo.Products ON
INSERT Products
(Product,ProductId,ProductDescription,Type,StandardPrice,EngineeringOnly,StructureVersion)
VALUES (55085, N'11-222-333', N'Assembly', 'A', 123.45678, 0, 55104);
SET IDENTITY_INSERT dbo.Products OFF;
GO
-- The procedure to show audit details for a product
CREATE PROCEDURE dbo.ShowProductAudit
@Product BIGINT
AS
BEGIN
SET NOCOUNT ON;
-- Table to hold each column that changed
CREATE TABLE #KeyValuePairs
(
Sequence BIGINT NOT NULL,
ProductHistory BIGINT NOT NULL,
Name SYSNAME NOT NULL,
Value SQL_VARIANT NOT NULL,
PRIMARY KEY (Name, Sequence)
);
WITH SourceData
AS (
-- All the columns from the History table
-- converted to SQL_VARIANT
SELECT PH.ProductHistory,
ProductId = CONVERT(SQL_VARIANT, PH.ProductId),
ProductDescription = CONVERT(SQL_VARIANT, PH.ProductDescription),
Type = CONVERT(SQL_VARIANT, PH.Type),
StandardPrice = CONVERT(SQL_VARIANT, PH.StandardPrice),
EngineeringOnly = CONVERT(SQL_VARIANT, PH.EngineeringOnly),
StructureVersion = CONVERT(SQL_VARIANT, PH.StructureVersion),
HistoricUser = CONVERT(SQL_VARIANT, PH.HistoricUser),
HistoricDate = CONVERT(SQL_VARIANT, PH.HistoricDate)
FROM dbo.ProductsHistory PH
WHERE PH.Product = @Product
UNION ALL
-- The current records
-- NULLs used for columns that only exist in the History table
SELECT 2147483647,
P.ProductId,
P.ProductDescription,
P.Type,
P.StandardPrice,
P.EngineeringOnly,
P.StructureVersion,
NULL,
NULL
FROM dbo.Products P
WHERE P.Product = @Product
)
INSERT #KeyValuePairs
(Sequence, ProductHistory, Name, Value)
SELECT ROW_NUMBER() OVER (
PARTITION BY Name
ORDER BY ProductHistory),
KeyValuePairs.ProductHistory,
KeyValuePairs.Name,
KeyValuePairs.Value
FROM SourceData SD
UNPIVOT (
-- Break the audit columns into key/value pairs
Value
FOR Name IN
(
-- Just the columns to audit changes to
SD.ProductId,
SD.ProductDescription,
SD.[Type],
SD.StandardPrice,
SD.EngineeringOnly
)
) KeyValuePairs;
-- RESULTS
SELECT -- Audit details
SubQuery.Name,
SubQuery.changed_from,
SubQuery.changed_to,
SubQuery.ProductHistory,
-- Extra stuff from the History record
PH.HistoricUser,
PH.HistoricDate
FROM (
SELECT This.Name,
changed_from = Preceding.Value,
changed_to = This.Value,
Preceding.ProductHistory
FROM #KeyValuePairs This
JOIN #KeyValuePairs Preceding
ON Preceding.Name = This.Name
AND Preceding.Sequence = This.Sequence - 1
AND Preceding.Value <> This.Value
) SubQuery
JOIN dbo.ProductsHistory PH
ON PH.ProductHistory = SubQuery.ProductHistory
ORDER BY
SubQuery.Name,
SubQuery.ProductHistory;
END;
GO
--
-- === TESTS ===
--
-- Clear history
TRUNCATE TABLE dbo.ProductsHistory;
-- Show the product
SELECT *
FROM dbo.Products
WHERE Product = 55085;
-- Do some updates and show the audit after each
UPDATE Products SET StandardPrice = $1;
EXECUTE dbo.ShowProductAudit 55085;
UPDATE Products SET ProductDescription = 'Assembly Desc 2'
EXECUTE dbo.ShowProductAudit 55085;
UPDATE Products SET StandardPrice = $2
EXECUTE dbo.ShowProductAudit 55085;
UPDATE Products SET StandardPrice = $1;
EXECUTE dbo.ShowProductAudit 55085;
UPDATE Products SET ProductDescription = 'Assembly Desc 3'
EXECUTE dbo.ShowProductAudit 55085;
GO
-- Drop tempdb objects
IF OBJECT_ID(N'tempdb.dbo.Products', N'U')
IS NOT NULL
DROP TABLE tempdb.dbo.Products;
IF OBJECT_ID(N'tempdb.dbo.ProductsHistory', N'U')
IS NOT NULL
DROP TABLE tempdb.dbo.ProductsHistory;
IF OBJECT_ID(N'tempdb.dbo.ShowProductAudit', N'P')
IS NOT NULL
DROP PROCEDURE dbo.ShowProductAudit;
GO
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2010 at 1:11 am
Stage-by-stage breakdown:
--
-- *** STEP 1 ***
--
-- Get all the data for a particular product from the
-- history table and the current Products record
--
-- The columns are converted to SQL_VARIANT for the UNPIVOT
-- that comes next. UNPIVOT requires that all columns have
-- the same data type. SQL_VARIANT can hold most different
-- types.
--
-- A fixed value of 9223372036854775807 is used as the ProductHistory
-- reference for the current record. This just ensures that
-- the current record always has the highest possible value.
--
-- 9,223,372,036,854,775,807 is the highest possible value of a BIGINT.
--
-- The product to audit
DECLARE @Product BIGINT;
SET @Product = 55085;
-- Holds the results from step 1
DECLARE @Step1
TABLE (
ProductHistory BIGINT NOT NULL PRIMARY KEY,
ProductId SQL_VARIANT NULL,
ProductDescription SQL_VARIANT NULL,
Type SQL_VARIANT NULL,
StandardPrice SQL_VARIANT NULL,
EngineeringOnly SQL_VARIANT NULL
);
-- Find the current and history data
INSERT @Step1
(ProductHistory, ProductId, ProductDescription, Type, StandardPrice, EngineeringOnly)
SELECT -- History
PH.ProductHistory,
ProductId = CONVERT(SQL_VARIANT, PH.ProductId),
ProductDescription = CONVERT(SQL_VARIANT, PH.ProductDescription),
Type = CONVERT(SQL_VARIANT, PH.Type),
StandardPrice = CONVERT(SQL_VARIANT, PH.StandardPrice),
EngineeringOnly = CONVERT(SQL_VARIANT, PH.EngineeringOnly)
FROM dbo.ProductsHistory PH
WHERE PH.Product = @Product
UNION ALL
SELECT -- Current
9223372036854775807,
P.ProductId,
P.ProductDescription,
P.Type,
P.StandardPrice,
P.EngineeringOnly
FROM dbo.Products P
WHERE P.Product = @Product;
-- Show the results of step 1
SELECT ProductHistory, ProductId, ProductDescription, Type, StandardPrice, EngineeringOnly
FROM @Step1
ORDER BY
ProductHistory;
--
-- *** STEP 2 ***
--
-- Use UNPIVOT to break each value up into a column name & value pair
-- We only UNPIVOT the columns we want to audit
--
-- Holds the results from step 2
DECLARE @Step2
TABLE (
Name SYSNAME NOT NULL,
Value SQL_VARIANT NULL,
ProductHistory BIGINT NOT NULL,
PRIMARY KEY (Name, ProductHistory)
);
-- The UNPIVOT
INSERT @Step2
(Name, Value, ProductHistory)
SELECT KeyValuePairs.Name,
KeyValuePairs.Value,
KeyValuePairs.ProductHistory
FROM @Step1 S1
UNPIVOT (
Value
FOR Name IN
(
-- Just the audit columns. Each value will be split
-- into Name and Value
S1.ProductId,
S1.ProductDescription,
S1.[Type],
S1.StandardPrice,
S1.EngineeringOnly
)
) KeyValuePairs;
-- Show the results of step 2
SELECT S2.Name,
S2.Value,
S2.ProductHistory
FROM @Step2 S2
ORDER BY
S2.Name,
S2.ProductHistory;
--
-- *** STEP 3 ***
--
-- Assign a sequence number to each row,
-- in the order of ProductHistory
-- restarting the numbering every time
-- Name changes.
--
-- Holds the results from step 3
DECLARE @Step3
TABLE (
Sequence BIGINT NOT NULL,
Name SYSNAME NOT NULL,
Value SQL_VARIANT NOT NULL,
ProductHistory BIGINT NOT NULL,
PRIMARY KEY (Name, Sequence)
);
-- Row numbering
INSERT @Step3
(Sequence, Name, Value, ProductHistory)
SELECT ROW_NUMBER()
OVER (
PARTITION BY S2.Name
ORDER BY S2.ProductHistory),
S2.Name,
S2.Value,
S2.ProductHistory
FROM @Step2 S2;
-- Show the results of step 3
SELECT S3.Sequence,
S3.Name,
S3.Value,
S3.ProductHistory
FROM @Step3 S3
ORDER BY
S3.Name,
S3.Sequence;
-- *** STEP 4 ***
--
-- Join adjacent rows to find only values
-- that changed
--
-- Holds the results from step 4
DECLARE @Step4
TABLE (
Name SYSNAME NOT NULL,
changed_from SQL_VARIANT NULL,
changed_to SQL_VARIANT NULL,
ProductHistory BIGINT NOT NULL,
PRIMARY KEY (Name, ProductHistory)
);
-- Join previous and current values
INSERT @Step4
(Name, changed_from, changed_to, ProductHistory)
SELECT This.Name,
changed_from = Preceding.Value,
changed_to = This.Value,
Preceding.ProductHistory
FROM @Step3 This
JOIN @Step3 Preceding
ON Preceding.Name = This.Name -- Same column name
AND Preceding.Sequence = This.Sequence - 1 -- Adjacent
AND Preceding.Value <> This.Value; -- The value changed
-- Show the results from step 4
SELECT S4.Name,
S4.changed_from,
S4.changed_to,
S4.ProductHistory
FROM @Step4 S4
ORDER BY
S4.Name;
-- *** STEP 5 **
--
-- Join back to ProductHistory to get extra column details
--
SELECT -- Audit details
S4.Name,
S4.changed_from,
S4.changed_to,
S4.ProductHistory,
-- Extra stuff from the History record
PH.HistoricUser,
PH.HistoricDate
FROM @Step4 S4
JOIN dbo.ProductsHistory PH
ON PH.ProductHistory = S4.ProductHistory
ORDER BY
S4.Name,
S4.ProductHistory;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply