利用SqlServer触发器自动更新表updatetime字段值

  本文主要记录了使用SqlServer数据库触发器自动更新表的"更新时间updatetime"字段

  在 MySQL数据库中,某行数据创建时间字段 createtime 、 行最新更新时间字段updatetime 建表时可分别

用"datetime DEFAULT CURRENT_TIMESTAMP" 和 " datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"约束,MySQL建表如下:

CREATE TABLE `student` (
	`id` int(10) unsigned not null auto_increment,
	`name` varchar(60) default '' comment '学生姓名',
	`sex` varchar(1) default 'Y' comment '性别',
 
	`createtime` datetime DEFAULT CURRENT_TIMESTAMP,  
    `updatetime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

即可达到预期目标,updatetime字段会自动记录行的更新时间

  但是在在SqlServer数据库中, 以上这种方式建表 createtime字段效果如预期,但updatetime字段并不能自动记录更新时间。所以本人利用SqlServer触发器达到目标,来不及解释了,快上车解决问题,用如下格式建表即可:

-- 库名 my_ss 
-- schema名  myschema (默认是dbo)
-- 表名: TAB_USR
-- 触发时机、条件: 有update操作后
 
IF OBJECT_ID(N'myschema.TAB_USR', N'U') IS  NOT  NULL 
DROP TABLE  myschema.TAB_USR;
CREATE TABLE myschema.TAB_USR(
    ID varchar(6) not null default '',
    SEX char(1) not null default '',
    NAME varchar(10) not null default '',
 
    CRTTIM datetime DEFAULT CURRENT_TIMESTAMP,  
     UPDTIM datetime DEFAULT CURRENT_TIMESTAMP, 
    PRIMARY KEY (ID)
);
-- 添加表注释
 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户信息表',  @level0type=N'SCHEMA', @level0name=N'myschema', @level1type=N'TABLE', @level1name=N'TAB_USR'; 
 -- 添加字段注释
 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号',  @level0type=N'SCHEMA', @level0name=N'myschema', @level1type=N'TABLE', @level1name=N'TAB_USR', @level2type=N'column', @level2name=N'ID';
 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别',  @level0type=N'SCHEMA', @level0name=N'myschema', @level1type=N'TABLE', @level1name=N'TAB_USR', @level2type=N'column', @level2name=N'SEX';
 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名',  @level0type=N'SCHEMA', @level0name=N'myschema', @level1type=N'TABLE', @level1name=N'TAB_USR', @level2type=N'column', @level2name=N'NAME';
 
-- 指定使用的库名
USE [my_ss]
GO 
-- 国际惯例
SET ANSI_NULLS ON 
GO
SET QUOTED_IDENTIFIER ON 
GO
-- 创建触发器
CREATE TRIGGER [myschema].[tgr_TAB_USR_updtim]
ON [myschema].[TAB_USR]
-- 触发时机、条件
AFTER UPDATE AS 
BEGIN 
    SET NOCOUNT ON;
    UPDATE myschema.[TAB_USR]
    SET UPDTIM=SYSDATETIME()
    WHERE ID IN (SELECT DISTINCT ID FROM inserted)
END 
GO 
-- 启动触发器
ALTER TABLE [myschema].[TAB_USR] ENABLE TRIGGER tgr_TAB_USR_updtim
 

 向 表TAB_USR里插入数据:

insert into myschema.TAB_USR(ID, SEX, NAME) values ('101', '0', '霜霜'), ('102', '1', '老王'), ('103', '1', '大师'), ('104', '1', '小明');

 

 可以看到 CRTTIM  UPDTIM字段的时间值都有,为当前系统时间值:

进行更新操作:

UPDATE myschema.TAB_USR set SEX='1' where ID='101';

再次查看UPDTIM字段的值已经自动更新:

触发器基本格式:

use 数据库名
go
exec sp_helptext '触发器名称'
 
 CREATE TRIGGER trigger_name
 ON table_name
 [WITH ENCRYPTION]  -- 加密
  FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
 AS 
  T-SQL语句
GO
--with encryption 表示加密触发器定义的sql文本
--delete,insert,update指定触发器的类型

关于SqlServer触发器的基础知识:

   SqlServer有三种常规触发器, DML触发器、DDL触发器、登录触发器
    DML数据操作语言触发器,insert delete update 会触发

触发器语句中使用了两种特殊的表,deleted和 inserted
    触发器被触发时,系统自动在内存中创建 deleted inserted表,只读,不能修改,触发器执行完成后,自动删除
    deleted表用于存储 delete update语句锁影响的行的副本,在执行delete 或 update语句时,行从触发器表中删除,并传输到             deleted表中,deleted表和触发器表通常没有相同的行
    inserted表用于存储 insert 和 update语句所影响的行的副本,在一行插入或者更新事务处理中,新建行被同时添加到inserted表 和触发器表中,inserted表中的行数触发器表中新行的副本

触发器中 for、 after 、instead of 三者区别:
 instead of :相当于系统不直接对表进行操作,而是把操作内容交给触发器,让触发器检查将要进行的操作是否正确,如正确才能进行相应的操作,每个表只能创建一个 instead of 触发器 
after   :系统对表执行了 insert update delete的操作之后,才触发触发器,进行后续操作,每个表可创建多个 after触发器
for      : 和 after效果一样 

查看所有触发器:

查看数据库里的触发器
use 数据库名
go
select * from sysobjects where xtype='TR'

如:

USE [my_ss]
GO

SELECT * FROM SYSOBJECTS WHERE XTYPE='TR'

 

禁用:alter table 表名 disable trigger 触发器名称
启用:alter table 表名 enable trigger 触发器名称

如果有多个触发器,则各个触发器名称之间用英文逗号隔开。

如果把“触发器名称”换成“ALL”,则表示禁用或启用该表的全部触发器。

posted on 2018-08-29 19:00  葱大大家禽兽  阅读(16340)  评论(0编辑  收藏  举报

导航