本例演示在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

ALTER
TABLE
[
dbo
]
.
[
Fields
]
ADD
CONSTRAINT
[
PK_Fields
]
PRIMARY
KEY
CLUSTERED
(
[
FieldID
]
)
ON
[
PRIMARY
]
GO
2 建立一个动态字段表Customeres, 当Fields每增加一条记录,本表就相应增加一个字段。
CREATE
TABLE
[
dbo
]
.
[
Customeres
]
(
[
ID
]
[
int
]
IDENTITY
(
1
,
1
)
NOT
NULL
)
ON
[
PRIMARY
]
GO

ALTER
TABLE
[
dbo
]
.
[
Customeres
]
ADD
CONSTRAINT
[
PK_Customeres
]
PRIMARY
KEY
CLUSTERED
(
[
ID
]
)
ON
[
PRIMARY
]
GO
3 为表Fields新增一个触发器,当增加数据的时候,都会自动调用此触发器。注意:字段的长度硬编码,只是为了演示目的,应根据自己的需要来动态调整长度。
CREATE
TRIGGER
trigger_addField
ON
Fields
FOR
INSERT
AS

DECLARE
@FieldID
int
,
@Name
varchar
(
50
),
@DataType
varchar
(
50
),
@SQL
varchar
(
1000
)

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

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