DDL Triggers Implementation

-----create audit tables
--Table to Store Function Changes

create table dbo.t_function
(
ID int identity (1,1)
,DataChange xml not null
,createdDate smalldatetime default getutcdate()
,ModifyDate smalldatetime default getutcdate()
,CreateBY nvarchar(80) default suser_name ()
,ModifiedBy nvarchar(80) default suser_name()
,IsDeleted bit default 'FALSE'

constraint [t_function_PK] primary Key clustered 
(
[ID] asc
)
)
go
--Create Table for table changes 
create table dbo.t_table
(
ID int identity (1,1)
,DataChange xml not null
,createdDate smalldatetime default getutcdate()
,ModifyDate smalldatetime default getutcdate()
,CreateBY nvarchar(80) default suser_name ()
,ModifiedBy nvarchar(80) default suser_name()
,IsDeleted bit default 'FALSE'

constraint [t_table_PK] primary Key clustered 
(
[ID] asc
)
)
go
---Create Table for procedure changes
create table dbo.t_procedure
(
ID int identity (1,1)
,DataChange xml not null
,createdDate smalldatetime default getutcdate()
,ModifyDate smalldatetime default getutcdate()
,CreateBY nvarchar(80) default suser_name ()
,ModifiedBy nvarchar(80) default suser_name()
,IsDeleted bit default 'FALSE'

constraint [t_procedure_PK] primary Key clustered 
(
[ID] asc

)
)
Go
---Create table for View changes

create table dbo.t_view
(
ID int identity (1,1)
,DataChange xml not null
,createdDate smalldatetime default getutcdate()
,ModifyDate smalldatetime default getutcdate()
,CreateBY nvarchar(80) default suser_name ()
,ModifiedBy nvarchar(80) default suser_name()
,IsDeleted bit default 'FALSE'

constraint [t_views_PK] primary Key clustered 
(
[ID] asc

)
)
go
--Create Table for Trigger Changes
create table dbo.t_trigger
(
ID int identity (1,1)
,DataChange xml not null
,createdDate smalldatetime default getutcdate()
,ModifyDate smalldatetime default getutcdate()
,CreateBY nvarchar(80) default suser_name ()
,ModifiedBy nvarchar(80) default suser_name()
,IsDeleted bit default 'FALSE'

constraint [t_triggers_PK] primary Key clustered 
(
[ID] asc

)
)
go
----Now Create a View to Query the Data 
---Also is a way to put it all together 

CREATE view [dbo].[V_DDLAudit]
as
SELECT 
DataChange.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(25)') as ObjectType,
DataChange.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)') as EventType,
DataChange.value('(/EVENT_INSTANCE/PostTime)[1]','smalldatetime') as ChangedDated,
DataChange.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(80)') as ObjectName,
DataChange.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(80)') as ChangedBy,
DataChange.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(15)') as DBRole,
DataChange.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as CommandText
 
from dbo.t_table
union 
SELECT 
DataChange.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(25)') as ObjectType,
DataChange.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)') as EventType,
DataChange.value('(/EVENT_INSTANCE/PostTime)[1]','smalldatetime') as ChangedDated,
DataChange.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(80)') as ObjectName,
DataChange.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(80)') as ChangedBy,
DataChange.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(15)') as DBRole,
DataChange.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as CommandText
from dbo.t_function
union
SELECT 
DataChange.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(25)') as ObjectType,
DataChange.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)') as EventType,
DataChange.value('(/EVENT_INSTANCE/PostTime)[1]','smalldatetime') as ChangedDated,
DataChange.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(80)') as ObjectName,
DataChange.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(80)') as ChangedBy,
DataChange.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(15)') as DBRole,
DataChange.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as CommandText
from dbo.t_procedure
union
SELECT 
DataChange.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(25)') as ObjectType,
DataChange.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)') as EventType,
DataChange.value('(/EVENT_INSTANCE/PostTime)[1]','smalldatetime') as ChangedDated,
DataChange.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(80)') as ObjectName,
DataChange.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(80)') as ChangedBy,
DataChange.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(15)') as DBRole,
DataChange.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as CommandText
from dbo.t_view
union
SELECT 
DataChange.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(25)') as ObjectType,
DataChange.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)') as EventType,
DataChange.value('(/EVENT_INSTANCE/PostTime)[1]','smalldatetime') as ChangedDated,
DataChange.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(80)') as ObjectName,
DataChange.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(80)') as ChangedBy,
DataChange.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(15)') as DBRole,
DataChange.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as CommandText
from dbo.t_trigger

---Finaly Create DDL Triggers 

----Create Triggers 
create trigger trig_function_DDL 
on database 
for 
create_function,drop_function,alter_function
as
INSERT INTO [Sch_AuditDdl].[t_function]
 ([DataChange]

 )
 VALUES
 (eventdata())
go
create trigger trig_procs_DDL 
on database 
for 
create_procedure,drop_procedure,alter_procedure
as
INSERT INTO [Sch_AuditDdl].[t_procedure]
 ([DataChange]

 )
 VALUES
 (eventdata())
go
create trigger trig_table_DDL 
on database 
for 
create_table,drop_table,alter_table
as
INSERT INTO [Sch_AuditDdl].[t_table]
 ([DataChange]

 )
 VALUES
 (eventdata())
go
create trigger trig_views_DDL 
on database 
for 
create_view,drop_view,alter_view
as
INSERT INTO [Sch_AuditDdl].[t_view]
 ([DataChange]

 )
 VALUES
 (eventdata())
go
create trigger trig_triggers_DDL
on database
for
create_trigger,drop_trigger,alter_trigger
as
INSERT INTO [Sch_AuditDdl].[t_trigger]
 ([DataChange]

 )
 VALUES
 (eventdata())
go