Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Audit Triggers Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 10:04 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, August 1, 2014 10:55 AM
Points: 701, Visits: 1,732
Hi,

I need to create two triggers on my small lookup tables for audit purposes.

The triggers are for any inserts and any updates they would write the new row to my newly created audit tables '_HIST'

How do I go about doing that, so that it writes every column in my table to the history table no matter if just one column was updated/inserted?




Post #1382604
Posted Thursday, November 8, 2012 10:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 5,035, Visits: 11,772
I did a quick search and found this. Looks good enough to point you in the right direction, but there are loads of easy-to-find resources out there courtesy of Google.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1382614
Posted Thursday, November 8, 2012 10:35 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, August 1, 2014 10:55 AM
Points: 701, Visits: 1,732
Thanks Phil..

This is what I have so far...

Is this going to copy only the changed (updated/inserted) record?

CREATE TRIGGER update_delete ON dbo.WSI_T_CUSTOM_BENFIT_RATES
FOR INSERT, UPDATE
AS
UPDATE WSI_T_CUSTOM_BENFIT_RATES_HIST
FROM WSI_T_CUSTOM_BENFIT_RATES



Post #1382622
Posted Thursday, November 8, 2012 10:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 5,035, Visits: 11,772
krypto69 (11/8/2012)
Thanks Phil..

This is what I have so far...

Is this going to copy only the changed (updated/inserted) record?

CREATE TRIGGER update_delete ON dbo.WSI_T_CUSTOM_BENFIT_RATES
FOR INSERT, UPDATE
AS
UPDATE WSI_T_CUSTOM_BENFIT_RATES_HIST
FROM WSI_T_CUSTOM_BENFIT_RATES


No. Did you read the link I sent?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1382635
Posted Thursday, November 8, 2012 11:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Here are some articles I wrote a while back on data auditing:

http://www.sqlservercentral.com/articles/Auditing/63247/
http://www.sqlservercentral.com/articles/Auditing/63248/

There are a number of trigger-based options and samples in the second one.

With regard to a trigger to record updates and inserts, what you need to do in the query part of the trigger is select from the "inserted" or "deleted" table.

Here's how I like to do trigger-based audits these days:

USE ProofOfConcept;
GO
IF OBJECT_ID(N'dbo.MyTable001') IS NOT NULL
DROP TABLE dbo.MyTable001;
IF OBJECT_ID(N'dbo.AuditLog') IS NOT NULL
DROP TABLE dbo.AuditLog;
GO
CREATE TABLE dbo.MyTable001
(ID INT IDENTITY
PRIMARY KEY,
Col1 VARCHAR(100),
Col2 VARCHAR(100));
GO
CREATE TABLE dbo.AuditLog
(ID INT IDENTITY
PRIMARY KEY,
LogTime DATETIME NOT NULL
DEFAULT (GETDATE()),
LogEntry XML);
GO
CREATE TRIGGER dbo.MyTable001_Audit ON dbo.MyTable001
FOR UPDATE, DELETE
AS
SET NOCOUNT ON;

INSERT INTO dbo.AuditLog
(LogEntry)
SELECT (SELECT 'dbo.MyTable001' AS Obj,
*
FROM DELETED
FOR
XML RAW('PriorValues'),
TYPE);
GO
INSERT INTO dbo.MyTable001
(Col1, Col2)
VALUES ('A', 'B'),
('C', 'D');
GO
SELECT *
FROM dbo.AuditLog;
GO
UPDATE dbo.MyTable001
SET Col2 = 'E'
WHERE ID = 2;
GO
SELECT *
FROM dbo.AuditLog;
GO



- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1382651
Posted Thursday, November 8, 2012 1:05 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, August 1, 2014 10:55 AM
Points: 701, Visits: 1,732
Cool thanks GSQUARED.


thanks guys!



Post #1382689
Posted Sunday, November 11, 2012 3:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 36,958, Visits: 31,464
@Gus,

Although I certainly appreciate the self-healing nature of XML to automatically capture column additions deletions to the table, full row auditing is already expensive from a storage standpoint. It seems that the bloat of XML tags would make that much worse. Considering that a table might never suffer a structure change in it's lifetime, is it really worth using XML for such a thing?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1383474
Posted Monday, November 12, 2012 6:19 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Jeff Moden (11/11/2012)
@Gus,

Although I certainly appreciate the self-healing nature of XML to automatically capture column additions deletions to the table, full row auditing is already expensive from a storage standpoint. It seems that the bloat of XML tags would make that much worse. Considering that a table might never suffer a structure change in it's lifetime, is it really worth using XML for such a thing?


If the table is static enough for it, or if you want to play around with some moderately complex DDL triggers (which can rewrite the DML trigger for you if the attached object changes), then doing the XML trigger can actually be an even better solution.

Just change the Select in the trigger to look like this:

NullIf(deleted.MyCol1, inserted.MyCol1) as Col1, NullIf(deleted.MyCol2, inserted.MyCol2) 
FROM inserted
FULL OUTER JOIN deleted
ON inserted.ID = deleted.ID

Keep the For XML, Type on there. XML defaults NULL-value columns out of the dataset completely. If you update a single column, that's the only one that goes into the audit log. If you update 2 columns, they go in. If you update everything, it all goes in. Unlike the Update() function in triggers, this method will actually correctly handle a column that's set to the same value it already has, by treating it as unchanged.

On tables that routinely get narrow updates, this usually ends up taking a lot less storage space than column-matched log tables (where the log table has the same columns as the table being logged), and is a lot faster than name-value log tables (the ones that insert ColumnName, NewValue type data into a vertical log). Also avoids the name-value log overhead on reconstituting the data, or accidentally getting two transactions crossed up.

I've tested that method on real data, and it pretty routinely works out better than any other active logging solution I've seen. Add the DDL trigger trick to it, where any column changes result in the trigger automatically being updated to match the new columns, and you have a logging system that can really have all the advantages and no real drawbacks.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1383657
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse