Tables Change Auditing

/*
This is used to track database table changes and include the ability to email when tables are altered or dropped. The is accomplished with a DDL trigger. It assumes there is a table in an auditing database to hold the rollback information and a database mail default account.
*/ 


IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'Event_Table_changes')
DISABLE TRIGGER [Event_Table_changes] ON DATABASE

GO

USE [Name of Database]
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'Event_Table_changes')DROP TRIGGER [Event_Table_changes] ON DATABASE
GO

USE [Manuals]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE trigger [Event_Table_changes]
on database
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
DECLARE @FullFile xml
DECLARE @EventType nvarchar(25)
DECLARE @EventTime datetime
DECLARE @LoginName nvarchar(50)
DECLARE @UserName nvarchar(25)
DECLARE @ObjectName nvarchar(50)
DECLARE @ObjectType nvarchar(25)
DECLARE @Command nvarchar(max)
SET @FullFile = eventdata()
SET @EventType = @FullFile.value('(//EVENT_INSTANCE/EventType)[1]', 'nvarchar(25)')
SET @EventTime = @FullFile.value('(//EVENT_INSTANCE/PostTime)[1]', 'DATETIME')
SET @LoginName = @FullFile.value('(//EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)')
SET @UserName = @FullFile.value('(//EVENT_INSTANCE/UserName)[1]', 'nvarchar(25)')
SET @ObjectName = @FullFile.value('(//EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(50)')
SET @ObjectType = @FullFile.value('(//EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(25)')
SET @Command = @FullFile.value('(//EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
--print @command


INSERT INTO [AuditDB].dbo.Table_Events
           ([EventType]
           ,[EventTime]
           ,[LoginName]
           ,[UserName]
           ,[ObjectName]
           ,[ObjectType]
           ,[Command]
           ,[FullFile])
SELECT @EventType
, @EventTime
, @LoginName
, @UserName
, @ObjectName
, @ObjectType
, @Command
, @FullFile

--if table drop email admin group
IF @EventType = 'DROP_TABLE'
BEGIN
     DECLARE @profile varchar(100)
     DECLARE @EmailTo varchar(128)
     DECLARE @Subject varchar(128)
     DECLARE @Body varchar(max)
     DECLARE @BodyType varchar(25)


     SELECT @profile = CAST(COALESCE(SERVERPROPERTY ( 'InstanceName'  ), SERVERPROPERTY ( 'ServerName')) AS varchar(80)) + 'Admin'
     SET @EmailTo = [Add email name]
     SET @Subject = CAST(COALESCE(SERVERPROPERTY ( 'InstanceName'  ), SERVERPROPERTY ( 'ServerName')) AS varchar(80)) +  ' Table has been changed in DB: ' + db_name()
     SET @Body = @Command
     SET @BodyType = 'HTML'

     EXEC msdb.dbo.sp_send_dbmail
     @profile_name = @profile,
     @recipients = @EmailTo ,
     @subject = @Subject,
     @body = @Body ,
     @body_format  = @BodyType
END

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [Event_Table_changes] ON DATABASE
GO

ENABLE TRIGGER [Event_Table_changes] ON DATABASE
GO