I need to implement metadata for millions of record changes in sql server.what is the best way to implement the metadata.I need even to store the old and new values, even the object type.
Answer by Kristen ·
"Millions of record changes" on DDL sounds a bit worrying?
It doesn't look like you mean DML, but this is what I do for DML:
I use a Trigger which stores the "Before" version of the record in a separate "Archive" table. I have seen other people store the "new" version in the archive table instead.
My view is that storing the New version means the archive table has a complete set of data, but it is duplicating the record currently in the Main table, which seems to me to be a waste of space - it immeditely doubles the size of the database before you have even changed any records :)
Here's an example of my Trigger:
CREATE TRIGGER dbo.MyTrigger ON dbo.MyTable AFTER INSERT, UPDATE, DELETE AS SET NOCOUNT ON SET XACT_ABORT ON SET ARITHABORT ON INSERT dbo.MyAuditTable SELECT [AuditType] = CASE WHEN I.MyPK IS NULL THEN 'D' ELSE 'U' END, [AuditDate] = GetDate(), D.* FROM deleted D LEFT OUTER JOIN inserted I ON I.MyPK = D.MyPK GO
For DDL you can do something similar in SQL2005 onwards along the lines of:
First create a table to hold the audit data:
CREATE TABLE MyDDLAudit ( EventID int identity NOT NULL, -- ... You probably also want a Date/Time column ... EventData xml ) GO
Then create a DDL trigger
USE MyDatabase GO CREATE TRIGGER MyDDLTrigger ON DATABASE FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE AS INSERT INTO MyDDLAudit ( [EventData] ) VALUES ( eventdata() ) GO
or this example for SQL Server Books Online:
USE AdventureWorks; GO CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000)); GO CREATE TRIGGER log ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @data XML SET @data = EVENTDATA() INSERT ddl_log (PostTime, DB_User, Event, TSQL) VALUES (GETDATE(), CONVERT(nvarchar(100), CURRENT_USER), @data.value('(/EVENT_INSTANCE/EventType)', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(2000)') ) ; GO --Test the trigger. CREATE TABLE TestTable (a int) DROP TABLE TestTable ; GO SELECT * FROM ddl_log ; GO --Drop the trigger. DROP TRIGGER log ON DATABASE GO --Drop table ddl_log. DROP TABLE ddl_log GO