InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 07:24:11 GMT20http://www.sqlservercentral.com/Forums/Topic1382604-338-1.aspx[quote][b]Jeff Moden (11/11/2012)[/b][hr]@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?[/quote]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:[code="sql"]NullIf(deleted.MyCol1, inserted.MyCol1) as Col1, NullIf(deleted.MyCol2, inserted.MyCol2) FROM insertedFULL OUTER JOIN deleted ON inserted.ID = deleted.ID[/code]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.Mon, 12 Nov 2012 06:19:46 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1382604-338-1.aspx@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?Sun, 11 Nov 2012 15:45:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1382604-338-1.aspxCool thanks GSQUARED.thanks guys!Thu, 08 Nov 2012 13:05:44 GMTkrypto69http://www.sqlservercentral.com/Forums/Topic1382604-338-1.aspxHere are some articles I wrote a while back on data auditing:[url]http://www.sqlservercentral.com/articles/Auditing/63247/[/url][url]http://www.sqlservercentral.com/articles/Auditing/63248/[/url]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:[code="sql"]USE ProofOfConcept;GOIF 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;GOCREATE TABLE dbo.MyTable001 (ID INT IDENTITY PRIMARY KEY, Col1 VARCHAR(100), Col2 VARCHAR(100));GOCREATE TABLE dbo.AuditLog (ID INT IDENTITY PRIMARY KEY, LogTime DATETIME NOT NULL DEFAULT (GETDATE()), LogEntry XML);GOCREATE TRIGGER dbo.MyTable001_Audit ON dbo.MyTable001 FOR UPDATE, DELETEAS SET NOCOUNT ON; INSERT INTO dbo.AuditLog (LogEntry) SELECT (SELECT 'dbo.MyTable001' AS Obj, * FROM DELETED FOR XML RAW('PriorValues'), TYPE);GOINSERT INTO dbo.MyTable001 (Col1, Col2)VALUES ('A', 'B'), ('C', 'D');GOSELECT *FROM dbo.AuditLog;GOUPDATE dbo.MyTable001SET Col2 = 'E'WHERE ID = 2;GOSELECT *FROM dbo.AuditLog;GO[/code]Thu, 08 Nov 2012 11:15:42 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1382604-338-1.aspx[quote][b]krypto69 (11/8/2012)[/b][hr]Thanks Phil..This is what I have so far...Is this going to copy only the changed (updated/inserted) record?[quote]CREATE TRIGGER update_delete ON dbo.WSI_T_CUSTOM_BENFIT_RATESFOR INSERT, UPDATEASUPDATE WSI_T_CUSTOM_BENFIT_RATES_HISTFROM WSI_T_CUSTOM_BENFIT_RATES[/quote][/quote]No. Did you read the link I sent?Thu, 08 Nov 2012 10:54:29 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1382604-338-1.aspxThanks Phil..This is what I have so far...Is this going to copy only the changed (updated/inserted) record?[quote]CREATE TRIGGER update_delete ON dbo.WSI_T_CUSTOM_BENFIT_RATESFOR INSERT, UPDATEASUPDATE WSI_T_CUSTOM_BENFIT_RATES_HISTFROM WSI_T_CUSTOM_BENFIT_RATES[/quote]Thu, 08 Nov 2012 10:35:43 GMTkrypto69http://www.sqlservercentral.com/Forums/Topic1382604-338-1.aspxI did a quick search and found [url=http://www.sqlservercentral.com/articles/Triggers/auditingtriggers/579/]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.Thu, 08 Nov 2012 10:20:37 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1382604-338-1.aspxHi,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?Thu, 08 Nov 2012 10:04:10 GMTkrypto69