DDL Trigger

create TRIGGER indexTrigger
ON ALL SERVER
--FOR DROP_INDEX ,ALTER_INDEX,create_index
FOR DROP_INDEX
AS
declare @original_login sysname ,@login_name sysname,@user_name sysname,@eventtype nvarchar(100),@commandText nvarchar(max),@dbname sysname
select
@original_login=ORIGINAL_LOGIN(),
@login_name=SUSER_NAME(),
@user_name=CURRENT_USER,
@dbname=EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname') ,
@eventtype= EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') ,
@commandText= EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

if not exists(select 1 from tempdb.sys.tables where name='indexTrigger')
begin
    create table tempdb.DBO.indexTrigger(
        original_login sysname ,
        login_name sysname,
        user_name sysname,
        eventtype nvarchar(100),
        commandText nvarchar(max),
        dbname sysname,
        createtime datetime default getdate()
    )
end
insert tempdb.dbo.indexTrigger([original_login],[login_name],[user_name],[eventtype],[commandText],dbname)
select @original_login,@login_name,@user_name,@eventtype,@commandText,@dbname

 

EVENTDATA (Transact-SQL)