CREATE TABLE ddl_hist(
	id int IDENTITY(1,1) NOT NULL,
	dt datetime NULL DEFAULT getdate(),
	user_ varchar(40) NULL DEFAULT suser_sname(),
	event_ varchar(100) NULL,
	tsql_ varchar(max) NULL,
 CONSTRAINT PK_ddl_hist PRIMARY KEY CLUSTERED(id))
 
create trigger [tg_ddl_hist]
on database
--with encryption
for ddl_database_level_events
as
set nocount on;
declare @d xml;
set @d = EVENTDATA();
insert into ddl_hist(event_, tsql_) 
values ( @d.value('(EVENT_INSTANCE/EventType)[1]','varchar(100)'),
         @d.value('(EVENT_INSTANCE/TSQLCommand)[1]','varchar(max)'))

create trigger dbo.ddl_hist_ins
on dbo.ddl_hist
instead of insert as

begin try
	if TRIGGER_NESTLEVEL ( ( select object_id from sys.triggers
	where name = 'tg_ddl_hist' ), 'AFTER' , 'DDL' ) = 1
		insert into dbo.ddl_hist(event_, tsql_)
		select event_, tsql_ from inserted
end try
begin catch
	if @@trancount > 0
	rollback transaction
end catch	

 CREATE TABLE ddl_hist(
	id int IDENTITY(1,1) NOT NULL,
	dt datetime NULL DEFAULT getdate(),
	user_ varchar(40) NULL DEFAULT suser_sname(),
	event_ varchar(100) NULL,
	tsql_ varchar(max) NULL,
	o_o int NULL DEFAULT 0,
 CONSTRAINT PK_ddl_hist PRIMARY KEY CLUSTERED(id))
 
create trigger [tg_ddl_hist]
on database
--with encryption
for ddl_database_level_events
as
set nocount on;
declare @d xml;
set @d = EVENTDATA();
insert into ddl_hist(event_, tsql_, o_o) 
values ( @d.value('(EVENT_INSTANCE/EventType)[1]','varchar(100)'),
         @d.value('(EVENT_INSTANCE/TSQLCommand)[1]','varchar(max)'),
         @@procid)
         

create trigger dbo.ddl_hist_ins
on dbo.ddl_hist
instead of insert as
set nocount on;
begin try
	if (select object_id 
	   from sys.triggers
	   where name = 'tg_ddl_hist')  = (select o_o from inserted)
		insert into dbo.ddl_hist(event_, tsql_)
		select event_, tsql_ from inserted
end try
begin catch
	if @@trancount > 0
	rollback transaction
end catch