MSSQL 資料庫製作觸發程式 (自動補更新日期)

tags: 資料庫

使用情境

希望每次資料"被"更新時，自動補上日期的時候會使用

雖然也可以透過程式AP端呼叫時傳入日期，但有幾個問題

1. 程式的日期和資料庫會有些微落差
2. 如果程式人員手殘忘記加
3. 想檢查資料庫是否被程式端以外的方式異動(如資料庫管理人員未透過程式自行操作)

因此需要使用 觸發Trigger 的方式來自動補上日期

步驟1. 建立資料表，在預設欄位填上產生日期的函式 GETDATE()，如果使用語法生成範例如下

CREATE TABLE dbo.YourTable
( ........,
  LastModifiedOn DATETIME 
      CONSTRAINT DF_YourTable_LastModifiedOn DEFAULT (GETDATE())
)

建立觸發程序，讓資料表每次被新增或修改時，自動將欄位資料更新

CREATE TRIGGER trgAfterUpdate
ON dbo.YourTable
AFTER UPDATE 
AS BEGIN
   UPDATE dbo.YourTable
   SET LastModifiedOn = GETDATE()
   FROM INSERTED i
   WHERE i.Table1ID = YourTable.Table1ID
END

完整範例

CREATE TABLE [dbo].[CustomCssScript](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[data] [nvarchar](max) NULL,
	[updatetime] [datetime] NULL,
 CONSTRAINT [PK_CustomCssScript] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomCssScript] ADD  CONSTRAINT [DF_CustomCssScript_updatetime]  DEFAULT (getdate()) FOR [updatetime]
GO
/****** Object:  Trigger [dbo].[trgAfterUpdate]    Script Date: 2021/10/30 上午 12:59:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trgAfterUpdate]
ON [dbo].[CustomCssScript]
AFTER UPDATE 
AS BEGIN
   UPDATE dbo.CustomCssScript
   SET updatetime = GETDATE()
   FROM INSERTED i
   WHERE i.id = CustomCssScript.id
END
GO
ALTER TABLE [dbo].[CustomCssScript] ENABLE TRIGGER [trgAfterUpdate]
GO

參考來源

https://stackoverflow.com/questions/1882200/how-to-alter-a-datetime-field-when-it-is-updated