"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
AFTER INSERT, UPDATE, DELETE
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
SELECT [AuditType] = CASE WHEN I.MyPK IS NULL THEN 'D' ELSE 'U' END,
[AuditDate] = GetDate(),
FROM deleted D
LEFT OUTER JOIN inserted I
ON I.MyPK = D.MyPK
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 ...
Then create a DDL trigger
CREATE TRIGGER MyDDLTrigger
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE
INSERT INTO MyDDLAudit
or this example for SQL Server Books Online:
CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100),
Event nvarchar(100), TSQL nvarchar(2000));
CREATE TRIGGER log
DECLARE @data XML
SET @data = EVENTDATA()
(PostTime, DB_User, Event, TSQL)
@data.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(2000)') ) ;
--Test the trigger.
CREATE TABLE TestTable (a int)
DROP TABLE TestTable ;
SELECT * FROM ddl_log ;
--Drop the trigger.
DROP TRIGGER log
--Drop table ddl_log.
DROP TABLE ddl_log
Oct 29, 2009 at 09:59 AM