SQL-Ex blog

Вы не всегда увидите все четыре, иногда вам не нужно беспокоиться о том, кто создал или обновил строку. А иногда все, что вам необходимо знать, это когда она последний раз обновлялась. Или когда была создана. Я собираюсь дать вам общее представление об этих четырех, но можете спокойно смешивать их и сочетать, или добавить что-нибудь, что вам может потребоваться и о чем я не упомянул здесь.

Сначала сами столбцы. Я создаю их с говорящими именами. Create(User/Date) и LastUpdate(User/Date). Использование идентичных имен для разных таблиц позволит легче находить нужную информацию. Не говоря уже о написании динамического кода для просмотра информации в большом объеме.

CREATE TABLE AuditColumns (

    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

    Col1 varchar(50),

    CreateUser varchar(50) CONSTRAINT df_CreateUser DEFAULT original_login(),

    CreateDate datetime CONSTRAINT df_CreateDate DEFAULT getdate(),

    LastUpdateUser varchar(50) CONSTRAINT df_LastUpdateUser DEFAULT original_login(),

    LastUpdateDate datetime CONSTRAINT df_LastUpdateDate DEFAULT getdate()

    );

GO

Для столбцов пользователя я использую varchar(50), поскольку этого обычно достаточно. Вам может потребоваться больше (или даже меньше), вы могли бы даже использовать sysname. Я обычно использую original_login(), поскольку это препятствует возможности выдать себя за другое лицо. Однако в зависимости от того, как ваше приложение подключается к базе данных, вам может потребоваться использовать что-то иное. Я здесь обсуждаю, что какая функция, относящаяся к пользователю, делает.

Для столбцов даты я использую тип данных datetime, по больше части из-за лени, и это образец устаревшего кода. Если вы хотите взглянуть на другие варианты, мой друг Randolph West опубликовал фантастическую серию статей о типах данных и функциях даты и времени.

Вы действительно захотите уделить более пристальное внимание используемым типам данных, потому что то, что я здесь использую, добавляет дополнительные 116 байт на строку. И это может быстро нарастать.

Эти значения по умолчанию полезны только для вставки, а мы хотим обновлять информацию. Поэтому, как бы мне это не нравилось, нам нужен триггер.

CREATE TRIGGER upd_AuditColumns

ON dbo.AuditColumns AFTER UPDATE

AS

BEGIN

    IF UPDATE(LastUpdateUser) AND UPDATE(LastUpdateDate)

        RETURN

  

    UPDATE AuditColumns

        SET LastUpdateUser = DEFAULT,

            LastUpdateDate = DEFAULT

    FROM AuditColumns

    JOIN Inserted

        ON AuditColumns.Id = Inserted.Id

END;

GO

Мне следует, вероятно, указать, что значения по умолчанию также не работают, если вы включаете когкретный столбец в вашу команду (т.е. включаете значение для него в ваш оператор insert). Я также могу управлять этим с помощью триггера, но не буду. Мой частный вариант использования предназначен для вставок/обновлений в приложении, и я хочу оставить возможность вносить изменения вручную без дополнительной работы.

Сверху вниз

• Это триггер только на обновление, поскольку я обрабатываю столбцы последнего обновления.


• Оператор IF UPDATE() позволяет мне замкнуть триггер, если кто-то вручную обновляет столбцы последнего обновления.


• Я использую ключевое слово DEFAULT в операторе update, чтобы сослаться на значения по умолчанию для столбца. Если вам интересно, почему у меня были значения по умолчанию для столбцов последнего обновления, хотя значения по умолчанию в основном предназначены для вставок, то именно поэтому. Таким образом, сами значения по умолчанию находятся в одном месте, и если мне нужно их изменить, они ... ну ... как я уже сказал, все в одном месте.


• Я соединяю системное представление inserted с оригинальной таблицей, используя столбцы первичного ключа. Таким образом, мой триггер обрабатывает обновления множества строк, наряду с обновлениями единственной строки.

Как и всегда, когда вы добавляете столбцы или код базы данных, убедитесь, что вы протестировали код приложения на наличие в нем ошибок.