本例演示在SQL Server中,当用户添加一条字段信息数据到一个表中,通过触发器相应在另一个表中自动增加一个对应的字段。
1 首先建立一个字段信息表Fields
CREATE TABLE [dbo].[Fields] (
[FieldID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[DataType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ALTER TABLE [dbo].[Fields] ADD
CONSTRAINT [PK_Fields] PRIMARY KEY CLUSTERED
(
[FieldID]
) ON [PRIMARY]
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2 建立一个动态字段表Customeres, 当Fields每增加一条记录,本表就相应增加一个字段。
CREATE TABLE [dbo].[Customeres] (
[ID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ALTER TABLE [dbo].[Customeres] ADD
CONSTRAINT [PK_Customeres] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3 为表Fields新增一个触发器,当增加数据的时候,都会自动调用此触发器。注意:字段的长度硬编码,只是为了演示目的,应根据自己的需要来动态调整长度。
CREATE TRIGGER trigger_addField ON Fields
FOR INSERT
AS
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DECLARE @FieldID int,
@Name varchar(50),
@DataType varchar(50),
@SQL varchar(1000)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SELECT @FieldID = FieldID, @Name = [Name], @DataType = DataType
FROM Inserted
if not exists (SELECT * FROM syscolumns
where id=object_id('Customeres')
AND name=@Name)
BEGIN
SET @SQL = 'ALTER table Customeres add ' + @Name + ' '
+ @DataType + '(64) NULL'
EXEC (@SQL)
END
PRINT @Name + ',' + @DataType
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4 演示,在查询分析中执行
Insert into Fields ([name], DataType) values ('name', 'varchar')
参考来源:
Add a column to a table unless it already exists
Using Triggers In MS SQL Server