Триггер на обновление таблицы sql

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Удаление eset без пароля

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

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

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

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

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

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

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

AS

BEGIN

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

END

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

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

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

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

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

И проверяем audit

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

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

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

Читайте также:  1С переместить элемент формы программно

Как сделать обновление в таблице с помощью триггера SQL? Я обновляю таблицу test_trigger , в которой есть триггер для обновления таблицы table_forum. Но я не могу понять как можно обновлять table_forum если какие то данные изменились в test_trigger. Как можно передать значение в триггер 1000 и 200, которые я передаю в таблицу test_trigger?

Обновляем test_trigger:

Вот триггер таблицы test_trigger:

Примечание: Триггер с параметром AFTER. То есть, сначала таблица обновляется test_trigger а потому же table_forum.

1 ответ 1

Надо использовать alias NEW и OLD .
OLD – это запись до изменения
NEW – это запись после изменения

Т.е. в Вашем варианте будет так:

Это можно использовать как в триггерах BEFORE UPDATE, так и в AFTER UPDATE

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Возвращает логическое значение, указывающее на попытку применить функцию INSERT или UPDATE к указанному столбцу таблицы или представлению. Returns a Boolean value that indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view. UPDATE() используется в любом месте внутри тела триггера Transact-SQL Transact-SQL INSERT или UPDATE, чтобы проверить необходимость выполнения определенных действий. UPDATE() is used anywhere ins >Transact-SQL Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions.

Синтаксические обозначения в Transact-SQL Transact-SQL Syntax Conventions

Синтаксис Syntax

Аргументы Arguments

column column
Это имя столбца для проверки на действие INSERT или UPDATE. Is the name of the column to test for either an INSERT or UPDATE action. Так как имя столбца указано в предложении триггера ON, не ставьте имя таблицы перед именем столбца. Because the table name is specified in the ON clause of the trigger, do not include the table name before the column name. Столбец может иметь любой тип данных, поддерживаемый SQL Server SQL Server . The column can be of any data type supported by SQL Server SQL Server . Однако вычисляемые столбцы не могут использоваться в данном контексте. However, computed columns cannot be used in this context.

Типы возвращаемых данных Return Types

Логическое значение Boolean

Remarks Remarks

Функция UPDATE() возвращает TRUE независимо от того, была ли попытка применить операторы INSERT или UPDATE удачной. UPDATE() returns TRUE regardless of whether an INSERT or UPDATE attempt is successful.

Читайте также:  Электрон разогнанный разностью потенциалов u 2кв

Чтобы проверить действие операторов INSERT или UPDATE для нескольких столбцов, укажите отдельно предложение UPDATE(column), следующее за первым предложением. To test for an INSERT or UPDATE action for more than one column, specify a separate UPDATE(column) clause following the first one. Несколько столбцов также могут быть проверены на действие INSERT или UPDATE при помощи COLUMNS_UPDATED. Multiple columns can also be tested for INSERT or UPDATE actions by using COLUMNS_UPDATED. В результате возвращается битовый шаблон, который указывает на то, какие столбцы были вставлены или обновлены. This returns a bit pattern that indicates which columns were inserted or updated.

IF UPDATE возвращает значение TRUE по действиям оператора INSERT, так как столбцы содержат либо явные вставленные значения, либо неявные вставленные значения (NULL). IF UPDATE returns the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

Функции предложения IF UPDATE(column) аналогичны предложениям IF, IF. ELSE или WHILE и могут использовать блок BEGIN. END. The IF UPDATE(column) clause functions the same as an IF, IF. ELSE, or WHILE clause and can use the BEGIN. END block. Дополнительные сведения см. в разделе Язык управления потоком (Transact-SQL). For more information, see Control-of-Flow Language (Transact-SQL).

UPDATE(column) может применяться в любой части текста триггера Transact-SQL Transact-SQL . UPDATE(column) can be used anywhere ins >Transact-SQL Transact-SQL trigger.

Если триггер применяется к столбцу, значение UPDATED будет возвращаться в виде true или 1 , даже если значение столбца остается неизменным. If a trigger applies to a column, the UPDATED value will return as true or 1 , even if the column value remains unchanged. Это нормальное поведение, и триггер должен реализовывать бизнес-логику, которая определяет, допустимы ли операции вставки, обновления и удаления. This is by-design, and the trigger should implement business logic that determines if the insert/update/delete operation is permissible or not.

Примеры Examples

Следующий пример создает триггер, который выдает сообщение клиенту при попытке обновить столбец StateProvinceID или PostalCode в таблице Address . The following example creates a trigger that prints a message to the client when anyone tries to update the StateProvinceID or PostalCode columns of the Address table.

Оцените статью
Добавить комментарий

Adblock detector