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
