Audit View/Report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Audit View/Report

Author

Topic

lb6688
Starting Member

18 Posts

Posted - 2010-08-19 : 14:15:56

Trying to implement audit trail using Karen’s menthod (audit table is a mirroring data table with a couple of extra audit related columns like type, datetime and userid), the performance is great compare to using “tablename, audittype, auditdate, oldvalue, newvalue” approach, however, if there a way to still product a report/view to show “tablename, audittype, auditdate, oldvalue, newvalue”, basically to compare all columns in auditable row by row, something like:

Audit Table:

AuditType AuditDateTime AuditUser Street State Zip
------------- ------------------- ------------ ------------------- ------ ----
U 8/12/10 1:00PM john 22 Col Street OH 12345
U 7/11/10 2:00PM kate 12 Col Street OH 12345
U 7/11/10 1:00PM kate 11 Col Street OH 12355

View/Report We’d like:

AuditType AuditdateTime AuditUser Col Before After
------------- ------------------ ------------ ---- -------- ----------
U 7/11/10 2:00PM kate Street 11 Col Street 12 Col Street
U 7/11/10 2:00PM kate Zip 12355 12345
U 7/12/10 1:00PM john Street 12 Col Street 22 Col Street

Possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 14:22:18

are you using SQL 2005 or above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lb6688
Starting Member

18 Posts

Posted - 2010-08-19 : 16:13:07

Yes, thanks!!!

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-20 : 01:17:29

quote:
Originally posted by lb6688

Yes, thanks!!!

what do you mean yes are you using sql 2005 or which server are you using

With Regards
Kashyap M

lb6688
Starting Member

18 Posts

Posted - 2010-08-20 : 11:34:23

We are using SQL server 2k5. Sorry.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 11:56:19

something like :-


;With CTE
AS
(
SELECT AuditType, 
AuditDateTime, 
AuditUser,
Val,
Col
FROM Table
UNPIVOT(Val FOR Col IN ([Street],[State],[Zip]))u
)

SELECT c1.AuditType, 
c1.AuditdateTime, 
c1.AuditUser,
c1.Col,
c2.Val AS Before,
c1.Val AS After
FROM CTE c1
OUTER APPLY 
(
SELECT TOP 1 Val
FROM CTE
WHERE AuditType = c1.AuditType 
AND AuditdateTime < c1.AuditdateTime
AND AuditUser = c1.AuditUser
AND Col = c1.Col
)c2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-08-21 : 03:55:29

I hate that type of report (but that's just me! no offence intended)

What we do is to display the rows, in a grid, from the audit table and highlight values that are different to the previous row [in the audit table]. That way it is easy to see what changed at the same time as something else.

Just my $0.02 ...

lb6688
Starting Member

18 Posts

Posted - 2010-08-21 : 09:30:49

Thanks visakh16 for your time, Thanks Kristen (I hate that too, but users insist that "type" is easy to "read"). One question, How do you "highlight" values that are different to previous row, I am using writing report on the company intranet, I cannot think of an easy way to highlight the differences without loop through each column
from returned ADO recordsets, no?

Kristen
Test

22859 Posts

Posted - 2010-08-21 : 10:35:30

We have a function built into our frontend that compared columns to previous row in resultset and outputs some extra CSS if there is a difference.

If that is not possible you could perhaps do it in SQL - bit tedious! - add an extra column, for EVERY data column

, outputting a "style" - basically a value for whether the value is same as previous row, or different, and then in the front end report writer use that to apply highlight to the column's value.

lb6688
Starting Member

18 Posts

Posted - 2010-08-21 : 11:14:23

Thanks Kristen, I know CSS well and know enough Jquery so your method would work wonderfully. Any chance of share a little bit of your "function" code to get me started? Maybe store the resultset in a 2-d array?

lb6688
Starting Member

18 Posts

Posted - 2010-08-22 : 11:44:34

Sorry for been a pain, the audit trigger works great but it insert a row in the audit table even nothing changes (for example, user click on save on a edit address form/page). It's not a big deal we have a few of those identical rows in the audit table, but if there is easy way to only insert row when there is actual change in any of columns.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-22 : 12:24:24

you can add a check like

IF EXISTS(SELECT 1 FROM...)

inside Audit trigger before you do the saves to do save only if any of your interested value changes.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lb6688
Starting Member

18 Posts

Posted - 2010-08-22 : 13:03:42

Okay, I am trying to make the change here is what I have, still not working, What did I do wrong... HELP... (As you can see, I am very new to SQL)

BEFORE (Insert regardless whether changes were made to the table):
ALTER TRIGGER dbo.tr_MyDataTable
ON dbo.MyDataTable
AFTER UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON

INSERT dbo.Audit_MyDataTable
SELECT [AuditType] = CASE WHEN I.RecordNum IS NULL THEN 'D' ELSE 'U' END,
[AuditUser] = system_user, [AuditDate] = GetDate(),
D.*
FROM deleted D
LEFT OUTER JOIN inserted I
ON I.RecordNum = D.RecordNum
GO

AFTER (Hoping to only insert when any change to any column):
CREATE TRIGGER dbo.tr_MyDataTable
ON dbo.MyDataTable
AFTER UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON

DECLARE @AuditType varchar(1)

If exists (select 1 from inserted) and exists (select 1 from deleted)
BEGIN
--'Update happened'
SELECT @AuditType = 'U'
END
ELSE
BEGIN
if exists( select 1 from inserted)
BEGIN
--'Insert happened'
SELECT @AuditType = 'I'
END
ELSE
BEGIN
--'Delete happened'
SELECT @AuditType = 'D'
END
END
IF @AuditType = 'D' or @AuditType = 'U'
BEGIN
INSERT dbo.Audit_MyDataTable
SELECT [AuditType] = @AuditType,
[AuditUser] = @WebData, [AuditDate] = GetDate(),
D.*
FROM deleted D
LEFT OUTER JOIN inserted I
ON I.RecordNum = D.RecordNum
END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-22 : 13:08:33

when do you want details to be saved in audit table? based on what column changes?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lb6688
Starting Member

18 Posts

Posted - 2010-08-22 : 15:12:01

I'd like to save a record (before change) in audit table based on ANY column changes, possible?

Kristen
Test

22859 Posts

Posted - 2010-08-23 : 03:20:27

Just compare all the columns between INSERTED and DELETED tables and only insert if at least one of them is different

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 04:54:48

quote:
Originally posted by lb6688

I'd like to save a record (before change) in audit table based on ANY column changes, possible?

just do check like


IF EXISTS(SELECT 1
          FROM INSERTED i
          JOIN DELETED d
          ON d.PK=i.PK
          WHERE d.Col1<> i.Col1
           OR d.Col2 <> i.Col2
          ...... )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-08-23 : 10:27:15

We use sometihng a bit more detailed than that:

WHERE (d.Col1 <> i.Col1 OPTIONAL COLLATE OR (d.Col1 IS NULL AND i.Col1 IS NOT NULL) OR (d.Col1 IS NOT NULL AND i.Col1 IS NULL))
OR ...

The OPTIONAL COLLATE is a Binary Collation on VARCHAR columns to force case-sensitivity - so that changes ONLY to Case of letters are regarded as a change (i.e. where a Case or Accent INSENSITIVE Collation is the default on the column)

lb6688
Starting Member

18 Posts

Posted - 2010-08-23 : 12:15:13

THANKS to both of you, I will try and post back !!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 12:18:12

good luck!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lb6688
Starting Member

18 Posts

Posted - 2010-08-23 : 21:53:46

Okay, I did what you suggested and the trigger works as it intended, however, this means I need to manually change the trigger if table design changes unless I have some sort "auto" generated script to re-generate the trigger after the column changes. I tried to use ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS to build loop through the columns but the trigger was noticeable slower (which was the reason I want use Kristen's “mirror” table trigger to improve the performance) .

For now, I will stick with original trigger (insert record into audit table every time use click on "save" regardless whether changes made to the columns), maybe this is teach them not to be "save" happy.

Thanks again for both of you, I now need to move on to “Audit View/Report”.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources