November 8, 2012 at 10:04 am
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?
November 8, 2012 at 10:20 am
I did a quick search and found this[/url]. Looks good enough to point you in the right direction, but there are loads of easy-to-find resources out there courtesy of Google.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 8, 2012 at 10:35 am
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
November 8, 2012 at 10:54 am
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 8, 2012 at 11:15 am
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
November 8, 2012 at 1:05 pm
Cool thanks GSQUARED.
thanks guys!
November 11, 2012 at 3:45 pm
@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
Change is inevitable... Change for the better is not.
November 12, 2012 at 6:19 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply