Как создать триггер на Transact-SQL

Продолжаем изучать SQL в частности Transact-SQL, на примере MS Sql 2008 и сегодня речь пойдет о триггерах. Мы рассмотрим, для чего нужны триггеры, основной синтаксис написания триггера, и, конечно же, все это будем делать на основе простого и понятного примера.

Как создать триггер на Transact-SQL

И как обычно начнем с небольшой теории, узнаем, что такое триггер и вообще, зачем он нужен. Т.е. дадим определение триггеру и рассмотрим его назначение в базе данных.

Определение и назначение триггера

Триггер – в базе данных это обычная хранимая процедура, но вызывается она не пользователем, а событием, которое может произойти в базе данных. События могут быть следующие, и относятся они как к таблицам, так и к представлениям VIEW:

  • INSERT – добавление записей в таблицу;
  • DELETE – удаление записей из таблицы;
  • UPDATE – обновление записей таблицы.

С определением разобрались, теперь давайте разберемся, для чего нужны эти триггеры. А нужны они всего лишь для одной цели, выполнение дополнительных действий до или после самой sql инструкции в которой присутствуют вышеперечисленные события. Имеется в виду, Вы сами можете указать, когда выполнять и на какое событие реагировать. Вы можете написать триггер сразу на все события (insert, delete, update) а можете только на одно или на два.

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

Пример создания триггера в MS SQL 2008

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

Мы с Вами уже реализовывали подобную задачу, которую рассматривали в материале Журналирование изменений данных в таблице на Transact-SQL. Но там мы не использовали триггеры, и использовали своего рода импровизированный метод хранения всех изменений, но сегодня как в материале Transact-sql – работа с xml мы будем использовать XML данные именно в триггере.

Примечание! Все действия мы будем выполнять в Management Studio SQL сервера.

И для начала, давайте создадим таблицу, на которую будем вешать триггер:

Код создания таблицы:

   
   CREATE TABLE [dbo].[test_table](
        [number] [int] NULL,
        [pole1] [nvarchar](255) NULL,
        [pole2] [nvarchar](255) NULL
   ) ON [PRIMARY]
   GO

И добавим пару строк, которые в дальнейшем мы будем обновлять, я добавил следующие данные, и проверил их наличие с помощью простого запроса select

Теперь давайте создадим таблицу, в которую мы будем записывать все изменения, я назвал ее audit.

Код создания таблицы audit:

   
   CREATE TABLE [dbo].[audit](
        [id] [bigint] IDENTITY(1,1) NOT NULL,
        [table_name] [varchar](50) NOT NULL,
        [oper] [varchar](15) NOT NULL,
        [record_old] [xml] NULL,
        [record_new] [xml] NULL,
        [username] [varchar](50) NOT NULL,
        [date_ch] [datetime] NULL,
   CONSTRAINT [PK_audit] PRIMARY KEY CLUSTERED 
   (
        [id] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
   ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   GO

Где,

  • id – это идентификатор с автоинкрементном;
  • table_name – название таблицы, в которой произошло изменение;
  • oper – операция, в данном случае у нас туда будет записывать update;
  • record_old – xml данные старой строки;
  • record_new — xml данные новой строки;
  • username – кто изменил;
  • date_ch – когда изменили.

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

CREATE TRIGGER название триггера ON таблица на которую вешать

FOR на какие операции (update, insert, delete)

AS

BEGIN

Сюда пишем сами sql инструкции которые необходимо выполнять при срабатывании триггера

END

Теперь пришло время привести пример кода самого триггера. SQL инструкции я прокомментировал.

   
   CREATE TRIGGER [dbo].[trg_test_table_update] ON [dbo].[test_table]
   for UPDATE
   AS
   BEGIN
   SET NOCOUNT on
   --переменные для хранения старых и новых данных
   DECLARE @record_new xml
   DECLARE @record_old xml
   --задаем значения этим переменным
   -- в таблице deleted хранятся старые данные, или удаленные 
   SET @record_old=(SELECT * FROM deleted  FOR XML RAW, TYPE);
   -- в таблице inserted хранятся измененные данные, или только что созданные 
   SET @record_new=(SELECT * FROM inserted FOR XML RAW, TYPE);
   /*--проверяем, действительно ли update обновил хотя бы одну строку, 
   так как может возникнуть ситуация Вы запустили update 
   при этом он не обновил не одной строки, не подошло условие*/
     IF @record_new is not null and @record_old is not null
      BEGIN
        INSERT INTO dbo.audit (table_name, oper, record_old, record_new, username, date_ch)
        VALUES ('test_table', 'update', @record_old, @record_new, SUSER_NAME(), GETDATE() )
      END
   END
   GO

Теперь осталось проверить работу данного триггера, для начала запустим update одной строки.

И посмотрим, что у нас появилось в таблице audit.

Как видите, XML данные легко просматриваются, и мы видим, что в старой записи были одни значения, а в новой уже соответственно новые.

Кстати если нажать на эти xml данные можно попасть в отдельное окно, где еще наглядней будет видно, что за данные мы обновили, но для начала давайте попробуем, обновить сразу несколько строк, и посмотрим, как поведет себя наш триггер, запустим простой запрос на обновление:

  
   UPDATE test_table SET pole1 = 'обновили' , pole2 = 'обновили'
   WHERE number in(1,2)

И проверяем audit

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

А здесь отображены все строки, которые мы затронули.

Вот такой простой пример написания триггера, надеюсь, теперь стал понятно, как писать эти самые триггера и для чего их можно использовать. Но учтите триггера не всегда полезны так как неправильное их применение может привести к серьезным последствиям. Поэтому прежде чем повесить триггер на ту или иную таблицу, все тщательно продумайте, возможно, можно будет обойтись и без триггера.  Ну, на этом все! Удачи!

Заметка! Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментарии: 5
  1. Дарья

    Здравствуйте, во-первых, спасибо за понятное изложение… лишь на третьей странице google набрела на нормальное объяснение, но(!) Вы даете слишком мало комментариев используя сложные инструкции (ON PRIMARY, ALLOW_PAGE_LOCK S, STATISTICS_NORE COMPUTE и т.д.), так, что приходится бродить по google, чтобы все понять. И еще, используемое для триггера имя таблицы ‘audit’, явно зарезервировано системой, это видно даже в подсветке синтаксиса, и SQL SERVER мне также об этом любезно сообщил. Было бы правильнее использовать какое-нибудь другое, то же ‘audits’.
    Спасибо!

  2. Mihel

    У меня почему-то операция удаления строки приводит к появлению в таблице Audit одной записи, а операция вставки или обновления одной строки приводит к появлению в таблице Audit 2-х записей. Почему так происходит?

  3. Админ (автор)

    Цитирую Mihel:У меня почему-то операция удаления строки приводит к появлению в таблице Audit одной записи, а операция вставки или обновления одной строки приводит к появлению в таблице Audit 2-х записей. Почему так происходит?
    Покажи, пожалуйста, код триггера?

  4. Talgat

    Статья хорошая. Все достаточно понятно. Создал на базе этого триггера 5 штук в одной базе, к 5 таблицам за которыми надо следить. Но для того чтобы не вредить базу, создал еще одну базу в которой создал 5 таблиц и туда триггер вносит все изменения в рабочей базе.
    Рабочая база — Tests — проверяемая таблица tbLo.
    База для контроля — Audit — таблица с изменениями — audit_tbLo.
    Вот код одного триггера:
    *************** *************** ********
    CREATE TRIGGER [dbo].[trg_tbLo_updat e] ON [dbo].[tbLo]
    for UPDATE
    …….
    begin
    insert into audit.dbo.audit_tbLo (table_name, oper, record_old, record_new, username, date_ch) —audit.dbo.audit_tbLo — это таблица в другйо базе
    values (‘tests.dbo.tbLo’, ‘update’, @record_old, @record_new, SUSER_NAME(), GETDATE() )
    end
    END
    GO

    *************** *************** ***********
    Прошу подсказать, если можно , есть ли ошибки? Не будет ли грузить сервер такой триггер?

  5. Светлана

    Ничего не получается. Выдает ошибку:
    Сообщение 311, уровень 16, состояние 1, процедура trg_test_table_update, строка 11 Нельзя использовать столбцы text, ntext или image в таблицах «inserted» и «deleted».
    На строку: — в таблице deleted хранятся старые данные, или удаленные
    SET @record_old=(SELECT * FROM deleted FOR XML RAW, TYPE);
    Сообщение 311, уровень 16, состояние 1, процедура trg_test_table_update, строка 13
    Нельзя использовать столбцы text, ntext или image в таблицах «inserted» и «deleted».
    На строку: — в таблице inserted хранятся измененные данные, или только что созданные
    SET @record_new=(SELECT * FROM inserted FOR XML RAW, TYPE);
    Что не так? подскажите…

Добавить комментарий для Mihel Отменить ответ

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.