SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Audit Triggers


Audit Triggers

Author
Message
krypto69
krypto69
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1210 Visits: 2444
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?



Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18169 Visits: 20392
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
krypto69
krypto69
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1210 Visits: 2444
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




Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18169 Visits: 20392
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23273 Visits: 9730
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
krypto69
krypto69
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1210 Visits: 2444
Cool thanks GSQUARED.


thanks guys!



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84677 Visits: 41069
@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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23273 Visits: 9730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search