sql server 默认时间创建时间最后修改时间

最新推荐文章于 2023-12-12 15:59:24 发布

caoye_oo0

最新推荐文章于 2023-12-12 15:59:24 发布

阅读量186

点赞数

文章标签：数据库 sql mysql

本文链接：https://blog.youkuaiyun.com/xc_oo0/article/details/135524385

版权

本文展示了如何在SQLServer中创建带有默认当前日期时间戳的`创建时间`字段，并通过触发器自动更新`修改时间`字段。示例涉及ALTERTABLE语句、触发器创建以及对表锁策略的设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建时间：将字段设置为datetime类型，并设置默认值为 getdate()
ALTER TABLE 表名 ADD createTime SMALLDATETIME DEFAULT (GETDATE())

修改时间：通过触发器，在 update After后，使用如下语句实现：
update tableName set columnName=getdate() WHERE ID IN (SELECT DISTINCT ID FROM inserted)

/*
 Navicat Premium Data Transfer

 Source Server         : xxx
 Source Server Type    : SQL Server
 Source Server Version : 13005026
 Source Host           : x.x.x.x:1433
 Source Catalog        : xxx
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 13005026
 File Encoding         : 65001

 Date: 30/06/2023 13:46:50
*/


-- ----------------------------
-- Table structure for TestEqConfig
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[TestEqConfig]') AND type IN ('U'))
	DROP TABLE [dbo].[TestEqConfig]
GO

CREATE TABLE [dbo].[TestEqConfig] (
  [id] int  IDENTITY(1,1) NOT NULL,
  [teamid] int  NULL,
  [field] varchar(8000) COLLATE Chinese_PRC_CI_AS  NULL,
  [create_time] datetime2(0) DEFAULT (getdate()) NULL,
  [update_time] datetime2(0)  NULL
)
GO

ALTER TABLE [dbo].[TestEqConfig] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Auto increment value for TestEqConfig
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[TestEqConfig]', RESEED, 13)
GO


-- ----------------------------
-- Triggers structure for table TestEqConfig
-- ----------------------------
CREATE TRIGGER [dbo].[trigger_TestEqConfig_update]
ON [dbo].[TestEqConfig]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	update TestEqConfig set update_time=getdate() WHERE ID IN (SELECT DISTINCT ID FROM inserted)
END
GO