阻止意外UPATE或DELETE某张表的所有行的数据

意外地UPATE或DELETE某张表的所有行的数据，会给我们带来很大的麻烦，这点不说你也明白。于是，有效地阻止这种情况的发生，对DBA来说，是项很有意义的事。在网上看到一篇文章，给我一些启示，为了应用方便，我写了一个存储过程，通过指定表名和操作类型（UPDATE, DELETE or BOTH）即可快速对指定的表建立触发器。

相关Script如下：

USE [TESTDB]
GO

/****** Object:  StoredProcedure [dbo].[usp_WHERE_Restriction]    Script Date: 2013/8/16 14:56:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ======================================================
-- Author:		Burgess
-- Create date: 2013/8/16
-- Description:	阻止没有WHERE条件的UPDATE,DELETE or BOTH
-- ======================================================
CREATE PROCEDURE [dbo].[usp_WHERE_Restriction] 
@TBLNAME VARCHAR(50),
@TYPE VARCHAR(20)
AS
BEGIN
	DECLARE @TRGNAME NVARCHAR(50)
	DECLARE @STRTRGTEXT NVARCHAR(MAX)=N''
	DECLARE @ERRALERT VARCHAR(50)
	DECLARE @SEVERITY NVARCHAR(5)='16'
	DECLARE @STATE NVARCHAR (5)='1'

	IF @TYPE NOT IN('UPDATE','DELETE','BOTH')
	BEGIN
		PRINT 'Type error, choose from UPDATE,DELETE or BOTH'
		RETURN;
	END

	SET @TBLNAME = SUBSTRING(@TBLNAME,CHARINDEX('.',@TBLNAME)+1, LEN(@TBLNAME))
	IF @TYPE='BOTH' SET @TYPE='UPDATE,DELETE'
	SET @ERRALERT='Cannot '+@TYPE+' all rows. Use WHERE CONDITION'
	SET	@TRGNAME = REPLACE('[dbo].[trg_'+@TYPE+'_'+ @TBLNAME +']',',','');

	IF  Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@TRGNAME))
	BEGIN
		SET @STRTRGTEXT += '/*-- ============================================================='
		SET @STRTRGTEXT += CHAR(13) + '-- Author      : Burgess Liu'
		SET @STRTRGTEXT += CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
		SET @STRTRGTEXT += CHAR(13) + N'-- Description : 阻止对所有行进行UPDATE,DELETE or BOTH 操作'
		SET @STRTRGTEXT += CHAR(13) + '-- ============================================================= */'

		SET @STRTRGTEXT+= CHAR(13) + 'CREATE TRIGGER'+@TRGNAME
		SET @STRTRGTEXT+= CHAR(13) + 'ON '+@TBLNAME
		SET @STRTRGTEXT+= CHAR(13) + 'FOR '+@TYPE +' AS'
		SET @STRTRGTEXT+= CHAR(13) + 'BEGIN'
		SET @STRTRGTEXT+= CHAR(13) + CHAR(9)+'IF @@ROWCOUNT >= (SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE OBJECT_ID = OBJECT_ID(''' + @TBLNAME + ''') AND index_id=1)'
		SET @STRTRGTEXT+= CHAR(13) + CHAR(9)+'BEGIN'
		SET @STRTRGTEXT+= CHAR(13) + CHAR(9)+ CHAR(9)+'RAISERROR('''+ @ERRALERT + ''',' + @SEVERITY +',' + @STATE +')'
		SET @STRTRGTEXT+= CHAR(13) + CHAR(9)+ CHAR(9)+'ROLLBACK TRANSACTION'
		SET @STRTRGTEXT+= CHAR(13) + CHAR(9)+ CHAR(9)+'RETURN;'
		SET @STRTRGTEXT+= CHAR(13) + CHAR(9)+'END'
		SET @STRTRGTEXT+= CHAR(13) + 'END'
		EXEC(@STRTRGTEXT);
		IF (@@ERROR=0) 
			PRINT 'Trigger ' + @TRGNAME + N' 创建成功 '
	END
	ELSE
	BEGIN
		PRINT  @TRGNAME + N' 已经存在... '
	END
END

GO

PS：不过我测试发现，针对DELETE不能达到预期目的（不能删除数据，不过有没有限制条件），测试的版本是SQL Server 2012.