DDL Triggers Implementation

Lately I have been bug by QA about developers doing changes to the database objects using store procedures or logging using the application account which is getting very annoying, eventually I search for some old post that I made is SQLServerCentral 

and found the below code...Now lets the hunt begin..Developers will be developers so if it was up to them we would be doing changes everywhere.

-----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