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

 

 

创建时间:将字段设置为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

  

posted @ 2023-06-30 13:46  草木物语  阅读(1131)  评论(0)    收藏  举报