CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE AS ROLLBACK ;
CREATE TABLE TRI_TEST1(ID INT) CREATE TABLE TRI_TEST2(ID INT) CREATE TRIGGER TRI_TEST1 ON DATABASE FOR DDL_TABLE_EVENTS AS DECLARE @EventData xml SET @EventData=EVENTDATA()IF @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(50)')='TABLE' AND @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(50)') ='TRI_TEST1' BEGIN ROLLBACK END GODROP TABLE TRI_TEST1 /* 消息 3609,级别 16,状态 2,第 1 行 The transaction ended in the trigger. The batch has been aborted. */ DROP TABLE TRI_TEST2/* 命令已成功完成。 */DROP TRIGGER TRI_TEST1 ON Database /* 命令已成功完成。 */DROP TABLE TRI_TEST1 /* 命令已成功完成。 */
if object_id('tb') is not null drop table tb create table tb(id varchar(20), pid varchar(20), DName varchar(20)) go IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'Protect') DROP TRIGGER Protect ON DATABASE; GO create trigger Protect on DATABASE FOR DROP_TABLE AS BEGIN print '不可删除表' rollback ENDdrop table tb IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'Protect') DROP TRIGGER Protect ON DATABASE; /* 不可删除表 消息 3609,级别 16,状态 2,第 1 行 事务在触发器中结束。批处理已中止。 */
create table a(id int,name varchar(10)) go--创建DDL触发器 create trigger tri_wsp on database for drop_table as declare @tablename varchar(50),@sql varchar(200) set @tablename=eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(50)') if(@tablename='a') begin rollback end godrop table a
ON DATABASE
FOR DROP_TABLE
AS
ROLLBACK
;
CREATE TABLE TRI_TEST1(ID INT)
CREATE TABLE TRI_TEST2(ID INT)
CREATE TRIGGER TRI_TEST1
ON DATABASE
FOR DDL_TABLE_EVENTS
AS
DECLARE @EventData xml
SET @EventData=EVENTDATA()IF @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(50)')='TABLE'
AND @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(50)') ='TRI_TEST1'
BEGIN
ROLLBACK
END
GODROP TABLE TRI_TEST1
/*
消息 3609,级别 16,状态 2,第 1 行
The transaction ended in the trigger. The batch has been aborted.
*/
DROP TABLE TRI_TEST2/*
命令已成功完成。
*/DROP TRIGGER TRI_TEST1 ON Database
/*
命令已成功完成。
*/DROP TABLE TRI_TEST1
/*
命令已成功完成。
*/
drop table tb
create table tb(id varchar(20), pid varchar(20), DName varchar(20))
go
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = 'Protect')
DROP TRIGGER Protect
ON DATABASE;
GO
create trigger Protect
on DATABASE
FOR DROP_TABLE
AS
BEGIN
print '不可删除表'
rollback
ENDdrop table tb
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = 'Protect')
DROP TRIGGER Protect
ON DATABASE;
/*
不可删除表
消息 3609,级别 16,状态 2,第 1 行
事务在触发器中结束。批处理已中止。
*/
create table a(id int,name varchar(10))
go--创建DDL触发器
create trigger tri_wsp on database for drop_table
as
declare @tablename varchar(50),@sql varchar(200)
set @tablename=eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(50)')
if(@tablename='a')
begin
rollback
end
godrop table a