StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The most simple solution uses triggers. Take a look at his example. Maybe will help you. Also, you can find several comments in source code.</p> <pre><code>--Test initialization CREATE TABLE dbo.[Transaction] ( ID INT IDENTITY(1,1) PRIMARY KEY, InsertDateTime DATETIME NOT NULL DEFAULT GETDATE(), [Date] DATETIME, CustomerID VARCHAR(50), [Type] VARCHAR(3), Amount MONEY ); GO INSERT INTO [Transaction]([Date],CustomerID,[Type],Amount) SELECT '2011-01-01','CUST1001','INV','12,000' UNION ALL SELECT '2011-01-02','CUST1001','PAY','3,000' UNION ALL SELECT '2011-01-03','CUST1001','INV','8,000' UNION ALL SELECT '2011-01-04','CUST1001','PAY','9,000' UNION ALL SELECT '2011-01-04','CUST1002','INV','1,000' UNION ALL SELECT '2011-01-05','CUST1002','PAY','5,000' UNION ALL SELECT '2011-01-05','CUST1002','INV','6,000'; GO CREATE TABLE AuditTransaction ( AuditID INT IDENTITY(1,1) PRIMARY KEY ,UpdateDateTime DATETIME NOT NULL DEFAULT GETDATE() ,ID INT NOT NULL ,[Date] DATETIME NULL ,CustomerID VARCHAR(50) NULL ,[Type] VARCHAR(3) NULL ,Amount MONEY NULL ); GO CREATE TRIGGER trgI_Transaction_Logging ON dbo.[Transaction] AFTER UPDATE AS BEGIN SET NOCOUNT ON; --Will be inserted only the old values (d.* = deleted.*) . Otherwise NULL will be inserted. INSERT dbo.AuditTransaction(ID,[Date],CustomerID,[Type],Amount) SELECT i.ID --If the old value (d.[Date]) and the new one (i.[Date]) are both equals --then "NULL" --else "old value" (d.[Date]) ,NULLIF(d.[Date],i.[Date]) ,NULLIF(d.CustomerID,i.CustomerID) ,NULLIF(d.[Type],i.[Type]) ,NULLIF(d.Amount,i.Amount) FROM deleted d INNER JOIN inserted i ON d.ID=i.ID; END; GO --End of Test initialization --Test: run UPDATEs WAITFOR DELAY '00:00:01'; UPDATE dbo.[Transaction] SET Amount = Amount + 5 WHERE ID = 1; --1 second delay between UPDATEs WAITFOR DELAY '00:00:01'; UPDATE dbo.[Transaction] SET Amount = Amount + 9 ,[Date] = '2011-01-02 22:22:22' WHERE ID = 2; WAITFOR DELAY '00:00:01'; UPDATE dbo.[Transaction] SET Amount = Amount + 9 ,[Date] = '2011-01-02 11:11:11' WHERE ID = 1; WAITFOR DELAY '00:00:01'; UPDATE dbo.[Transaction] SET Amount = Amount + 10 WHERE ID = 3; WAITFOR DELAY '00:00:01'; UPDATE dbo.[Transaction] SET Amount = Amount + 20 WHERE ID = 4; WAITFOR DELAY '00:00:01'; UPDATE dbo.[Transaction] SET CustomerID = NULL WHERE ID = 5; --End of UPDATEs PRINT 'dbo.AuditTransaction content' SELECT * FROM dbo.AuditTransaction at; --Test: checking ”log” (audit table) for the first transaction DECLARE @TransactioID INT; SELECT @TransactioID = 1; PRINT 'Transaction 1 history' SELECT 'I' AS OperationType, t.[InsertDateTime] OperationDateTime, 0 AS AuditID ,t.ID, t.[Date], t.CustomerID, t.[Type], t.Amount FROM dbo.[Transaction] t WHERE t.ID = @TransactioID UNION ALL SELECT 'U' AS OperationType, at.UpdateDateTime, at.AuditID ,at.ID, at.[Date], at.CustomerID, at.[Type], at.Amount FROM dbo.AuditTransaction at WHERE at.ID = @TransactioID ORDER BY OperationType DESC, OperationDateTime; --End of test --By, by DROP TABLE dbo.AuditTransaction; DROP TABLE dbo.[Transaction]; GO </code></pre> <p>Results:</p> <pre><code>dbo.AuditTransaction content AuditID UpdateDateTime ID Date CustomerID Type Amount ----------- ----------------------- ----------- ----------------------- -------------------------------------------------- ---- --------------------- 1 2011-10-02 13:48:22.683 1 NULL NULL NULL 12000,00 2 2011-10-02 13:48:23.677 2 2011-01-02 00:00:00.000 NULL NULL 3000,00 3 2011-10-02 13:48:24.680 1 2011-01-01 00:00:00.000 NULL NULL 12005,00 4 2011-10-02 13:48:25.680 3 NULL NULL NULL 8000,00 5 2011-10-02 13:48:26.683 4 NULL NULL NULL 9000,00 6 2011-10-02 13:48:27.683 5 NULL CUST1002 NULL NULL (6 row(s) affected) Transaction 1 history OperationType OperationDateTime AuditID ID Date CustomerID Type Amount ------------- ----------------------- ----------- ----------- ----------------------- -------------------------------------------------- ---- --------------------- U 2011-10-02 13:56:41.420 1 1 NULL NULL NULL 12000,00 U 2011-10-02 13:56:43.430 3 1 2011-01-01 00:00:00.000 NULL NULL 12005,00 I 2011-10-02 13:56:40.363 0 1 2011-01-02 11:11:11.000 CUST1001 INV 12014,00 (3 row(s) affected) </code></pre> <p>How can be interpred these results ? The 'I' rows contain the actual values while the 'U' rows contain the old values.</p> <p>Example:</p> <ol> <li>For <code>Amount</code> field the first value was 12014 and the last value is 12014.</li> <li><code>CustomerID</code> field has not modification.</li> <li><code>[Date]</code> field was modified from <code>2011-01-01 00:00:00.000</code> to <code>2011-01-02 11:11:11.000</code> on <code>2011-10-02 13:56:43.430</code>.</li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload