一个记录数据库中数据改动的基本信息，大家来提提意见

[DeceitLei]

题目：
在现有的数据库基础上获得改动字段的名称，字段属性等信息，并将其保存到Opensource数据库中

解决方案：
在Opensource数据库中建立三张表，分别是Modify_Column_Information、Modify_Column_Value、Temp_Column_Value
Modify_Column_Information是获得改动字段的属性信息，表结构为：

CREATE TABLE [dbo].[Modify_Column_Information] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Table_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Column_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Column_type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Column_length] [int] NOT NULL ,
[Column_isnull] [int] NOT NULL ,
[Modify_type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Modify_date] [datetime] NOT NULL
) ON [PRIMARY]

Modify_Column_Value是获得改动字段的值，如果是delete操作则存入的是删除之前的值，如果是insert或update操作的话则存入的是修改后的值，表的结构如下：

CREATE TABLE [dbo].[Modify_Column_Value] (
[Table_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Column_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Column_value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[modify_date] [datetime] NOT NULL ,
[modify_type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]

Temp_Column_Value这是一个存放临时值的表，在每一次从deleted或者inserted表中获得相对列的数据时，就将该数据存放至此，表的结构如下：

CREATE TABLE [dbo].[Temp_Column_Value] (
[Temp_Column_Value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

上述三个表均放在OpenSource数据库中。

[DeceitLei]

存储过程的设计：
本案例有两个存储过程，作用分别是想Modify_Column_Information和Modify_Column_Value表插入相应的数据，先看第一个存储过程：

/* 存储过程名为modify_column_information */
/* 作用是向OpenSource.dbo.Modify_Column_Information 插入相应信息 */
-- 如果存在相同的存储过程，则删除
IF OBJECT_ID('dbo.modify_column_information') IS NOT NULL
DROP PROCEDURE dbo.modify_column_information
GO
-- 创建modify_column_information存储过程
CREATE PROCEDURE modify_column_information
@table_name varchar(50) = NULL, -- 输入参数 ：表名
@modify_type varchar(20) = NULL, -- 输入参数 ：更新类型（insert/delete/update）
@modify_date datetime -- 输入参数 ：更新时间
AS
IF @modify_type = NULL OR -- 确保参数不为空
@modify_date = NULL
BEGIN
PRINT 'Please enter modify type.'
PRINT 'Please enter modify datetime.'
END
-- 开始事务处理
BEGIN TRANSACTION
-- 将表的字段信息放入opensource..dbo.Modify_Column_Information中
INSERT opensource.dbo.Modify_Column_Information
(Table_name, Column_name, Column_type, Column_length,
Column_isnull, Modify_type, Modify_date)
SELECT d.name 'table_name',a.name 'column_name' ,b.name 'column_type' ,
a.length, a.isnullable, @modify_type, @modify_date
FROM syscolumns a, systypes b,sysobjects d
WHERE a.xtype=b.xusertype and a.id=d.id and d.name=@table_name
-- 如果出错将回滚事务
IF @@error <> 0
BEGIN
ROLLBACK
RETURN
END

PRINT '**** We have inserted column''s property to OpenSource.dbo.Modify_Column_Information ***'
COMMIT TRANSACTION -- 提交事务处理
GO – 结束


下面介绍Modify_Column_Value存储过程：

/* 存储过程名为modify_column_value */
/* 作用是向OpenSource.dbo.Modify_Column_Value插入相应信息 */
-- 如果存在相同的存储过程，则删除
IF OBJECT_ID('dbo.modify_column_value') IS NOT NULL
DROP PROCEDURE dbo.modify_column_value
GO
-- 创建名为modify_column_value的存储过程
CREATE PROCEDURE modify_column_value
@table_name varchar(50) = NULL, -- 输入参数：表名
@column_name varchar(50) = NULL, -- 输入参数：列名
@modify_type varchar(50) = NULL, -- 输入参数：更改类型（delete/insert/update）
@modify_date datetime -- 输入参数：更改时间
AS
IF @table_name = NULL OR -- 确保参数不为空
@column_name = NULL OR
@modify_type = NULL
BEGIN
PRINT 'Please enter table name.'
PRINT 'Please enter column name.'
PRINT 'Please enter modify type.'
RETURN
END
-- 开始事务处理
BEGIN TRANSACTION
-- 向OpenSource.dbo.Modify_Column_Value中插入相应信息
INSERT opensource.dbo.Modify_Column_Value
SELECT @table_name, @column_name, Temp_Column_Value, @modify_date, @modify_type
FROM opensource.dbo.Temp_Column_Value -- Temp_Column_Value的值是在触发器中插入的
-- 如果有错回滚事务
IF @@error <> 0
BEGIN
ROLLBACK
RETURN
END
COMMIT TRANSACTION – 提交事务处理
GO – 结束

[DeceitLei]

触发器的设计：
从代码讲解，代码如下
/* 创建名为delete_record触发器 */
/* 作用是记录该表的delete insert update操作之前或之后的数据改动和改动日期 */
-- 创建名为delete_record的触发器
CREATE TRIGGER delete_record ON [dbo].[orders] – orders为表名，可以随时更改
FOR DELETE -- 触发操作为删除记录
AS
DECLARE @table_name varchar(50) -- 声明变量：表名
DECLARE @modify_type varchar(50) -- 声明变量：更改类型
DECLARE @col_name varchar(50) -- 声明变量：列名
DECLARE @sql varchar(800) -- 声明变量：用来存放一动态sql语句
DECLARE @date datetime -- 声明变量：更改时间

SELECT @date=getdate() -- 获得当前时间

EXEC modify_column_information -- 调用modify_column_information存储过程
@table_name='orders',
@modify_type='delete',
@modify_date=@date

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL -- 避免系统中存在相同的临时表#temp
DROP TABLE #temp

SELECT * INTO #temp FROM deleted -- 将deleted表中的数据完全复制到#temp表中
-- 如果用deleted表做操作的话，它不支持在动态sql语句里的调用

DECLARE cursor_column_name CURSOR FOR -- 声明游标，获取当前表的列名
SELECT Column_name FROM opensource.dbo.Modify_Column_Information
WHERE Modify_date=@date
OPEN cursor_column_name -- 打开游标
FETCH cursor_column_name INTO @col_name -- 将列名放入@col_name变量中

WHILE @@fetch_status = 0 -- 判断游标是否取到最后一个记录
BEGIN
-- 清理opensource.dbo.Temp_Column_Value
-- 作用是使得opensource.dbo.Temp_Column_Value中只存放当前列名做对应的值组
TRUNCATE TABLE opensource.dbo.Temp_Column_Value

-- 设置动态sql语句，将当前列所对应的值放入Temp_Column_Value表中
SET @sql =
'INSERT opensource.dbo.Temp_Column_Value(Temp_Column_Value) ' +
'SELECT ' + convert(varchar(50), @col_name) +
' FROM #temp'
EXEC(@sql) -- 执行动态sql语句
EXEC modify_column_value -- 调用modify_column_value存储过程
@table_name='orders',
@column_name=@col_name,
@modify_type='delete',
@modify_date=@date
FETCH cursor_column_name INTO @col_name -- 或许表中的下一列的列名
END -- 取完所有的列名，并将值都存放好
CLOSE cursor_column_name -- 关闭游标
DEALLOCATE cursor_column_name -- 释放游标
TRUNCATE TABLE opensource.dbo.Temp_Column_Value -- 清空表Temp_Column_Value

上面介绍的是delete触发器的设计，下面给出insert触发器的代码：

CREATE TRIGGER insert_record ON [dbo].[orders]
FOR INSERT
AS
DECLARE @table_name varchar(50)
DECLARE @modify_type varchar(50)
DECLARE @col_name varchar(50)
DECLARE @sql varchar(800)
DECLARE @date datetime

SELECT @date=getdate()

EXEC modify_column_information
@table_name='orders',
@modify_type='insert',
@modify_date=@date

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp

SELECT * INTO #temp FROM inserted

DECLARE cursor_column_name CURSOR FOR
SELECT Column_name FROM opensource.dbo.Modify_Column_Information
WHERE Modify_date=@date
OPEN cursor_column_name
FETCH cursor_column_name INTO @col_name

WHILE @@fetch_status = 0
BEGIN
TRUNCATE TABLE opensource.dbo.Temp_Column_Value

SET @sql =
'INSERT opensource.dbo.Temp_Column_Value(Temp_Column_Value) ' +
'SELECT ' + convert(varchar(50), @col_name) +
' FROM #temp'
EXEC(@sql)
EXEC modify_column_value
@table_name='orders',
@column_name=@col_name,
@modify_type='insert'
@modify_date=@date
FETCH cursor_column_name INTO @col_name
END
CLOSE cursor_column_name
DEALLOCATE cursor_column_name
TRUNCATE TABLE opensource.dbo.Temp_Column_Value

Update触发器与Insert触发器是一样的，因为我是要记录更新后的数据，所以只从inserted表中取数据。至此为止，一个获得更改数据信息的设计方案就写完了。欢迎大家提出宝贵意见

[iceandfire]

不错不错,怎么没人理会啊

[BluePenguin]

mark

[lqy661]

gz,不错不错！