我需要在MS-SQL中创建一个新的DATETIME字段,它始终包含创建记录的日期,然后每当修改记录时都需要自动更新.我听说有人说我需要一个触发器,这很好,但我不知道如何写它.有人可以帮助触发器的语法来实现这一目标吗?
在MySQL术语中,它应该与此MySQL语句完全相同:
ADD `modstamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
以下是一些要求:
我无法更改我的UPDATE语句以在修改行时设置字段,因为我不控制写入记录的应用程序逻辑.
理想情况下,我不需要知道表中任何其他列的名称(例如主键)
它应该简短而有效,因为它会经常发生.
rkawano.. 29
实际上,MSSQL没有办法为UPDATE定义默认值.
因此,您需要添加一个具有默认值的列以进行插入:
ADD modstamp DATETIME2 NULL DEFAULT GETDATE()
并在表格上添加一个Trigger:
CREATE TRIGGER tgr_modstamp ON **TABLENAME** AFTER UPDATE AS UPDATE **TABLENAME** SET ModStamp = GETDATE() WHERE **ID** IN (SELECT DISTINCT **ID** FROM Inserted)
是的,您需要为每个触发器指定一个标识列.
注意:在不知道应用程序代码的表上插入列时要小心.如果您的应用程序具有INSERT VALUES命令而没有字段定义,则即使在新列上使用默认值,也会引发错误.
实际上,MSSQL没有办法为UPDATE定义默认值.
因此,您需要添加一个具有默认值的列以进行插入:
ADD modstamp DATETIME2 NULL DEFAULT GETDATE()
并在表格上添加一个Trigger:
CREATE TRIGGER tgr_modstamp ON **TABLENAME** AFTER UPDATE AS UPDATE **TABLENAME** SET ModStamp = GETDATE() WHERE **ID** IN (SELECT DISTINCT **ID** FROM Inserted)
是的,您需要为每个触发器指定一个标识列.
注意:在不知道应用程序代码的表上插入列时要小心.如果您的应用程序具有INSERT VALUES命令而没有字段定义,则即使在新列上使用默认值,也会引发错误.
Create trigger tr_somename On table_name For update As Begin Set nocount on; Update t Set t.field_name = getdate() From table_name t inner join inserted I On t.pk_column = I.pk_column End
好吧,我总是喜欢不仅记录发生的事情,而且还记得谁做到了!
让我们在名为[dwarfs]的[tempdb]中创建一个测试表.在以前的工作,金融机构,我们跟踪插入(创建)日期和更新(修改)日期.
-- just playing use tempdb; go -- drop table if object_id('dwarfs') > 0 drop table dwarfs go -- create table create table dwarfs ( asigned_id int identity(1,1), full_name varchar(16), ins_date datetime, ins_name sysname, upd_date datetime, upd_name sysname, ); go -- insert/update dates alter table dwarfs add constraint [df_ins_date] default (getdate()) for ins_date; alter table dwarfs add constraint [df_upd_date] default (getdate()) for upd_date; -- insert/update names alter table dwarfs add constraint [df_ins_name] default (coalesce(suser_sname(),'?')) for ins_name; alter table dwarfs add constraint [df_upd_name] default (coalesce(suser_sname(),'?')) for upd_name; go
对于更新,但存在已插入和已删除的表.我选择加入插入更新.
-- create the update trigger create trigger trg_changed_info on dbo.dwarfs for update as begin -- nothing to do? if (@@rowcount = 0) return; update d set upd_date = getdate(), upd_name = (coalesce(suser_sname(),'?')) from dwarfs d join inserted i on d.asigned_id = i.asigned_id; end go
最后但并非最不重要的是,让我们测试代码.任何人都可以输入未经测试的TSQL语句.但是,我总是强调我的团队测试!
-- remove data truncate table dwarfs; go -- add data insert into dwarfs (full_name) values ('bilbo baggins'), ('gandalf the grey'); go -- show the data select * from dwarfs; -- update data update dwarfs set full_name = 'gandalf' where asigned_id = 2; -- show the data select * from dwarfs;
输出.我只在插入和删除之间等了10秒.好的是,谁和何时都被捕获.
这可以从SQL Server 2016开始使用PERIOD FOR SYSTEM_TIME
.
这是为时态表引入的内容,但您不必使用时态表来使用它.
一个例子如下
CREATE TABLE dbo.YourTable ( FooId INT PRIMARY KEY CLUSTERED, FooName VARCHAR(50) NOT NULL, modstamp DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, MaxDateTime2 DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, PERIOD FOR SYSTEM_TIME (modstamp,MaxDateTime2) ) INSERT INTO dbo.YourTable (FooId, FooName) VALUES (1,'abc'); SELECT * FROM dbo.YourTable; WAITFOR DELAY '00:00:05' UPDATE dbo.YourTable SET FooName = 'xyz' WHERE FooId = 1; SELECT * FROM dbo.YourTable; DROP TABLE dbo.YourTable;
它有一些局限性.
存储的时间将由系统更新,并始终为UTC.
需要声明第二列(MaxDateTime2
上面),这对于这个用例是完全多余的.但它可以被标记为隐藏,使其更容易被忽略.