快捷導(dǎo)航

動(dòng)態(tài)給表添加刪除字段并同時(shí)修改它的插入更新存儲(chǔ)過(guò)程

更新時(shí)間：2011年11月15日 23:46:20 作者：

有一個(gè)表，用戶需要在后臺(tái)操作它，希望能對(duì)它動(dòng)態(tài)進(jìn)行添加刪除字段

有一個(gè)表，用戶需要在后臺(tái)操作它，希望能對(duì)它動(dòng)態(tài)進(jìn)行添加刪除字段。這個(gè)功能也許沒(méi)有問(wèn)題，但是它原有插入與更新的兩個(gè)存儲(chǔ)過(guò)程，也需要一起修改。因此Insus.NET實(shí)現(xiàn)了它，因此此文會(huì)讓你了解到怎樣動(dòng)態(tài)為一個(gè)表添加刪除字段以及動(dòng)態(tài)修改它的存儲(chǔ)過(guò)程

首先需要建一個(gè)表[A]，這個(gè)表只有兩個(gè)字段，一個(gè)是[ID]自動(dòng)增長(zhǎng),另一個(gè)是表[B]的字段名，存儲(chǔ)的每一筆記錄，即是用戶需要操作的表[B]的字段。這個(gè)表[A]需要建添加，更新，以及刪除的存儲(chǔ)過(guò)程，方便用戶在后臺(tái)方便操作，還有重點(diǎn)部分，需要寫觸發(fā)器。如有記錄對(duì)表[A]進(jìn)行添加，更新或是刪除時(shí)，它會(huì)觸發(fā)去作表[B]相應(yīng)操作，還要去修改表[B]的存儲(chǔ)過(guò)程。

動(dòng)態(tài)修改表[B]的存儲(chǔ)過(guò)程：

復(fù)制代碼代碼如下:

 
CREATE PROCEDURE [dbo].[usp_B_DymanicallyAlterStoreProcedure] 
AS 
DECLARE @VariableList NVARCHAR(MAX) = '' 
DECLARE @FieldList NVARCHAR(MAX) = '' 
DECLARE @ValueList NVARCHAR(MAX) = '' 
DECLARE @FieldValueList NVARCHAR(MAX) = '' 

DECLARE @I INT = 1, @R INT = 0 
SET @R = (SELECT MAX([Id]) FROM [dbo].[A]) 
WHILE (@I <= @R) 
BEGIN 
DECLARE @fName NVARCHAR(100) 
IF EXISTS(SELECT [Id] FROM [dbo].[A] WHERE [Id] = @I) 
BEGIN 
SELECT @fName = [FieldName] FROM [dbo].[A] WHERE [Id] = @I 
SET @VariableList = @VariableList + ',@' + @fName +' DECIMAL(18,4)' --動(dòng)態(tài)的字段數(shù)據(jù)類型都一樣 
SET @FieldList = @FieldList + ',[' + @fName + ']' 
SET @ValueList = @ValueList + ',@' + @fName 
SET @FieldValueList = @FieldValueList + ',[' + @fName + '] = @' + @fName 
END 
SET @I = @I + 1 
END 

DECLARE @sql_I NVARCHAR(MAX),@sql_U NVARCHAR(MAX) 
SET @sql_I = ' 
ALTER PROCEDURE [dbo].[usp_B_Insert] 
( 
@ItemCode NVARCHAR(50) 
'+ @VariableList +' 
) 
AS 
INSERT INTO [dbo].[B] ([ItemCode]'+ @FieldList +') VALUES (@ItemCode'+ @ValueList +') 
' 
EXECUTE sp_EXECUTESQL @sql_I; 

SET @sql_U = ' 
ALTER PROCEDURE [dbo].[usp_B_Update] 
( 
@Id INT, 
@ItemCode NVARCHAR(50) 
'+ @VariableList +' 
) 
AS 
UPDATE [dbo].[B] SET [ItemCode] = @ItemCode'+ @FieldValueList +' WHERE [Id] = @Id 
' 
EXECUTE sp_EXECUTESQL @sql_U; 

表[A]的插入觸發(fā)器：

復(fù)制代碼代碼如下:

 
CREATE TRIGGER [dbo].[tri_A_Insert] ON [dbo].[A] 
FOR INSERT 
AS 
BEGIN 
SET NOCOUNT ON 
DECLARE @FieldName NVARCHAR(50) 
SELECT @FieldName = [FieldName] FROM INSERTED 

EXECUTE('IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE [id] = OBJECT_ID(''B'') AND [name] = '''+ @FieldName +''') 
ALTER TABLE [B] ADD ['+ @FieldName +'] DECIMAL(18,4) NULL') 

EXECUTE [dbo].[usp_B_DymanicallyAlterStoreProcedure]; 
END 

表[A]刪除觸發(fā)器：

復(fù)制代碼代碼如下:

 
CREATE TRIGGER [dbo].[tri_A_Delete] ON [dbo].[A] 
FOR DELETE 
AS 
BEGIN 
SET NOCOUNT ON 
DECLARE @FieldName NVARCHAR(50) 
SELECT @FieldName = [FieldName] FROM DELETED 

EXECUTE('IF EXISTS(SELECT * FROM SYSCOLUMNS WHERE [id] = OBJECT_ID(''B'') AND [name] = '''+ @FieldName +''') 
ALTER TABLE [B] DROP COLUMN ['+ @FieldName +']') 

EXECUTE [dbo].[usp_B_DymanicallyAlterStoreProcedure]; 
END 