Нужно поле datetime в MS SQL, которое автоматически обновляется при изменении записи

Мне нужно создать новое поле DATETIME в MS-SQL, которое всегда будет содержать дату создания записи, а затем оно должно автоматически обновляться всякий раз, когда запись будет изменена. Я слышал, что люди говорят, что мне нужен триггер, и это нормально, но я не знаю, как его написать. Может ли кто-нибудь помочь с синтаксисом триггера для выполнения этого?

В терминах MySQL он должен делать то же самое, что и этот оператор MySQL:

ADD `modstamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Вот несколько требований:

Я не могу изменить свои инструкции UPDATE для установки поля при изменении строки, потому что я не контролирую логику приложения, которая записывает записи.
В идеале мне не нужно было бы знать имена любых других столбцов в таблице (например, первичный ключ)
Он должен быть коротким и эффективным, потому что это произойдет очень часто.

Ответ 1

MSSQL, на самом деле, не имеет способа определить значение по умолчанию для UPDATE.

Итак, вам нужно добавить столбец со значением по умолчанию для вставки:

ADD modstamp DATETIME2 NULL DEFAULT GETDATE()

И добавьте триггер в таблицу:

CREATE TRIGGER tgr_modstamp
ON **TABLENAME**
AFTER UPDATE AS
  UPDATE **TABLENAME**
  SET ModStamp = GETDATE()
  WHERE **ID** IN (SELECT DISTINCT **ID** FROM Inserted)

И да, вам нужно указать столбец идентификатора для каждого триггера.

ВНИМАНИЕ: будьте осторожны при вставке столбцов в таблицы, где вы не знаете код приложения. Если ваше приложение имеет команду INSERT VALUES без определения поля, оно будет увеличивать ошибки даже со значением по умолчанию для новых столбцов.

Ответ 2

Хорошо, мне всегда нравится отслеживать не только когда что-то случилось, но и кто это сделал!

Позволяет создать тестовую таблицу в [tempdb] с именем [dwarfs]. На предыдущем задании, финансовом учреждении, мы отслеживаем вставленную (созданную) дату и обновленную (модифицирующую) дату.

-- 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 секунд между вставкой и удалением. Приятно, что кто и когда захвачен.

Ответ 3

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

Ответ 4

Это возможно после 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 выше), который является совершенно лишним для этого варианта использования. Но он может быть помечен как скрытый, что упрощает игнорирование.

Ответ 5

ALTER TRIGGER [trg_table_name_Modified]
ON [table_name]
AFTER UPDATE 
AS
Begin
    UPDATE table_name
    SET modified_dt_tm = GETDATE()  -- or use SYSDATETIME() for 2008 and newer
    FROM Inserted i
    WHERE i.ID = table_name.id

    end