最新公告
  • 欢迎您光临AA分享网,一个高级程序员的学习、分享的分享平台!立即加入我们
  • SQL Server如何利用触发器实现表的历史修改痕迹的

    在开发许多应用程序时,有必要记录某些数据表的历史记录或修改记录,以便将来发生数据错误时可以检查数据。针对此业务需求,我们可以通过数据库触发器轻松实现历史记录功能。那么,SQL
    Server如何利用触发器实现表的历史修改痕迹的?本文以SQL Server 2005数据库中的触发器为例(因为手中的项目用的就是这个数据库)。

      先简单描述一下SQL Server触发器

      SQL Server触发器的inserted和deleted

    SQL
    Server为每个触发器都创建了两个专用虚拟表:inserted表和deleted表。这两个表由系统来维护,他们存在于内存中,而不是在数据库中。这两个表的结构总是与被该触发器作用的表结构相同。触发器执行完成后,与该触发器相关的两个表会被删除(即在内存中销毁)。

    inserted表存放由执行insert或update语句而要想飙中插入的所有行;即:插入后或更新后的值。

    deleted表存放由delete或update语句而要从表中删除的所有行;即:删除或更新钱的值。

      SQL Server触发器的instead of和after

    SQL Server提供了两种触发器:instead of和after触发器。这两种触发器的区别在于他们被激活的时机不同:

    · instead of触发器用于替代引用触发器执行的sql语句。除表之外,instead
    of触发器也可以用于视图,用来扩展视图可以支持更新操作。

    ·
    after触发器在一个inserted、update或delete语句之后执行,进行约束检查等动作都在after触发器被激活之前发生。after触发器只能用于数据表中。

    说(复制)了这么多,是因为我们要实现的功能需要用到inserted虚拟表、deleted虚拟表和after触发器。

      实现方法

    通过一个示例来演示具体的实现方法。

    假设当前有一个表:产品表(product),字段为“产品名(name)”、“产品描述(description)”、“单价(unit_cost)”和“生成日期(pub_time)”。

    CREATE TABLE product(name VARCHAR(50),description VARCHAR(200),unit_cost
    MONEY,pub_time DATETIME)

    GO

    现在我们”上帝”的需求是:需要记录product表发生数据变化(增、删、改)时,记录每次操作改动情况。

      1.创建日志表

    需要创建一个产品日志表(product_log)用来将记录每次数据改动情况,我这里直接在原数据表的结构上增加两个字段(在实际开发环境中,大家可以根据需求来设置日志表的表结构),分别为sqlcomm和exectime;代码如下:

    1).CREATE TABLE product_log(name VARCHAR(50),description
    VARCHAR(200),unit_cost MONEY,pub_time DATETIME,sqlcomm varchar(10),exectime
    datetime)

    2).GO

    新增的两个字段sqlcomm和exectime分别记录执行命令(insert、update和delete)和执行时间。

      2.增加触发器

    在产品表增加触发器,其目的是为了记录表数据发生改变时记录到product_log中。

    针对插入(insert)操作,增加名为tr_product_i的触发器:

    1.CREATE TRIGGER tr_product_i

    2.ON product

    3.AFTER INSERT

    4.AS

    5.if @@rowcount = 0 –为了避免占用资源,当影响行数为0时,结束触发器

    6.return

    7.insert into product_log
    (name,description,unit_cost,pub_time,sqlcomm,exectime)

    8.select name,description,unit_cost,pub_time,'insert',getdate() from
    inserted

    9.GO

      针对更新(update)操作,增加名为tr_product_u的触发器:

    1.CREATE TRIGGER tr_product_u

    2.ON product

    3.AFTER UPDATE

    4.AS

    5.if @@rowcount = 0 –为了避免占用资源,当影响行数为0时,结束触发器

    6.return

    7./*更新前*/

    8.insert into product_log
    (name,description,unit_cost,pub_time,sqlcomm,exectime)

    9.select name,description,unit_cost,pub_time,'update',getdate() from
    deleted

    10./*更新后*/

    11.insert into product_log
    (name,description,unit_cost,pub_time,sqlcomm,exectime)

    12.select name,description,unit_cost,pub_time,'update',getdate() from
    inserted

    13.GO

      针对删除(delete)操作,增加名为tr_product_d的触发器:

    1.CREATE TRIGGER tr_product_d

    2.ON product

    3.AFTER DELETE

    4.AS

    5.if @@rowcount = 0 –为了避免占用资源,当影响行数为0时,结束触发器

    6.return

    7.insert into product_log
    (name,description,unit_cost,pub_time,sqlcomm,exectime)

    8.select name,description,unit_cost,pub_time,'delete',getdate() from
    deleted

    9.GO

      3.测试触发器

      插入(insert)测试

    1.INSERT INTO product(name,description,unit_cost,pub_time)

    2.VALUES('逗比','这是一个逗比的测试数据',200.5,'1990-11-18')

    3.GO

    4.SELECT * FROM product

    5.SELECT * FROM product_log

    6.GO

      更新(update)测试

    1.UPDATE product SET unit_cost=250.0 WHERE name='逗比'

    2.GO

    3.SELECT * FROM product

    4.SELECT * FROM product_log

    5.GO

      删除(delete)测试

    1.DELETE FROM product WHERE name='逗比'

    2.GO

    3.SELECT * FROM product

    4.SELECT * FROM product_log

    5.GO

    以上即是关于SQL Server如何利用触发器实现表的历史修改痕迹的全部内容,想了解更多关于SQL
    Server数据库的信息,请继续关注。

    AA分享网一个高级程序员的学习、分享的IT资源分享平台
    AA分享网-企业网站源码-PHP源码-网站模板-视频教程-IT技术教程 » SQL Server如何利用触发器实现表的历史修改痕迹的
    • 277会员总数(位)
    • 6130资源总数(个)
    • 0本周发布(个)
    • 0 今日发布(个)
    • 788稳定运行(天)

    提供最优质的资源集合

    立即查看 了解详情