22,210
社区成员
发帖
与我相关
我的任务
分享
USE test
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'tri_avoidDrop')
DROP TRIGGER tri_avoidDrop ON DATABASE
GO
CREATE TRIGGER tri_avoidDrop
ON DATABASE
FOR DROP_TABLE
AS
--指定某些表不能被drop
if eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)') in ('tb','ta','temp')
begin
PRINT '此表内有数据嘿,别jb乱删!'
ROLLBACK
end
GO
--测试:
create table tb(id int)
create table tc(id int)
drop table tb
/*
表内有数据嘿,别jb乱删!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
drop table tc
/*
Command(s) completed successfully.
*/
DROP TRIGGER tri_avoidDrop ON DATABASE
GO
USE test
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'tri_avoidDrop')
DROP TRIGGER tri_avoidDrop ON DATABASE
GO
CREATE TRIGGER tri_avoidDrop
ON DATABASE
FOR DROP_TABLE
AS
PRINT '表内有数据嘿,别jb乱删!'
ROLLBACK
GO
--测试:
create table tb(id int)
drop table tb
/*
表内有数据嘿,别jb乱删!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
DROP TRIGGER tri_avoidDrop ON DATABASE
GO
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK