复制代码 代码如下:
— create a log table
create table triggerlog (loginfo xml)
— create a dummy table to delete later on
create table tabletodelete (id int primary key)
— 新建一个新表，作为删除实验表
insert into tabletodelete values(1)
go
— 创建一个drop table 的 ddl
create trigger stoptabledrop on database after drop_table
as
declare @eventdata as xml
set @eventdata = eventdata() — 必须要在rollback之前截获ddl信息
rollback
print ‘drop table attempt in database ‘ + db_name() + ‘.’
insert into triggerlog values(@eventdata)
执行如下删除操作：
drop table tabletodelete
会触发上面的ddl，从而回滚操作。
执行下面的sql：
select * from triggerlog
查看刚才截获的ddl信息。