SQL Server初始化表：删除数据及主键复位（二）

上一篇用的是临时表，执行之后特殊应用都会消失。后面有讲到SQL Server本身只怎么操作的，就模拟了一个——添加触发器特殊应用。

 

1、创建S_ENTITY表和insert测试数据上一篇有这一篇就不写了。

 

2、创建Table2表并添加测试触发器：

--创建表：
CREATE TABLE [dbo].[table2_2](
	[field1] [int] IDENTITY(1,1) NOT NULL,
	[field2] [int] NULL,
	[field3] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
	[field4] [varchar](300) COLLATE Chinese_PRC_CI_AS NULL,
	[field10] [int] NULL,
	[field5] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
	[field6] [decimal](30, 2) NULL,
	[field7] [decimal](30, 2) NULL,
	[field8] [decimal](30, 2) NULL,
	[field9] [decimal](20, 3) NULL,
 CONSTRAINT [PK_table2_2] PRIMARY KEY CLUSTERED 
(
	[field1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

--添加测试触发器
-- =============================================
-- Author:		<wjl>
-- Create date: <2015-5-13>
-- Description:	<测试触发器>
-- =============================================
ALTER TRIGGER [TestTrigger] 
   ON  [dbo].[table2]
   AFTER DELETE
AS 
BEGIN
	
	SET NOCOUNT ON;
	
	print '****************************************************'
END

 

3、先查看一下SQL Server对于表中插入一列的处理过程：在table2添加一列field10，在保存之前“生成更改脚本”，以下为其脚本信息：

/*
   2015年5月13日11:01:49
   用户: sa
   服务器: 192.168.0.110
   数据库: ac
   应用程序: 
*/

/* 为了防止任何可能出现的数据丢失问题，您应该先仔细检查此脚本，然后再在数据库设计器的上下文之外运行此脚本。*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
--1、创建表
CREATE TABLE dbo.Tmp_table2
	(
	field1 int NOT NULL IDENTITY (1, 1),
	field2 int NULL,
	field3 varchar(100) NULL,
	field4 varchar(300) NULL,
	field10 int NULL,
	field5 varchar(200) NULL,
	field11 int NULL,
	field6 decimal(30, 2) NULL,
	field7 decimal(30, 2) NULL,
	field8 decimal(30, 2) NULL,
	field9 decimal(20, 3) NULL
	)  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_table2 ON
GO
--2、insert表数据
IF EXISTS(SELECT * FROM dbo.table2)
	 EXEC('INSERT INTO dbo.Tmp_table2 (field1, field2, field3, field4, field10, field5, field6, field7, field8, field9)
		SELECT field1, field2, field3, field4, field10, field5, field6, field7, field8, field9 FROM dbo.table2 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_table2 OFF
GO
--3、删除原始表
DROP TABLE dbo.table2
GO
--4、重命名
EXECUTE sp_rename N'dbo.Tmp_table2', N'table2', 'OBJECT' 
GO
--5、添加主键信息
ALTER TABLE dbo.table2 ADD CONSTRAINT
	PK_table2 PRIMARY KEY CLUSTERED 
	(
	field1
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
--6、添加触发器信息
-- =============================================
-- Author:		<wjl>
-- Create date: <2015-5-13>
-- Description:	<测试触发器>
-- =============================================
CREATE TRIGGER TestTrigger 
   ON  dbo.table2
   AFTER DELETE
AS 
BEGIN
	
	SET NOCOUNT ON;
	
	print '****************************************************'
END
GO
COMMIT

 以上1234的注释为个人所加，生成脚本时并没有这些。

 

 4、个人模拟：

-- =============================================
-- Author:		<wjl>
-- Create date: <2015-5-13>
-- Description:	<初始化业务表中的数据，包括删除业务表数据并复位主键>
-- =============================================
ALTER PROCEDURE [dbo].[initData2] 
	
AS
BEGIN
	SET NOCOUNT ON;

	--1、从实体表中获取业务数据表：以table开头
	declare @count int;--记录符合条件的表的数量
	select @count = count(S_ID) from S_ENTITY where ENTITYNAME like '%table%' 
	print @count;
	if(@count>0)--说明有符合条件的table
	begin
		declare @c int;
		declare @tableName varchar(100);--表名
		declare @tempTableName varchar(100);--临时物理表名称
		declare @PKColumn varchar(50);--主键所在列名称
		declare @sql varchar(200);--拼接的SQL语句
		declare @triggerText varchar(4000);--触发器定义信息
		set @c = 1;
		while(@c<=@count)--依次循环各个表
			begin
				--获取表名称 
				select @tableName = ENTITYNAME from (
				        select ENTITYNAME,row_number() over(order by S_ID) as fn from S_ENTITY 
						where ENTITYNAME like '%table%') 
				as a where fn=@c;
				print @tableName;
				
				--2、将表结构复制到临时物理表中
				set @tempTableName = 'TEMP_TAB_'+@tableName;
				print @tempTableName;
				--判断临时物理表是否存在
				if object_id(N''+@tempTableName+'',N'U') is not null
				begin
					print '临时物理表存在';
					set @sql = 'drop table '+@tempTableName+';'--若存在则删除
					exec(@sql);
				end
				--判断原来的物理表是否存在
				if object_id(N''+@tableName+'',N'U') is not null
				print '物理表存在'
				else
				begin
				print @tableName;
				print '物理表不存在'
				set @c=@c+1;--循环数加1，否则会死循环
				continue;--不存在跳出当前循环，进行下一个循环
				end

				set @sql = 'SELECT * INTO '+@tempTableName+' FROM '+@tableName+ ' where 1=2';--只复制结构，不复制数据
				print @sql;
				exec(@sql);
				
				--3、获取主键所在列：			
				SELECT @PKColumn=COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@tableName; 
				print @PKColumn;
				
				--4、获取当前表的触发器信息
				select @triggerText = c.text  
				FROM sysobjects a  
					INNER JOIN sys.triggers b  
						ON b.object_id=a.id  
					INNER JOIN syscomments c  
						ON c.id=a.id  
				WHERE a.xtype='tr' and (a.parent_obj=object_id(@tableName));
				
				--5、删除原有表
				set @sql = 'drop table '+@tableName+';'
				print @sql;
				exec(@sql);
		
				--5、将临时物理表更名为原来物理表名称
				set @sql = 'EXECUTE sp_rename N'''+@tempTableName+''', N'''+@tableName+''', ''OBJECT'' ';
				print @sql;
				exec(@sql);
			
				--6、为新表添加主键
				set @sql ='alter table '+@tableName+' Add  Constraint  PK_'+@tableName+'  Primary  Key ('+@PKColumn+')';
				print @sql;
				exec(@sql);--执行SQL

				--7、为新表添加触发器信息
				if(@triggerText is not null and @triggerText<>'')--说明有触发器信息
				begin
				print @triggerText;
				exec(@triggerText);--执行触发器信息

				end
			set @c=@c+1;
			end
	end

END

 

 5、以物理表table2_2进行测试，执行initData2存储过程之后控制台输出：

1
table2_2
TEMP_TAB_table2_2
物理表存在
SELECT * INTO TEMP_TAB_table2_2 FROM table2_2 where 1=2
field1
drop table table2_2;
EXECUTE sp_rename N'TEMP_TAB_table2_2', N'table2_2', 'OBJECT' 
警告: 更改对象名的任一部分都可能会破坏脚本和存储过程。
alter table table2_2 Add  Constraint  PK_table2_2  Primary  Key (field1)
-- =============================================
-- Author:		<wjl>
-- Create date: <2015-5-13>
-- Description:	<测试触发器>
-- =============================================
CREATE TRIGGER [TestTrigger2] 
   ON  [dbo].[table2_2]
   AFTER DELETE
AS 
BEGIN
	
	SET NOCOUNT ON;
	
	print '****************************************************'
END

 

6、查询table2的触发器信息：

SELECT  
    object_name(a.parent_obj) as tableName 
    ,a.name as triggerName 
    ,(case when b.is_disabled=0 then '启用' else '禁用' end) as status  
    ,b.create_date as createDate
    ,b.modify_date as modifyDate  
    ,c.text as triggerText  
FROM sysobjects a  
    INNER JOIN sys.triggers b  
        ON b.object_id=a.id  
    INNER JOIN syscomments c  
        ON c.id=a.id  
WHERE a.xtype='tr' and (a.parent_obj=object_id('table2'))
ORDER BY tableName

 输出结果：