Trigger : logs in table and email whenever a database is created or dropped and a object is created altered or dropped

the below script with steps included will create databses , tables and triggers under server and test databse
the AuditNewDatabaseCreation1 will emails and logs details in table whene ever a databse is created or dropped and Trigger:AuditObjects1 will be created in test database  and when ever a object is created , altered, dropped , it lohs information and emails


-----Step1: Create Database: AuditDatabase

-----Step2:Create table as below

USE [AuditDatabase]
 CREATE TABLE [dbo].[DatabaseCreateDropTracking](
    [TrackingId] [int] IDENTITY(1,1) NOT NULL,
    [TrackingDate] [datetime] NOT NULL,
    [DatabaseName] [varchar](100) NULL,
    [EventType] [varchar](100) NULL,
    [LoginName] [varchar](100) NULL,
    [InstanceName] [varchar](100) NULL,
    [SQLCommand] [nvarchar](max) NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[DatabaseCreateDropTracking] ADD  CONSTRAINT [DF_DatabaseCreateDropTracking_TrackingDate]  DEFAULT (getdate()) FOR [TrackingDate]
GO

-----Step3:create table
USE [AuditDatabase]
--CREATE TABLE [dbo].[ObjectTracking](
    [TrackingId] [bigint] IDENTITY(1,1) NOT NULL,
    [TrackingDate] [datetime] NOT NULL,
    [DatabaseName] [varchar](500) NULL,
    [EventType] [varchar](500) NULL,
    [ObjectName] [varchar](500) NULL,
    [ObjectType] [varchar](500) NULL,
    [LoginName] [varchar](500) NULL,
    [HostName] [varchar](500) NULL,
    [SqlCommand] [nvarchar](max) NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[ObjectTracking] ADD  DEFAULT (getdate()) FOR [TrackingDate]
GO

------Step4:THIS  below TRIGGERS EMAILS when a database is created or dropped  and keep track in tables in AuditDatabase   database

/****** Object:  DdlTrigger [AuditNewDatabaseCreation1]    Script Date: 03/14/2013 09:01:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [AuditNewDatabaseCreation1]
ON ALL SERVER
FOR
-- DDL_DATABASE_LEVEL_EVENTS
-- Defining all the DDL database events on which we need track
CREATE_DATABASE, DROP_DATABASE
AS
BEGIN
--DECLARE @dbname varchar(500)
DECLARE @data XML
DECLARE @InstanceName varchar(100)
Declare @tsql varchar(max)
DECLARE @bd varchar(max)

--SET @dbname = DB_NAME()
SET @data = EVENTDATA()
SET @InstanceName = HOST_NAME()

INSERT INTO AuditDatabase.dbo.DatabaseCreateDropTracking(DatabaseName,EventType,LoginName,InstanceName,SqlCommand)
VALUES(
--@dbname,
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(100)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(100)'),
@InstanceName,
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
)
 
Set @tsql = EVENTDATA().value
        ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)')
SET @bd = 'UserName: ' + UPPER(SUSER_NAME()) + '

           ServerName: ' + @@SERVERNAME + '

           Time: '    + CONVERT(varchar(25),Getdate()) + '

           HostName: ' + HOST_NAME() + '

           Database: ' + db_name() + '

           T-SQL: ' +  @tsql
           
IF @bd LIKE '%drop%'
--;astratto@chesapeakebay.net

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Database Mail ',
                             @recipients = 'xyz@abc.com',                      
                             @subject = 'A new database has been dropped ',
                             @body_format = 'HTML',
                             @importance = 'High',
                             @body = @bd
               

ELSE
--;astratto@chesapeakebay.net
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Database Mail',
                             @recipients = 'xyz@abc.com',                      
                             @subject = 'A new database has been created ',
                             @body_format = 'HTML',
                             @importance = 'High',
                             @body = @bd
               

END
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [AuditNewDatabaseCreation1] ON ALL SERVER
GO

----STEP6:
CREATE DATABASE TEST
GO


USE [test]
GO

 CREATE  TRIGGER [AuditObjects1]
ON DATABASE
FOR
-- DDL_DATABASE_LEVEL_EVENTS
-- Defining all the DDL database events on which we need track
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_VIEW,ALTER_VIEW,DROP_VIEW,
CREATE_INDEX,ALTER_INDEX,DROP_INDEX
AS

BEGIN



DECLARE @data XML
DECLARE @HostName varchar(500)
SET @data = EVENTDATA()
SET @HostName = HOST_NAME()
Declare @tsql varchar(max)
DECLARE @bd varchar(max)



INSERT INTO AuditDatabase.dbo.ObjectTracking(DatabaseName,EventType,ObjectName,ObjectType,LoginName,HostName,SqlCommand)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(500)'),
@HostName,
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
)

Set @tsql = EVENTDATA().value
        ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)')
       
SET @bd = 'UserName: ' + UPPER(SUSER_NAME()) + '

           ServerName: ' + @@SERVERNAME + '

           Time: '    + CONVERT(varchar(25),Getdate()) + '

           HostName: ' + HOST_NAME() + '

           Database: ' + db_name() + '

           T-SQL: ' +  @tsql
           

--PRINT 'Database has been created or dropped '
           
IF @bd LIKE '%CREATE%'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'CBPO Database Mail Admins',
                             @recipients = 'xyz@abc.com',
                             @subject = 'An Object is created',
                             @body_format = 'HTML',
                             @importance = 'High',
                             @body = @bd
                           
   IF @bd LIKE '%ALTER%'
   EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Database Mail',
                             @recipients = 'xyz@abc.com',
                             @subject = 'An Object is altered  ',
                             @body_format = 'HTML',
                             @importance = 'High',
                             @body = @bd
                           
 
 
   IF @bd LIKE '%DROP%'    
      EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Database Mail',
                             @recipients = 'xyz@abc.com',
                             @subject = 'An Object is Dropped ',
                             @body_format = 'HTML',
                             @importance = 'High',
                             @body = @bd      
END


GO



ENABLE TRIGGER [AuditObjects1] ON DATABASE
GO