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

DeceitLei 2003-05-16 04:07:02
题目:
在现有的数据库基础上获得改动字段的名称,字段属性等信息,并将其保存到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数据库中。
...全文
18 5 点赞 打赏 收藏 举报
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
lqy661 2003-05-23
gz,不错不错!
  • 打赏
  • 举报
回复
BluePenguin 2003-05-21
mark
  • 打赏
  • 举报
回复
iceandfire 2003-05-17
不错不错,怎么没人理会啊
  • 打赏
  • 举报
回复
DeceitLei 2003-05-16
触发器的设计:
从代码讲解,代码如下
/* 创建名为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表中取数据。至此为止,一个获得更改数据信息的设计方案就写完了。欢迎大家提出宝贵意见
  • 打赏
  • 举报
回复
DeceitLei 2003-05-16
存储过程的设计:
本案例有两个存储过程,作用分别是想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 – 结束
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2003-05-16 04:07
社区公告
暂无公告