Создание триггера sql: MS SQL Server и T-SQL

Содержание

Создание триггера для формата телефона по умолчанию с помощью T-SQL

Как и сказал @Jason хинонов, Transact-SQL не поддерживает триггеры FOR EACH ROW . Он также не поддерживает BEFORE , поэтому и его, и мои ответы предлагают триггер AFTER . Хотя, на самом деле, в T-59 есть триггеры INSTEAD OF , которые иногда могут использоваться в качестве замены триггеров BEFORE , тем не менее, они совершенно разные (как можно предположить из названий).

В любом случае, триггер AFTER должен отлично работать для этой проблемы, так что вот вам еще одна версия Transact-SQL:

CREATE TRIGGER locations_format_phone
ON locations
AFTER INSERT, UPDATE
AS
BEGIN
  UPDATE locations
  SET phone = '(' + STUFF(STUFF(RIGHT('907' + RTRIM(i.phone), 10), 7, 0, '-'), 4, 0, ') ')
  FROM inserted AS i
  WHERE i.location_id = locations.location_id
    AND LEN(i.phone) IN (7, 10)
END

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

phone составляет 7 или 10 символов. (10 chars) (original value)

Затем функция STUFF вызывается дважды, чтобы вставить a - и A ) в середину полученного числа. Поскольку позиции вставки связаны с неизменным 10-значным числом, сначала вставляется - , а затем ) (в противном случае необходимо было бы учитывать сдвиг позиций):

       #4  #7
        |  |
        v  v
0)  'xxxxxxxxxx'

1)  'xxxxxx-xxxx'

2)  'xxx) xxx-xxxx'

Наконец, a ( просто сцепляется в самом начале.

Еще одна вещь, которую стоит иметь в виду в связи с этой проблемой, заключается в том, что триггер

AFTER UPDATE , обновляющий ту же таблицу, может привести к повторному срабатыванию, что является феноменом, называемым рекурсией триггера . Рекурсия триггера по умолчанию отключена, но если вы не уверены, что соответствующая опция никогда не изменялась, вы можете проверить ее текущее состояние. Запрос к sys.databases -это один из способов:

SELECT is_recursive_triggers_on
FROM sys.databases
WHERE name = 'your database name'

Поскольку is_recursive_triggers_on -это столбец bit , 0 будет означать off , а 1 — on .

Вышеприведенный триггер разработан таким образом, что

не препятствует его неограниченной рекурсии: даже если вложенные вызовы в какой-то момент перестанут обновлять любые строки (потому что условие LEN(i.phone) IN (7, 10) в конечном итоге станет false ), триггер все равно будет продолжать вызываться . Чтобы исправить это, вы можете просто добавить эту проверку в самом начале:

IF NOT EXISTS (SELECT * FROM inserted)
  RETURN
;

Подробнее о рекурсивных триггерах читайте здесь:

НОУ ИНТУИТ | Лекция | Триггеры: создание и применение

Аннотация: Дается определение триггера, область его использования, место и роль триггера в обеспечении целостности данных. Описываются типы триггеров. Рассматриваются операторы создания, изменения, удаления триггера. Программирование триггера иллюстрируется примерами создания триггеров для реализации ограничений целостности и сбора статистических данных.

Определение триггера в стандарте языка SQL

Триггеры являются одной из разновидностей хранимых процедур. Их исполнение происходит при выполнении для таблицы какого-либо оператора языка манипулирования данными (DML). Триггеры используются для проверки целостности данных, а также для отката транзакций.

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

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

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

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

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

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

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

Основной формат команды CREATE TRIGGER показан ниже:

<Определение_триггера>::=
  CREATE TRIGGER имя_триггера
  BEFORE | AFTER <триггерное_событие>
  ON <имя_таблицы>
  [REFERENCING 
    <список_старых_или_новых_псевдонимов>]
  [FOR EACH { ROW | STATEMENT}]
  [WHEN(условие_триггера)]
  <тело_триггера>

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

Выполняемые триггером действия задаются для каждой строки ( FOR EACH ROW ), охваченной данным событием, или только один раз для каждого события ( FOR EACH STATEMENT ).

Обозначение <список_старых_или_новых_псевдонимов> относится к таким компонентам, как старая или новая строка ( OLD / NEW ) либо старая или новая таблица ( OLD TABLE / NEW TABLE ). Ясно, что старые значения не применимы для событий вставки, а новые – для событий удаления.

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

сложность: при перемещении некоторых функций в базу данных усложняются задачи ее проектирования, реализации и администрирования;
скрытая функциональность: перенос части функций в базу данных и сохранение их в виде одного или нескольких триггеров иногда приводит к сокрытию от пользователя некоторых функциональных возможностей. Хотя это в определенной степени упрощает его работу, но, к сожалению, может стать причиной незапланированных, потенциально нежелательных и вредных побочных эффектов, поскольку в этом случае пользователь не в состоянии контролировать все процессы, происходящие в базе данных;
влияние на производительность: перед выполнением каждой команды по изменению состояния базы данных СУБД должна проверить триггерное условие с целью выяснения необходимости запуска триггера для этой команды. Выполнение подобных вычислений сказывается на общей производительности СУБД, а в моменты пиковой нагрузки ее снижение может стать особенно заметным. Очевидно, что при возрастании количества триггеров увеличиваются и накладные расходы, связанные с такими операциями.

Неправильно написанные триггеры могут привести к серьезным проблемам, таким, например, как появление «мертвых» блокировок. Триггеры способны длительное время блокировать множество ресурсов, поэтому следует обратить особое внимание на сведение к минимуму конфликтов доступа.

Ms sql создание триггера — Вэб-шпаргалка для интернет предпринимателей!

Определение триггеров

Триггеры представляют специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд INSERT, UPDATE, DELETE.

Формальное определение триггера:

Для создания триггера применяется выражение CREATE TRIGGER , после которого идет имя триггера. Как правило, имя триггера отражает тип операций и имя таблицы, над которой производится операция.

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

Затем устанавливается тип триггера. Мы можем использовать один из двух типов:

AFTER : выполняется после выполнения действия. Определяется только для таблиц.

INSTEAD OF : выполняется вместо действия (то есть по сути действие — добавление, изменение или удаление — вообще не выполняется). Определяется для таблиц и представлений

После типа триггера идет указание операции, для которой определяется триггер: INSERT , UPDATE или DELETE .

Для триггера AFTER можно применять сразу для нескольких действий, например, UPDATE и INSERT. В этом случае операции указываются через запятую. Для триггера INSTEAD OF можно определить только одно действие.

И затем после слова AS идет набор выражений SQL, которые собственно и составляют тело триггера.

Создадим триггер. Допустим, у нас есть база данных productsdb со следующим определением:

Определим триггер, который будет срабатывать при добавлении и обновлении данных:

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

Таким образом, триггер будет срабатывать при любой операции INSERT или UPDATE над таблицей Products. Сам триггер будет изменять цену товара, а для получения того товара, который был добавлен или изменен, находим этот товар по Id. Но какое значение должен иметь Id такой товар? Дело в том, что при добавлении или изменении данные сохраняются в промежуточную таблицу inserted. Она создается автоматически. И из нее мы можем получить данные о добавленных/измененных товарах.

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

Удаление триггера

Для удаления триггера необходимо применить команду DROP TRIGGER :

Отключение триггера

Бывает, что мы хотим приостановить действие триггера, но удалять его полностью не хотим. В этом случае его можно временно отключить с помощью команды DISABLE TRIGGER :

А когда триггер понадобится, его можно включить с помощью команды ENABLE TRIGGER :

— это механизм, который вызывается, когда в указанной таблице происходит определенное действие. Каждый триггер имеет следующие основные составляющие: имя, действие и исполнение. Имя триггера может содержать максимум 128 символов. Действием триггера может быть или инструкция DML (INSERT, UPDATE или DELETE), или инструкция DDL. Таким образом, существует два типа триггеров: триггеры DML и триггеры DDL. Исполнительная составляющая триггера обычно состоит из хранимой процедуры или пакета.

Компонент Database Engine позволяет создавать триггеры, используя или язык Transact-SQL, или один из языков среды CLR, такой как C# или Visual Basic.

Создание триггера DML

Триггер создается с помощью инструкции CREATE TRIGGER, которая имеет следующий синтаксис:

Предшествующий синтаксис относится только к триггерам DML. Триггеры DDL имеют несколько иную форму синтаксиса, которая будет показана позже.

Здесь в параметре schema_name указывается имя схемы, к которой принадлежит триггер, а в параметре trigger_name — имя триггера. В параметре table_name задается имя таблицы, для которой создается триггер. (Также поддерживаются триггеры для представлений, на что указывает наличие параметра view_name.)

Также можно задать тип триггера с помощью двух дополнительных параметров: AFTER и INSTEAD OF. (Параметр FOR является синонимом параметра AFTER.) Триггеры типа AFTER вызываются после выполнения действия, запускающего триггер, а триггеры типа INSTEAD OF выполняются вместо действия, запускающего триггер. Триггеры AFTER можно создавать только для таблиц, а триггеры INSTEAD OF — как для таблиц, так и для представлений.

Параметры INSERT, UPDATE и DELETE задают действие триггера. Под действием триггера имеется в виду инструкция Transact-SQL, которая запускает триггер. Допускается любая комбинация этих трех инструкций. Инструкция DELETE не разрешается, если используется параметр IF UPDATE.

Как можно видеть в синтаксисе инструкции CREATE TRIGGER, действие (или действия) триггера указывается в спецификации AS sql_statement.

Компонент Database Engine позволяет создавать несколько триггеров для каждой таблицы и для каждого действия (INSERT, UPDATE и DELETE). По умолчанию определенного порядка исполнения нескольких триггеров для данного модифицирующего действия не имеется.

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

Изменение структуры триггера

Язык Transact-SQL также поддерживает инструкцию ALTER TRIGGER, которая модифицирует структуру триггера. Эта инструкция обычно применяется для изменения тела триггера. Все предложения и параметры инструкции ALTER TRIGGER имеют такое же значение, как и одноименные предложения и параметры инструкции CREATE TRIGGER.

Для удаления триггеров в текущей базе данных применяется инструкция DROP TRIGGER.

Использование виртуальных таблиц deleted и inserted

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

deleted — содержит копии строк, удаленных из таблицы;

inserted — содержит копии строк, вставленных в таблицу.

Структура этих таблиц эквивалентна структуре таблицы, для которой определен триггер.

Таблица deleted используется в том случае, если в инструкции CREATE TRIGGER указывается предложение DELETE или UPDATE, а если в этой инструкции указывается предложение INSERT или UPDATE, то используется таблица inserted. Это означает, что для каждой инструкции DELETE, выполненной в действии триггера, создается таблица deleted. Подобным образом для каждой инструкции INSERT, выполненной в действии триггера, создается таблица inserted.

Инструкция UPDATE рассматривается, как инструкция DELETE, за которой следует инструкция INSERT. Поэтому для каждой инструкции UPDATE, выполненной в действии триггера, создается как таблица deleted, так и таблица inserted (в указанной последовательности).

Таблицы inserted и deleted реализуются, используя управление версиями строк, которое рассматривалось в предыдущей статье. Когда для таблицы с соответствующими триггерами выполняется инструкция DML (INSERT, UPDATE или DELETE), для всех изменений в этой таблице всегда создаются версии строк. Когда триггеру требуется информация из таблицы deleted, он обращается к данным в хранилище версий строк. В случае таблицы inserted, триггер обращается к самым последним версиям строк.

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

Области применения DML-триггеров

Такие триггеры применяются для решения разнообразных задач. В этом разделе мы рассмотрим несколько областей применения триггеров DML, в частности триггеров AFTER и INSTEAD OF.

Триггеры AFTER

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

создания журнала логов действий в таблицах базы данных;

принудительного обеспечения ссылочной целостности.

Создание журнала логов

В SQL Server можно выполнять отслеживание изменения данных, используя систему перехвата изменения данных CDC (change data capture). Эту задачу можно также решить с помощью триггеров DML. В примере ниже показывается, как с помощью триггеров можно создать журнал логов действий в таблицах базы данных:

В этом примере создается таблица AuditBudget, в которой сохраняются все изменения столбца Budget таблицы Project. Изменения этого столбца будут записываться в эту таблицу посредством триггера trigger_ModifyBudget.

Этот триггер активируется для каждого изменения столбца Budget с помощью инструкции UPDATE. При выполнении этого триггера значения строк таблиц deleted и inserted присваиваются соответствующим переменным @budgetOld, @budgetNew и @projectNumber. Эти присвоенные значения, совместно с именем пользователя и текущей датой, будут затем вставлены в таблицу AuditBudget.

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

то содержимое таблицы AuditBudget будет таким:

Реализация бизнес-правил

С помощью триггеров можно создавать бизнес-правила для приложений. Создание такого триггера показано в примере ниже:

Здесь создается правило для управления модификацией бюджетов проектов. Триггер trigger_TotalBudget проверяет каждое изменение бюджетов и выполняет только такие инструкции UPDATE, которые увеличивают сумму всех бюджетов не более чем на 50%. В противном случае для инструкции UPDATE выполняется откат посредством инструкции ROLLBACK TRANSACTION.

Принудительное обеспечение ограничений целостности

В системах управления базами данных применяются два типа ограничений для обеспечения целостности данных: декларативные ограничения, которые определяются с помощью инструкций языка CREATE TABLE и ALTER TABLE; процедурные ограничения целостности, которые реализуются посредством триггеров.

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

В примере ниже показано принудительное обеспечение ссылочной целостности посредством триггеров для таблиц Employee и Works_on:

Триггер trigger_WorksonIntegrity в этом примере проверяет ссылочную целостность для таблиц Employee и Works_on. Это означает, что проверяется каждое изменение столбца Id в ссылочной таблице Works_on, и при любом нарушении этого ограничения выполнение этой операции не допускается. (То же самое относится и к вставке в столбец Id новых значений.) Инструкция ROLLBACK TRANSACTION во втором блоке BEGIN выполняет откат инструкции INSERT или UPDATE в случае нарушения ограничения для обеспечения ссылочной целостности.

В этом примере триггер выполняет проверку на проблемы ссылочной целостности первого и второго случая между таблицами Employee и Works_on. А в примере ниже показан триггер, который выполняет проверку на проблемы ссылочной целостности третьего и четвертого случая между этими же таблицами (эти случаи обсуждались в статье «Transact-SQL — создание таблиц»):

Триггеры INSTEAD OF

Триггер с предложением INSTEAD OF заменяет соответствующее действие, которое запустило его. Этот триггер выполняется после создания соответствующих таблиц inserted и deleted, но перед выполнением проверки ограничений целостности или каких-либо других действий.

Триггеры INSTEAD OF можно создавать как для таблиц, так и для представлений. Когда инструкция Transact-SQL ссылается на представление, для которого определен триггер INSTEAD OF, система баз данных выполняет этот триггер вместо выполнения любых действий с любой таблицей. Данный тип триггера всегда использует информацию в таблицах inserted и deleted, созданных для представления, чтобы создать любые инструкции, требуемые для создания запрошенного события.

Значения столбцов, предоставляемые триггером INSTEAD OF, должны удовлетворять определенным требованиям:

значения не могут задаваться для вычисляемых столбцов;

значения не могут задаваться для столбцов с типом данных timestamp;

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

Эти требования действительны только для инструкций INSERT и UPDATE, которые ссылаются на базовые таблицы. Инструкция INSERT, которая ссылается на представления с триггером INSTEAD OF, должна предоставлять значения для всех столбцов этого представления, не допускающих пустые значения NULL. (То же самое относится и к инструкции UPDATE. Инструкция UPDATE, ссылающаяся на представление с триггером INSTEAD OF, должна предоставить значения для всех столбцов представления, которое не допускает пустых значений и на которое осуществляется ссылка в предложении SET.)

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

В этом примере используется таблица Orders, содержащая два вычисляемых столбца. Представление view_AllOrders содержит все строки этой таблицы. Это представление используется для задания значения в его столбце, которое соотносится с вычисляемым столбцом в базовой таблице, на которой создано представление. Это позволяет использовать триггер INSTEAD OF, который в случае инструкции INSERT заменяется пакетом, который вставляет значения в базовую таблицу посредством представления view_AllOrders. (Инструкция INSERT, обращающаяся непосредственно к базовой таблице, не может задавать значение вычисляемому столбцу.)

Триггеры first и last

Компонент Database Engine позволяет создавать несколько триггеров для каждой таблицы или представления и для каждой операции (INSERT, UPDATE и DELETE) с ними. Кроме этого, можно указать порядок выполнения для нескольких триггеров, определенных для конкретной операции. С помощью системной процедуры sp_settriggerorder можно указать, что один из определенных для таблицы триггеров AFTER будет выполняться первым или последним для каждого обрабатываемого действия. Эта системная процедура имеет параметр @order, которому можно присвоить одно из трех значений:

first — указывает, что триггер является первым триггером AFTER, выполняющимся для модифицирования действия;

last — указывает, что данный триггер является последним триггером AFTER, выполняющимся для инициирования действия;

none — указывает, что для триггера отсутствует какой-либо определенный порядок выполнения. (Это значение обычно используется для того, чтобы выполнить сброс ранее установленного порядка выполнения триггера как первого или последнего.)

Изменение структуры триггера посредством инструкции ALTER TRIGGER отменяет порядок выполнения триггера (первый или последний). Применение системной процедуры sp_settriggerorder показано в примере ниже:

Для таблицы разрешается определить только один первый и только один последний триггер AFTER. Остальные триггеры AFTER выполняются в неопределенном порядке. Узнать порядок выполнения триггера можно с помощью системной процедуры sp_helptrigger или функции OBJECTPROPERTY.

Возвращаемый системной процедурой sp_helptrigger результирующий набор содержит столбец order, в котором указывается порядок выполнения указанного триггера. При вызове функции objectproperty в ее втором параметре указывается значение ExeclsFirstTrigger или ExeclsLastTrigger, а в первом параметре всегда указывается идентификационный номер объекта базы данных. Если указанное во втором параметре свойство имеет значение true, функция возвращает значение 1.

Поскольку триггер INSTEAD OF исполняется перед тем, как выполняются изменения в его таблице, для триггеров этого типа нельзя указать порядок выполнения «первым» или «последним».

Триггеры DDL и области их применения

Ранее мы рассмотрели триггеры DML, которые задают действие, предпринимаемое сервером при изменении таблицы инструкциями INSERT, UPDATE или DELETE. Компонент Database Engine также позволяет определять триггеры для инструкций DDL, таких как CREATE DATABASE, DROP TABLE и ALTER TABLE. Триггеры для инструкций DDL имеют следующий синтаксис:

Как можно видеть по их синтаксису, триггеры DDL создаются таким же способом, как и триггеры DML. А для изменения и удаления этих триггеров используются те же инструкции ALTER TRIGGER и DROP TRIGGER, что и для триггеров DML. Поэтому в этом разделе рассматриваются только те параметры инструкции CREATE TRIGGER, которые новые для синтаксиса триггеров DDL.

Первым делом при определении триггера DDL нужно указать его область действия. Предложение DATABASE указывает в качестве области действия триггера DDL текущую базу данных, а предложение ALL SERVER — текущий сервер.

После указания области действия триггера DDL нужно в ответ на выполнение одной или нескольких инструкций DDL указать способ запуска триггера. В параметре event_type указывается инструкция DDL, выполнение которой запускает триггер, а в альтернативном параметре event_group указывается группа событий языка Transact-SQL. Триггер DDL запускается после выполнения любого события языка Transact-SQL, указанного в параметре event_group. Ключевое слово LOGON указывает триггер входа.

Кроме сходства триггеров DML и DDL, между ними также есть несколько различий. Основным различием между этими двумя видами триггеров является то, что для триггера DDL можно задать в качестве его области действия всю базу данных или даже весь сервер, а не всего лишь отдельный объект. Кроме этого, триггеры DDL не поддерживают триггеров INSTEAD OF. Как вы, возможно, уже догадались, для триггеров DDL не требуются таблицы inserted и deleted, поскольку эти триггеры не изменяют содержимого таблиц.

В следующих подразделах подробно рассматриваются две формы триггеров DDL: триггеры уровня базы данных и триггеры уровня сервера.

Триггеры DDL уровня базы данных

В примере ниже показано, как можно реализовать триггер DDL, чья область действия распространяется на текущую базу данных:

Триггер в этом примере предотвращает удаление любого триггера для базы данных SampleDb любым пользователем. Предложение DATABASE указывает, что триггер trigger_PreventDrop является триггером уровня базы данных. Ключевое слово DROP_TRIGGER указывает предопределенный тип события, запрещающий удаление любого триггера.

Триггеры DDL уровня сервера

Триггеры уровня сервера реагируют на серверные события. Триггер уровня сервера создается посредством использования предложения ALL SERVER в инструкции CREATE TRIGGER. В зависимости от выполняемого триггером действия, существует два разных типа триггеров уровня сервера: обычные триггеры DDL и триггеры входа. Запуск обычных триггеров DDL основан на событиях инструкций DDL, а запуск триггеров входа — на событиях входа.

В примере ниже демонстрируется создание триггера уровня сервера, который является триггером входа:

Здесь сначала создается имя входа SQL Server loginTest, которое потом используется в триггере уровня сервера. По этой причине, для этого имени входа требуется разрешение VIEW SERVER STATE, которое и предоставляется ему посредством инструкции GRANT. После этого создается триггер trigger_ConnectionLimit. Этот триггер является триггером входа, что указывается ключевым словом LOGON.

С помощью представления sys.dm_exec_sessions выполняется проверка, был ли уже установлен сеанс с использованием имени входа loginTest. Если сеанс уже был установлен, выполняется инструкция ROLLBACK. Таким образом имя входа loginTest может одновременно установить только один сеанс.

Триггеры и среда CLR

Подобно хранимым процедурам и определяемым пользователем функциям, триггеры можно реализовать, используя общеязыковую среду выполнения (CLR — Common Language Runtime). Триггеры в среде CLR создаются в три этапа:

Создается исходный код триггера на языке C# или Visual Basic, который затем компилируется, используя соответствующий компилятор в объектный код.

Объектный код обрабатывается инструкцией CREATE ASSEMBLY, создавая соответствующий выполняемый файл.

Посредством инструкции CREATE TRIGGER создается триггер.

Выполнение всех этих трех этапов создания триггера CLR демонстрируется в последующих примерах. Ниже приводится пример исходного кода программы на языке C# для триггера из первого примера в статье. Прежде чем создавать триггер CLR в последующих примерах, сначала нужно удалить триггер trigger_PreventDrop, а затем удалить триггер trigger_ModifyBudget, используя в обоих случаях инструкцию DROP TRIGGER.

Пространство имен Microsoft.SQLServer.Server содержит все классы клиентов, которые могут потребоваться программе C#. Классы SqlTriggerContext и SqlFunction являются членами этого пространства имен. Кроме этого, пространство имен System.Data.SqlClient содержит классы SqlConnection и SqlCommand, которые используются для установления соединения и взаимодействия между клиентом и сервером базы данных. Соединение устанавливается, используя строку соединения «context connection = true».

Затем определяется класс Triggers, который применяется для реализации триггеров. Метод ModifyBudget() реализует одноименный триггер. Экземпляр context класса SqlTriggerContext позволяет программе получить доступ к виртуальной таблице, создаваемой при выполнении триггера. В этой таблице сохраняются данные, вызвавшие срабатывание триггера. Метод IsUpdatedColumn() класса SqlTriggerContext позволяет узнать, был ли модифицирован указанный столбец таблицы.

Данная программа содержит два других важных класса: SqlConnection и SqlCommand. Экземпляр класса SqlConnection обычно применяется для установления соединения с базой данных, а экземпляр класса SqlCommand позволяет исполнять SQL-инструкции.

Программу из этого примера можно скомпилировать с помощью компилятора csc, который встроен в Visual Studio. Следующий шаг состоит в добавлении ссылки на скомпилированную сборку в базе данных:

Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, на основе которого создается триггер CLR. Предложение WITH PERMISSION_SET в примере указывает, что разрешениям доступа присвоено значение SAFE.

Наконец, в примере ниже посредством инструкции CREATE TRIGGER создается триггер trigger_modify_budget:

Инструкция CREATE TRIGGER в примере отличается от такой же инструкции в примерах ранее тем, что она содержит параметр EXTERNAL NAME. Этот параметр указывает, что код создается средой CLR. Имя в этом параметре состоит из трех частей. В первой части указывается имя соответствующей сборки (CLRStoredProcedures), во второй — имя открытого класса, определенного в примере выше (Triggers), а в третьей указывается имя метода, определенного в этом классе (ModifyBudget).

Продолжаем изучать 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 сервера.

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

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

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

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

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

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

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

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

BEGIN

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

END

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

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

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

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

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

И проверяем audit

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

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

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

Создание триггера

Существует три типа триггеров:

INSERT TRIGGER – запускаются при попытке вставки данных с помощью команды INSERT.

UPDATE TRIGGER – запускаются при попытке изменения данных с помощью команды UPDATE.

DELETE TRIGGER – запускаются при попытке удаления данных с помощью команды DELETE.

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

Рисунок 13 – Триггер запрета ввода новой записи по условию

Скрипт создания триггера приведён ниже:

CREATE TRIGGER MoscowHaters

ON Postavshiki

AFTER INSERT

BEGIN

SET NOCOUNT ON;

if (select Firm_city from Postavshiki)=’Moscow’

rollback

print ‘We do not work with Moscow!’

END

При желании добавить данного поставщика будет показано сообщение об ошибке, пример показан на рисунке 14.

Рисунок 14 – сообщение об ошибке посредством триггера 1

Триггер №2. Триггер DDL.

USE CD_Shop

CREATE TRIGGER CantDropMe

ON DATABASE

FOR DROP_TABLE

AS PRINT ‘You cannot delete tables!’

ROLLBACK

Данный триггер предотвращает попытку удаления любой таблицы из базы данных CD_shop, при этом показывая сообщение об ошибке. Пример показан на рисунке 15.

Рисунок 15 – Сообщение об ошибке, вызываемое триггером 2

Создание пользователей

Для БД Магазин компакт дисков были созданы 2 пользователя: Manager и Seller. Создание их в Windows показано на рисунке 16.

Рисунок 16 – Создание пользователей в Windows

Далее в MSSQL был создан пользователь Manager с правами sysadmin (рисунок 17) и пользователь Seller с правами public и схемой по умолчанию datareader (рисунок 18)

Рисунок 17 – Роль sysadmin для пользователя Manager

Рисунок 18 – Назначение прав пользователю Seller внутри SQL Server

При попытке, например, создать новую таблицу, пользователю Seller будет показано сообщение об ошибке, как показано на рисунке 19.

Разграничение прав пользователей позволяет защитить данные, как от несанкционированного доступа, так и от случайных действий определенного круга пользователей.

Рисунок 19 – Сообщение об ошибке для пользователя Seller

Создание триггеров. Обработка баз данных на Visual Basic®.NET

Почему следует избегать триггеров | Windows IT Pro/RE

Ни для кого не секрет, что разработчики не принимают во внимание проблемы масштабируемости, создавая решения, поддерживаемые SQL Server или другими реляционными базами данных. В самом деле, не так давно мне пришлось жаловаться на разработчиков, которые ошибочно считают NoSQL решением всех проблем, связанных с масштабируемостью и производительностью, с которыми они сталкиваются при создании приложений, основанных на реляционных базах данных. Тем не менее, по моему опыту, разработчикам проще всего прочувствовать проблемы, связанные с масштабируемостью, благодаря триггерам.

Проблемы с триггерами

Триггеры представляют собой специализированный код, который срабатывает при инициирующем событии внутри базы данных. В самом SQL Server есть два основных типа триггеров: триггеры Data Definition Language, или языка определения данных (DDL), и Data Manipulation Language, или языка управления данными (DML). Триггеры DDL, как следует из названия, работают в том случае, когда выполняются события языка определения данных (например, создание нового года, структур, имен входа и т.д.). Этот вид триггеров мы здесь рассматривать не будем. Нас интересуют триггеры DML, которые выполняются при изменении данных или управлении ими, в контексте перечисленных ниже проблем.

Неизвестность. При отладке или устранении неисправностей, а также при поиске причины ошибки или изменений данных внутри сложных систем, триггеры слишком удобны для разработчиков (и даже администраторов), чтобы забывать о них. Представим себе ситуацию: разработчики знают, что определенной части приложения или кода поручена обработка, скажем, вставка в конкретной таблице, и продолжают сталкиваться с некоторыми конечными значениями, отличными от тех, что были при отправке. Обычно в таких случаях предполагают, что есть некая ошибка в коде, — пока не вспомнят, что триггер, развернутый когда-то в прошлом, просто молчаливо наблюдает за всеми INSERT и изредка управляет ими, выравнивая данные по бизнес-правилам, которые уже не актуальны. Эта же проблема с триггерами типа «развернуть и забыть» может, в свою очередь, доставить немало хлопот при устранении неполадок.
Сложности. Помимо того, что существуют веские основания избегать включения бизнес-логики в базу данных, факт остается фактом: триггеры на самом деле сложнее, чем кажется. Например, одна из распространенных проблем с триггерами заключается в том, что разработчики, их создающие, мыслят в рамках одной строки, модифицируемой в триггере. Также нередко разработчики ошибочно создают триггеры с неверной скалярной семантикой, что вызывает проблемы, когда оператор вроде UPDATE затрагивает несколько строк. Еще один вопрос, должен ли триггер DML быть запущен до, после или вместо DML — в результате труднее определить, что происходит, когда есть триггеры.
Производительность и масштабируемость. Триггеры, по определению, выполняются всякий раз, когда работает операция DML. С помощью логики и дополнительной фильтрации определяется, должен ли триггер игнорировать, блокировать или модифицировать DML при попытках изменения. Кроме того, свойства DELETED и INSERTED для псевдотаблиц работают, и нередко можно наблюдать операции с триггерами, требующими более гранулированных и дорогих блокировок, чем обычно. Другими словами, таблица без триггеров всегда будет вести себя лучше, чем таблица с триггерами — это ключевой фактор, когда речь идет о масштабируемости. Хотя триггер может прекрасно работать на небольших базах данных, по мере того, как количество данных и таблиц увеличивается, триггеры выполняют все больше блокировок, повышают нагрузку, снижают производительность и вызывают проблемы с масштабируемостью. Действительно, в некоторых самых сложных случаях, с которыми я когда-либо сталкивался, были со всей тщательностью устроенные триггеры, которые только усугубляли ситуацию.

Когда использовать триггеры

Учитывая, что триггеры сложны и велика вероятность потерпеть неудачу при устранении неполадок и столкнуться с серьезными проблемами в крупных параллельных системах, возникает вопрос: какова их роль в системе? Лично я не сторонник их применения для чего бы то ни было. По большому счету, основная часть логики, которую они реализуют, может быть перемещена в хранимые процедуры или в любой код, уже служащий для модифицирования данных. На мой взгляд, использование триггеров сигнализирует о системной или архитектурной проблеме, поскольку разработчики зачастую начинают применять триггеры в качестве «горячей клавиши», чтобы обеспечить выполнение бизнес-правил с использованием таблицы вместо модифицирующего кода, «захватывающей» всю логику и все функции. В некотором отношении это равносильно попытке написать пре- и пост-фильтры к веб-запросам для вставок или изменений верхних и нижних колонтитулов HTML, вместо того чтобы найти код создания этих объектов и модифицировать его, если необходимо.

Если отвлечься от перечисленных соображений, можно найти одно место, где использование триггеров имеет смысл: это аудит. Аудит необходим для соблюдения законодательных требований, которые обычно слишком обширны для триггеров и ими, как правило, должны заниматься дорогостоящие решения аудита сторонних фирм. Однако для простых сценариев, где вам нужно следить за изменением данных в определенных строках, можно настроить простые триггеры, которые будут захватывать и направлять строки INSERTED, DELETED и UPDATED в таблицу TableName_Audit. Это позволит отслеживать значения и метаданные о том, кто и когда выполнил операцию, без обременительных расходов. Это вовсе не означает, что использование триггеров избавит вас от всех проблем, но, на мой взгляд, это единственная область, где они сыграют свою роль. Если вы все же захотите использовать триггеры, поймите, что операции INSERT, UPDATE, а также DELETE и MERGE изменят единовременно более чем одну строку. Представьте, что ваши аудиторские таблицы будут становиться все больше, а времени на их заполнение строками в конечном итоге потребуется слишком много, если индексация этих таблиц аудита должным образом не оптимизирована для операторов INSERT. В целом, я настоятельно рекомендую не использовать триггеры, учитывая проблемы, которые просто неизбежны.

Поделитесь материалом с коллегами и друзьями

Как создать триггер Oracle

Как создать триггер для определения времени изменения данных Oracle. Вы можете каждый раз генерировать данные средствами PHP, Python, но триггер сделает всё автоматический. У триггеров есть интересное качество — их можно отключать. Как создать триггер в оракле. Тут не сложно. Вам нужно пролезть в свою схему и вставить код SQL: CREATE TRIGGER…END. Содержимое триггера может быть любым. Их часто используют для увеличения ID (автоинкремента) и для определения времени изменения записи.


/*создание триггера через консоль SQL*/
CREATE TRIGGER схема.таблица_название_триггера
  BEFORE INSERT OR UPDATE ON схема.таблица
  FOR EACH ROW
BEGIN
  :new.changed := sysdate;
END;

/*через редактор*/
TRIGGER схема.название_триггера
  BEFORE INSERT OR UPDATE ON схема.таблица
  FOR EACH ROW
BEGIN
  /*новое значение.changed = системному времени*/
  :new.changed := sysdate;
END;

/*готовый пример*/
TRIGGER scheme1.table_trigger_changed
  BEFORE INSERT OR UPDATE ON схема.таблица
  FOR EACH ROW
BEGIN
  :new.changed := sysdate;
END;

Если вы делаете триггер для увеличения ID на единицу, то в настройках выделите галочкой настройку INSERT. INSERT — эта настройка включает триггер после вставки записи. При изменении записи триггер не сработает. Это важно. Изменений может быть сотня, но создание уникальной записи выполняется чаще всего один раз. Я воспользовался последовательностью SEQUENCES (AUTONUMBER) для создания автоинкремента. О том как содать последовательность читайте в статье: Как создать последовательность SEQUENCES (AUTONUMBER) в Oracle


/*создание триггера через консоль SQL*/
create or replace TRIGGER схема.название_триггера 
before insert on схема.таблица
for each row
begin
  if :new.id is null then
    /*
      SEQ_ID - sequences  последовательность для автонумерации
      nextval - следующее значение. происходит прибавление 1
      id - столбец в таблице
    */
    select SEQ_ID.nextval into :new.id from dual;
  end if;
end;

/*готовый пример*/
create or replace TRIGGER scheme1.name_trigger1
before insert on scheme1.table1
for each row
begin
  if :new.id is null then
    select SEQ_ID.nextval into :new.id from dual;
  end if;
end;

просмотры: 3708, уровень: лёгкий уровень, рейтинг: 0, дата: 2017-12-05 08:56:37

CREATE TRIGGER — База знаний MariaDB

Синтаксис

 СОЗДАТЬ [ИЛИ ЗАМЕНИТЬ]
    [DEFINER = {пользователь | CURRENT_USER | роль | ТЕКУЩАЯ РОЛЬ }]
    ТРИГГЕР [ЕСЛИ НЕ СУЩЕСТВУЕТ] имя_ триггера время_пуска_событие_события
    НА tbl_name ДЛЯ КАЖДОЙ СТРОКИ
   [{СЛЕДУЕТ | PRECEDES} other_trigger_name]
   trigger_stmt;

Описание

Этот оператор создает новый триггер. Триггер — это именованная база данных объект, связанный с таблицей, который активируется, когда для таблицы происходит определенное событие.Триггер становится связанным с таблицей с именем имя_таблицы , которая должна относиться к постоянной таблице. Вы не можете связать триггер с таблицей TEMPORARY или представлением.

CREATE TRIGGER требует привилегии TRIGGER для связанной таблицы с курком.

MariaDB, начиная с 10.2.3

У вас может быть несколько триггеров для одного и того же trigger_time и trigger_event .

Информацию о допустимых идентификаторах для использования в качестве имен триггеров см. В разделе «Имена идентификаторов».

ИЛИ ЗАМЕНИТЬ

MariaDB, начиная с 10.1.4

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

DEFINER

Предложение DEFINER определяет контекст безопасности, который будет использоваться, когда проверка прав доступа во время активации триггера. Для использования требуется привилегия SUPER или, начиная с MariaDB 10.5.2, привилегия SET USER.

ЕСЛИ НЕ СУЩЕСТВУЕТ

MariaDB начиная с 10.1.4

Если используется предложение IF NOT EXISTS , триггер будет создан только в том случае, если триггер с таким же именем не существует. Если триггер уже существует, по умолчанию будет возвращено предупреждение.

trigger_time

trigger_time — время срабатывания триггера. Это может быть ДО или ПОСЛЕ до указывают, что триггер активируется до или после каждой строки, которая будет изменен.

trigger_event

trigger_event указывает тип оператора, который активирует курок. trigger_event может быть одним из следующих:

INSERT : триггер активируется всякий раз, когда в таблицу вставляется новая строка; например, с помощью операторов INSERT, LOAD DATA и REPLACE.
ОБНОВЛЕНИЕ : триггер активируется при изменении строки; например, с помощью операторов UPDATE.
DELETE : триггер активируется всякий раз, когда строка удаляется из таблицы; например, с помощью операторов DELETE и REPLACE.Однако операторы DROP TABLE и TRUNCATE в таблице не активируют этот триггер, поскольку они не используют DELETE . Удаление раздела также не активирует триггеры DELETE .

ПОДПИСЫВАЕТСЯ / ПРЕДЫДУЩАЕТСЯ other_trigger_name

MariaDB, начиная с 10.2.3

Параметры FOLLOWS other_trigger_name и PRECEDES other_trigger_name были добавлены в MariaDB 10.2.3 как часть поддержки нескольких триггеров за время действия.Это тот же синтаксис, который используется в MySQL 5.7, хотя MySQL 5.7 не имеет поддержки нескольких триггеров.

FOLLOWS добавляет новый триггер после другого триггера, а PRECEDES добавляет новый триггер перед другим триггером. Если ни одна из опций не используется, новый триггер добавляется последним для данного действия и времени.

FOLLOWS и PRECEDES не сохраняются в определении триггера. Однако порядок срабатывания гарантированно не изменится со временем.mariadb-dump / mysqldump и другие методы резервного копирования не изменяют порядок запуска. Вы можете проверить порядок запуска из столбца ACTION_ORDER в таблице INFORMATION_SCHEMA.TRIGGERS.

 ВЫБЕРИТЕ имя_триггера, порядок_действия из схемы_информации.triggers
  ГДЕ event_object_table = 't1';

Примеры

 CREATE DEFINER = `root` @` localhost` TRIGGER increment_animal
  ПОСЛЕ ВСТАВКИ НА животных ДЛЯ КАЖДОГО РЯДА
   ОБНОВЛЕНИЕ animal_count SET animal_count.animals = animal_count.животные + 1;

ИЛИ ЗАМЕНИТЬ И ЕСЛИ НЕ СУЩЕСТВУЕТ

 CREATE DEFINER = `root` @` localhost` TRIGGER increment_animal
  ПОСЛЕ ВСТАВКИ НА животных ДЛЯ КАЖДОГО РЯДА
    ОБНОВЛЕНИЕ animal_count SET animal_count.animals = animal_count.animals + 1;
ОШИБКА 1359 (HY000): триггер уже существует.

СОЗДАТЬ ИЛИ ЗАМЕНИТЬ DEFINER = `root` @` localhost` TRIGGER increment_animal
  ПОСЛЕ ВСТАВКИ НА животных ДЛЯ КАЖДОГО РЯДА
    ОБНОВЛЕНИЕ animal_count SET animal_count.animals = animal_count.animals + 1;
Запрос в порядке, затронуто 0 строк (0,12 секунды)

CREATE DEFINER = `root` @` localhost` ТРИГГЕР, ЕСЛИ НЕ СУЩЕСТВУЕТ, increment_animal
  ПОСЛЕ ВСТАВКИ НА животных ДЛЯ КАЖДОГО РЯДА
    ОБНОВИТЬ animal_count УСТАНОВИТЬ animal_count.животные = animal_count.animals + 1;
Запрос выполнен, затронуты 0 строк, 1 предупреждение (0,00 сек)

ПОКАЗАТЬ ПРЕДУПРЕЖДЕНИЯ;
+ ------- + ------ + ------------------------ +
| Уровень | Код | Сообщение |
+ ------- + ------ + ------------------------ +
| Примечание | 1359 | Триггер уже существует |
+ ------- + ------ + ------------------------ +
1 ряд в комплекте (0,00 сек)

См. Также

Как создавать и использовать триггеры DML в SQL Server на реальных примерах

Перво-наперво, немного предыстории о триггерах.Несмотря на то, что их часто клевещут, в определенных обстоятельствах они могут быть очень полезны … когда вы их узнаете. Например, отправка электронного письма с SQL Server может выполняться с помощью триггера, основанного на каком-то ожидаемом событии.

В этой статье мы сосредоточимся на случае изменения данных T-SQL, когда оператор Insert, Update или Delete попадает в данные в нашей таблице. Когда имя применяется, триггер срабатывает, и затем мы можем принимать решения о том, что делать дальше, на основе новых поступающих данных или старых данных, которые будут заменены.Это то, для чего чаще всего используются триггеры.

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

С точки зрения производительности, лучше всего делать триггеры маленькими и простыми. Как и в программировании, при создании триггера руководствуйтесь логикой: чем меньше, тем лучше.

В этой статье мы рассмотрим три примера использования триггера с использованием различных комбинаций операций, например типы вставки и триггера, например ВМЕСТО, в том числе:

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

Обзор триггеров DML

Следует упомянуть, что существуют также триггеры DLL и CLR, которые также являются особым типом объектов T-SQL, но здесь основное внимание уделяется DML (языку манипулирования данными), таким как инструкции Insert, Update и Delete.

Типы триггеров

ПОСЛЕ — триггеры срабатывают по завершении выполнения операторов DML.
ПОСЛЕ указывает, что триггер DML срабатывает только после успешного выполнения всех операций, указанных в операторе SQL. Все ссылочные каскадные действия и проверки ограничений также должны быть успешными до срабатывания этого триггера. Триггеры AFTER не могут быть определены для представлений.
INSTEAD OF — триггеры срабатывают перед операторами DML.
INSTEAD OF указывает, что триггер DML выполняется до того, как оператор DML полностью переопределит действия операторов запуска.Триггеры INSTEAD OF нельзя указать для DDL или входа в систему.

Хватит разговоров, давайте посмотрим, как создать простые триггеры DML для операторов Insert и Update, а затем еще один триггер для оператора Delete в важной таблице, который будет действовать как защита от фатальных действий и предотвращать удаление данных пользователем.

Триггер отслеживания / аудита изменений (обновление)

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

Вы можете создать простую таблицу, которая соответствует вашим потребностям, но ниже приведен пример таблицы, используемой в этом случае:

 ЕГЭ AdventureWorks2014;
ИДТИ
ЕСЛИ OBJECT_ID ('Покупка.StandardPriceHistory ',' U ') НЕ НУЛЬ
    ВЫБИРАЮЩАЯ ТАБЛИЦА Purchasing.StandardPriceHistory;
ИДТИ
СОЗДАТЬ ТАБЛИЦУ Purchasing.StandardPriceHistory
(PriceHistoryID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
 ProductID INT НЕ NULL,
 ПредыдущаяЦена DECIMAL (19, 4),
 NewPrice DECIMAL (19, 4),
 PriceChangeDate DATETIME NOT NULL
)
НА [ПЕРВИЧНОМ];
ИДТИ

Теперь, когда таблица создана, мы можем приступить к созданию триггера. Пример вышеописанного триггера показан ниже:

 ЕГЭ AdventureWorks2014;
ИДТИ
ЕСЛИ OBJECT_ID ('Покупка.uStandardPriceHistory ',' TR ') НЕ НУЛЬ
    DROP TRIGGER Purchasing.uStandardPriceHistory;
ИДТИ
СОЗДАТЬ ТРИГГЕР uStandardPriceИстория при покупке.
ДЛЯ ОБНОВЛЕНИЯ
В ВИДЕ
     ВСТАВИТЬ В Purchasing.StandardPriceHistory
(PriceHistoryID,
 Идантификационный номер продукта,
 Предыдущая цена,
 Новая цена,
 PriceChangeDate
)
            ВЫБЕРИТЕ NEWID (),
                   pv.ProductID,
                   d.StandardPrice,
                   i.StandardPrice,
                   GETDATE ()
            ОТ Закупки.ProductVendor pv
                 ПРИСОЕДИНЯЙТЕСЬ ВСТАВЛЕНО i НА pv.ProductID = i.ProductID
                 СОЕДИНЕНИЕ УДАЛЕНО d НА pv.ProductID = d.ProductID;
ИДТИ

Этот триггер создается в таблице Purchasing.ProductsVendor для оператора Update, и он в основном делает снимок столбца Price старой цены и новой цены и помещает его в ранее созданную таблицу ProductPriceHistory . чтобы это произошло.

После выполнения приведенного выше кода, если мы перейдем к таблице ProductsVendor и развернем папку Triggers, там должен быть наш вновь созданный триггер. При срабатывании он запишет старые данные, которые заменяются новыми, и вставит эту информацию в таблицу ProductPriceHistory :

Теперь все, что нам нужно сделать, это выполнить оператор обновления для таблицы ProductsVendor , чтобы увидеть, как это работает. Вместо того, чтобы писать код и потому что это быстрее, используйте опцию Edit Top 200 Rows , щелкнув правой кнопкой мыши таблицу в Object Explorer, которая откроет новую вкладку в Editor:

Здесь у нас есть продукт с ID 1, который имеет значение 47.87. Допустим, товар продается, и измените его, введя новое значение 39,99 и нажав клавишу Enter :

Это действие аналогично написанию оператора обновления. Он выдает инструкцию в фоновом режиме и, если с введенными данными все в порядке, обновляет записи. В противном случае появится информационное сообщение о том, что пошло не так.

Выписка прошла, и быстрый выбор из таблицы, используемой для хранения отслеживаемых данных, показывает изменения:

Уведомление о новой записи (триггер вставки)

Далее идет триггер Insert.Давайте использовать оператор Insert в сочетании с триггером в качестве механизма уведомления. На самом деле мы собираемся отправить электронное письмо с помощью триггера Insert. Идея этого, например, чтобы клиенты знали, когда появится новый продукт. Предварительным условием здесь является правильная установка и настройка почты базы данных.

Примечание. Если вы новичок в Database Mail, это корпоративное решение для отправки сообщений электронной почты из ядра СУБД SQL Server, и настоятельно рекомендуется ознакомиться со следующей подробной статьей о том, как настроить это и устранить известные проблемы, которые может произойти: Как настроить почту базы данных в SQL Server

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

 ЕГЭ AdventureWorks2014;
ИДТИ
IF OBJECT_ID ('Производство.iProductNotification ',' TR ') НЕ ПУСТО
    DROP TRIGGER Purchasing.iProductNotification;
ИДТИ
СОЗДАТЬ TRIGGER iProductNotification ON Production.Product
ДЛЯ ВСТАВКИ
В ВИДЕ
     ОБЪЯВЛЕНИЕ @ProductInformation NVARCHAR (255);
     SELECT @ProductInformation = 'Новый продукт' + Name + 'теперь доступен за $' + CAST (StandardCost AS NVARCHAR (20)) + '!'
     ОТ ВСТАВЛЕННОГО i;
     EXEC msdb.dbo.sp_send_dbmail
          @profile_name = 'Уведомления',
          @recipients = 'Использовать действующий адрес электронной почты',
          @body = @ProductInformation,
          @subject = 'Уведомление о новом продукте';
ИДТИ

На этот раз мы прикрепляем спусковой крючок к Production.Изделие табл. Обратите внимание, что на этот раз триггером является оператор FOR INSERT. Вы также можете использовать AFTER INSERT, поскольку for и after — это в основном одно и то же. Это одна из тех вещей, которые имеют обратную совместимость. Один был с самого начала, а потом добавили еще один.

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

Краткое описание этих двух событий: следует иметь в виду, что триггеры «Обновить» и «Вставить» принадлежат к группе триггеров AFTER. В обоих приведенных выше примерах они срабатывают после завершения операторов DML.

Защитный кожух (удаление триггера)

Переходя к триггеру удаления , мы можем показать пример триггеров INSTEAD OF, которые срабатывают до операторов DML.Давайте посмотрим, что мы можем сделать с этим типом триггера.

Так как этот триггер может предотвратить выполнение оператора DML, почему бы не использовать его в качестве «меры защиты» от случайной потери данных для важной информации. Пример описан и показан в другой статье, в которой также исследуются альтернативные способы предотвращения случайной потери данных: Как предотвратить случайную потерю данных при выполнении запроса в SQL Server, также известного как «Практика безопасного программирования»

Хорошо, мы настоятельно рекомендуем проверить код из статьи выше, но давайте также покажем здесь еще один пример.У Microsoft действительно есть действительно хороший триггер в базе данных AdventureWorks2014. Перейдите в обозреватель объектов и найдите триггер dEployee в таблице HumanResources.Employee или воспользуйтесь удобной функцией «Перейти к объекту» в ApexSQL Complete, чтобы быстро найти объект и выделить его в обозревателе объектов:

Щелкните правой кнопкой мыши триггер dEmployee и перейдите к триггеру сценария как > CREATE To , а затем щелкните команду New Query Editor Window :

Это действие откроет новую вкладку в редакторе со следующим кодом:

 ЕГЭ [AdventureWorks2014];
ИДТИ



УСТАНОВИТЬ ANSI_NULLS ON;
ИДТИ
ВКЛЮЧИТЬ QUOTED_IDENTIFIER;
ИДТИ
СОЗДАТЬ ТРИГГЕР [HumanResources].[dEmployee] ON [HumanResources]. [Employee]
ВМЕСТО УДАЛЕНИЯ
НЕ ДЛЯ РЕПЛИКАЦИИ
В ВИДЕ
         НАЧИНАТЬ
             ОБЪЯВИТЬ @Count INT;
             УСТАНОВИТЬ @Count = @@ ROWCOUNT;
             ЕСЛИ @Count = 0
                 ВОЗВРАЩАТЬСЯ;
             УСТАНОВИТЬ NOCOUNT ON;
             НАЧИНАТЬ
                 RAISERROR (N'Employees не могут быть удалены. Их можно только пометить как устаревшие. ', 10, 1);
                         ЕСЛИ @@ TRANCOUNT> 0
                     НАЧИНАТЬ
                         ОТКАТНАЯ СДЕЛКА;
                     КОНЕЦ;
             КОНЕЦ;
         КОНЕЦ;
ИДТИ
ИЗМЕНИТЬ ТАБЛИЦУ [HumanResources].[Сотрудник] ВКЛЮЧИТЬ ТРИГГЕР [dEmployee];
ИДТИ
EXEC sys.sp_addextendedproperty
     @name = N'MS_Description ',
     @value = N'INSTEAD OF DELETE триггер, предотвращающий удаление сотрудников. ',
     @ level0type = N'SCHEMA ',
     @ level0name = N'HumanResources ',
     @ level1type = N'TABLE ',
     @ level1name = N'Employee ',
     @ level2type = N'TRIGGER ',
     @ level2name = N'dEmployee ';
ИДТИ

Итак, у нас здесь происходит несколько вещей.Во-первых, не весь код нужен, однако это прекрасный пример триггера, в котором есть все. У нас есть триггер INSTEAD OF для оператора Delete, что означает, что, когда Delete попадает в данные в нашей таблице, триггер сначала выполняет код, который проверяет данные перед их фактическим выполнением, и дает нам возможность откатить вещи.

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

Если мы подошли к моменту, когда фактически был выполнен оператор Delete и были затронуты фактические строки, функция RAISERROR покажет определяемое пользователем сообщение. Чтобы увидеть это в действии, снова используйте команду Edit Top 200 Rows в таблице HumanResources.Employee :

Выберите любую строку, как показано ниже, и нажмите клавишу Delete на клавиатуре. Появится диалоговое окно подтверждения предупреждения.Нажмите кнопку Да , чтобы продолжить действие:

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

Здесь срабатывает функция ROLLBACK TRANSACTION, чтобы стереть все изменения данных, сделанные с начала транзакции.

То же произойдет и с редактором. Помните, что приведенный выше пример и приведенный ниже код делают то же самое:

Возможные ошибки

« С большой силой приходит большая ответственность, » часто цитируется из слов дяди Бена из фильма «Человек-паук».Теперь, когда мы убедились в истинной силе триггеров DML, давайте вернемся к самому началу статьи, где мы упоминали рекурсивный триггер и триггеры вложенного цикла. Мы пропустим примеры, так как цель этой статьи — лучше понять, как триггеры могут быть полезны в целом, и увидеть, когда, где и как их использовать, а не показать обратную сторону. Но также важно хотя бы кратко описать возможные минусы.

Вложенные триггеры

Вложенные триггеры — это случаи, когда триггер из одной таблицы выполняет действие, которое инициирует другой триггер в другой таблице и т. Д.Более того, если триггер из второй таблицы обращается к нашей таблице и обновляет данные, то мы получаем бесконечный цикл. К счастью для нас, в SQL Server есть защита, и триггеры могут быть вложены до 32 уровней (триггеры INSTEAD OF могут быть вложенными независимо от настройки этого параметра). Этот параметр включен по умолчанию в SQL Server, и это означает, что если этот предел вложенности превышен, триггер завершает работу.

Чтобы проверить параметр вложенных триггеров, в обозревателе объектов щелкните базу данных правой кнопкой мыши и выберите Свойства .На странице Options есть опция Nested Triggers Enabled , и для нее должно быть установлено значение True :

Обратите внимание, что этот параметр неактивен, что означает, что его нельзя настроить в графическом интерфейсе. Если по какой-либо причине этот параметр отключен, что не рекомендуется, за исключением некоторых редких случаев, выполните следующий код, чтобы включить его:

 ЕГЭ AdventureWorks, 2014 г.
ИДТИ

EXEC sp_configure 'вложенные триггеры', 1;
ИДТИ
ПЕРЕКОНФИГУРИРОВАТЬ;
ИДТИ

Настройка вступает в силу немедленно, без перезапуска сервера.Если вы столкнулись с ошибкой, скорее всего, это расширенный вариант. Добавьте приведенный ниже код перед системной хранимой процедурой, чтобы исправить это:

 ЕГЭ AdventureWorks2014;
ИДТИ
EXEC sp_configure 'показать дополнительные параметры', 1;
ИДТИ
ПЕРЕКОНФИГУРИРОВАТЬ;
ИДТИ

Рекурсивные триггеры

Рекурсивные триггеры — это случаи, когда триггер вызывает себя повторно. Обычно это происходит, когда внутри триггера нет никакой логики, которая могла бы прервать рекурсию.Это также известно как бесконечный цикл. К счастью, рекурсивные триггеры в SQL Server по умолчанию отключены. Как и раньше, вы можете установить этот флажок в обозревателе объектов, щелкнуть базу данных правой кнопкой мыши и выбрать Свойства . На странице Options есть опция Recursive Triggers Enabled , для которой установлено значение False :

Если установлено значение Ложь , предотвращается только прямая рекурсия. Этот параметр можно изменить прямо из графического интерфейса:

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

Автоматическое создание триггера на основе шаблона

ApexSQL Trigger — это инструмент аудита для баз данных SQL Server, который фиксирует изменения DML, произошедшие в базе данных. Итак, давайте посмотрим, как реализовать аудит SQL на основе триггеров, используя более сложную версию нашего триггера обновления для отслеживания / аудита изменений, с помощью этого инструмента за несколько шагов.

Запуск триггера ApexSQL, нажмите кнопку New , чтобы создать новый проект. Откроется диалоговое окно «Новый проект», в котором мы выберем сервер, метод аутентификации и базу данных, в которых мы хотим отслеживать изменения. После установки нажмите кнопку OK , чтобы перейти к следующему шагу:

Затем приложение отобразит диалоговое окно об отсутствующей архитектуре. Это нормально, поскольку это приложение впервые подключается к этой базе данных. Это важный шаг, поскольку приложение требует определенной архитектуры объектов SQL, установленных до начала аудита (аналогично созданию нашей таблицы ранее).Выберите вариант по умолчанию и нажмите кнопку Да , чтобы установить архитектуру:

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

Запуск сценария занимает всего несколько секунд, и после успешного завершения нажмите кнопку OK , чтобы закрыть окно и вернуться в окно «Управление архитектурой»:

Закройте это окно, так как оно нам больше не нужно, и вы увидите главное окно приложения:

Найдите закупку .В таблице ProductsVendor установите флажок слева, и вы увидите все столбцы под панелью «Столбцы». Проверьте столбец StandardPrice , а затем нажмите кнопку Create , чтобы создать триггер:

Нажмите кнопку Execute в окне Script, чтобы создать триггер:

Затем вы должны увидеть сообщение о том, что все операции завершены и сценарий выполнен успешно:

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

Если мы вернемся в обозреватель объектов и обновим папку триггеров, ApexSQL Trigger создаст три новых триггера:

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

Теперь мы можем удалить триггер uStandardPriceHistory , поскольку он нам больше не нужен. Также помните, что наличие нескольких триггеров в одной таблице может привести к снижению производительности (особенно в этом случае, когда два триггера делают одно и то же).Мы также должны удалить ранее созданную таблицу Purchasing.StandardPriceHistory , поскольку она бесполезна без триггера.

Допустим, товара больше нет в продаже, измените цену на прежнее значение, используя шаги, описанные ранее, чтобы мы могли видеть, как просматривать изменения. В отличие от написания оператора Select и извлечения данных из таблицы вручную, ApexSQL Trigger делает это путем создания настраиваемых пользователем отчетов.

Нажмите кнопку Standard в группе отчетов:

В следующем окне просто нажмите кнопку Применить , и изменение будет показано ниже:

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

Если вы примените метод из статьи выше, Database Mail будет отправлять электронные письма обо всех изменениях DML в таблице. В нашем случае он нам нужен только для оператора Insert. Пользователь может свободно редактировать и изменять шаблоны, что означает возможность вставки собственного кода для триггера.В этом случае мы можем просто создать новый триггер Insert и отредактировать существующий шаблон, поместив наш фрагмент кода, чтобы триггер не только записывал изменения, но также выполнял хранимую процедуру sp_send_dbmail и отправлял электронное письмо.

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

 ОБЪЯВИТЬ @Count int
    УСТАНОВИТЬ @Count = @@ ROWCOUNT;
         
    ЕСЛИ @Count> = (ВЫБРАТЬ СУММ (количество_строк)
         ОТ sys.dm_db_partition_stats
         ГДЕ OBJECT_ID = OBJECT_ID ('Продажи.Клиент')
         И index_id = 1)
    НАЧИНАТЬ
         RAISERROR ('Невозможно удалить все строки', 16,1)
         ОТКАТНАЯ СДЕЛКА
         ВОЗВРАЩАТЬСЯ;
КОНЕЦ

Отредактированный шаблон выглядит так:

Выполнение отредактированного кода создает триггер, предотвращающий уничтожение всей таблицы:

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

Надеюсь, эта статья окажется для вас полезной. Для получения дополнительной информации о триггере ApexSQL посетите страницу продукта

Полезные ссылки

12 января 2018 г.

Как создать и запустить триггер SQL Server

При правильной реализации триггеры SQL Server представляют собой мощный механизм для добавления полезных функций в базу данных без необходимости изменения какого-либо кода приложения.

В этой статье я рассмотрю, что такое триггер SQL Server, шаблон для определения нового триггера и возможные варианты использования.

Несколько фактов

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

Пару ключевых отличий можно резюмировать следующим образом.

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

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

Триггеры являются частью схемы базы данных так же, как ограничения и индексы прикрепляются к таблице. В результате вы можете рассчитывать на то, что логика, содержащаяся в триггере, будет надежно выполняться без зависимости от каких-либо других движущихся частей. Это отличается от подобных заданий агента SQL Server, которые требуют, чтобы служба агента SQL Server работала для их успешного выполнения.

Существуют специальные логические таблицы «Вставленные» и «Удаленные», к которым можно получить доступ в контексте триггера, и они дают триггерам возможность видеть, какие изменения были внесены, и принимать решения на основе этой дополнительной информации.

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

Обратите внимание, что существуют триггеры DML (язык управления данными), которые могут запускаться в командах INSERT , UPDATE или DELETE , а также триггеры DDL (язык определения данных), которые могут запускаться в CREATE , ALTER и DROP команд.

Я расскажу о триггерах DML в следующих разделах.

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

Шаблон триггера

Базовая схема структуры SQL, необходимая для создания триггера, может быть определена следующим образом.

 CREATE TRIGGER TriggerName
НА dbo.TableName
ДЛЯ ОБНОВЛЕНИЯ
В ВИДЕ
НАЧИНАТЬ
    - операторы SQL.
КОНЕЦ

В первых двух строках триггеру дается имя и указывается таблица, к которой он будет прикреплен.

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

В третьей строке определяется тип триггера.Это может быть одно из следующих.

В сочетании с одним из следующих.

например ДЛЯ ОБНОВЛЕНИЯ или ПОСЛЕ ВСТАВКИ ( ДЛЯ и ПОСЛЕ ведут себя одинаково).

Обратите внимание, что вы также можете комбинировать ключевые слова DML с запятыми, например FOR INSERT, UPDATE , чтобы триггер срабатывал как для вставки, так и для обновления.

Я упустил некоторые дополнительные параметры, которые вы можете указать при создании триггера, но в моем опыте они не так часто используются, поэтому я оставил их для простоты.Вы можете проверить все доступные параметры в Microsoft Docs.

Практический пример

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

Прежде чем продолжить, загрузите базу данных AdventureWorks (если у вас ее еще нет) и восстановите ее в подходящий экземпляр SQL Server, к которому у вас есть соответствующий доступ.

В только что восстановленной базе данных AdventureWorks добавьте новый столбец к стандартному dbo.Таблица DimProduct с использованием приведенного ниже оператора SQL ALTER .

 ALTER TABLE dbo.DimProduct ADD StockLevel INT NOT NULL ПО УМОЛЧАНИЮ 0;

Обратите внимание, что я указал тип столбца как INT для простоты в нашем примере.

Теперь создайте новую таблицу, которая будет использоваться для хранения контрольного журнала изменений уровня запасов. Выполните приведенный ниже сценарий SQL CREATE .

 СОЗДАТЬ ТАБЛИЦУ [dbo]. [DimProductStockLevelAudit] (
[ProductKey] [int] НЕ NULL,
[ProductAlternateKey] [nvarchar] (25) NULL,
[OldStockLevel] [int] НЕ NULL,
[NewStockLevel] [int] НЕ NULL,
[DateChanged] [datetime] НЕ ПУСТО
) НА [ОСНОВНОЙ]

Обратите внимание, что столбец ProductAlternateKey , по сути, является уникальным кодом продукта или идентификатором продукта, как определено в dbo.Стол DimProduct .

Теперь о триггере…

 СОЗДАТЬ ТРИГГЕР StockLevelAudit
ПО dbo.DimProduct
ДЛЯ ОБНОВЛЕНИЯ
В ВИДЕ
НАЧИНАТЬ
    - Обрабатывать только в том случае, если уровень запасов был обновлен.
    ЕСЛИ ОБНОВЛЕНИЕ (StockLevel)
    НАЧИНАТЬ
- Вставка с использованием SELECT гарантирует, что мы зафиксируем все изменения уровня запасов.
- Триггеры срабатывают один раз на команду, которая вносит изменения в таблицу.
ВСТАВИТЬ В dbo.DimProductStockLevelAudit
(Ключ продукта,
ProductAlternateKey,
OldStockLevel,
NewStockLevel,
DateChanged)
ВЫБРАТЬ
я.Ключ продукта,
i.ProductAlternateKey,
d.StockLevel,
i.StockLevel,
GETDATE ()
FROM Inserted i - «Inserted» содержит копии новых значений, которые были обновлены в виртуальной таблице.
JOIN Deleted d ON i.ProductKey = d.ProductKey - «Deleted» содержит копии старых значений, которые были заменены.
ГДЕ i.StockLevel <> d.StockLevel - записывать изменения только в том случае, если уровень запасов отличается.
    КОНЕЦ
КОНЕЦ

Вышеупомянутый триггер следует тому же шаблону, что и базовый шаблон, описанный в предыдущем разделе.

Сначала триггеру присваивается имя, например StockLevelAudit, затем указывается имя таблицы, к которой нужно присоединить триггер, например, dbo.DimProduct .

Триггер срабатывает всякий раз, когда для таблицы выполняется команда UPDATE в соответствии с оператором FOR UPDATE .

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

Затем мы вставляем записи в таблицу dbo.DimProductStockLevelAudit , извлекая интересующие нас значения столбцов из логических таблиц «Вставлено» и «Удалено».

Таблица «Удалено» присоединяется к таблице «Вставлено» в столбце ProductKey.

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

Пробные стрельбы

После создания триггера попробуйте обновить столбец StockLevel в dbo.Стол DimProduct для нескольких продуктов.

Вы должны увидеть запись в таблице dbo.DimProductStockLevelAudit для каждого значения уровня запасов, которое было обновлено.

Пример результатов аудита триггеров

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

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

Перезарядка…

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

ВМЕСТО Триггеры также могут быть полезны, и, как следует из названия, они выполняются вместо применения результатов команды, которая их инициировала. Это очень мощная концепция, но ее следует применять с осторожностью, поскольку триггер INSTEAD OF полностью отменяет исходную команду, которая его запустила.

В заключение, триггеры SQL Server — это еще один полезный инструмент в вашем арсенале администратора баз данных, который применим к множеству различных сценариев.

Наконец, в дополнение к основному сценарию, включенному в эту статью, у меня есть ряд других сценариев SQL, которые могут оказаться полезными в моем репозитории сценариев SQL на GitHub.

Создание триггеров SQL в SAP HANA — процесс, который нельзя игнорировать!

Продолжая нашу серию руководств по SAP HANA DataFlair, эта статья посвящена изучению триггеров SQL.Мы узнаем об основах триггеров SQL, их значении в SAP HANA, синтаксисе для создания триггеров в сценарии SQL и процессе создания триггеров в SAP HANA Studio.

Триггеры SQL в SAP HANA

Триггеры SQL концептуально аналогичны хранимым процедурам, поскольку триггеры представляют собой набор операторов SQL или хранимых программ, которые автоматически выполняются в ответ на событие. В SAP HANA вы можете создавать триггеры для таблиц, представлений, схем или баз данных. Программы-триггеры выполняются или запускаются, когда операция INSERT, UPDATE или DELETE имеет место в предметной таблице или предметном представлении.

В SAP HANA для заданной тематической таблицы или представления пользователь должен включить привилегию TRIGGER , чтобы иметь возможность создавать триггеры для этой конкретной таблицы или представления. Как правило, триггеры выполняются в ответ на три типа операторов SQL:

Оператор управления базой данных (оператор DML), такой как DELETE, INSERT или UPDATE .
Оператор определения базы данных (оператор DDL), например CREATE , ALTER или DROP .
Операторы операций с базой данных, такие как LOGON , STARTUP , SHUTDOWN или SERVERERROR .

Изучите различные типы аналитических привилегий в SAP HANA

Ключевые использования триггеров SQL в SAP HANA

В первую очередь мы можем использовать триггеры SQL в SAP HANA для следующих целей:

Для операций аудита.
Для синхронной репликации таблиц данных.
Для хранения информации о доступе к таблице.
Процессы авторизации безопасности
Для предотвращения недействительных транзакций.
Процессы регистрации событий
Для обеспечения целостности ссылок.
Для автоматического создания производных значений столбца.

Синтаксис для создания триггеров SQL

Мы создаем триггеры SQL с помощью команды CREATE TRIGGER . Полный синтаксис:

 CREATE TRIGGER   
НА <имя_таблицы_предмета> [ССЫЛКА на <список_переходов>]
[]
НАЧИНАТЬ
[]
[]

END

Элементы синтаксиса для создания триггеров SQL

Двумя важными элементами синтаксиса для создания триггеров являются и .

— это имя триггера, назначаемого пользователем. Вы должны создать триггер с именем триггера вместе с именем рабочей схемы.
указывает время, в которое триггер должен быть выполнен. Есть три утверждения времени действия триггера; ПЕРЕД , ПОСЛЕ и ВМЕСТО .

1. BEFORE дает команду системе выполнить триггер перед выполнением оператора DML.

2. ПОСЛЕ дает команду системе выполнить триггер после выполнения оператора DML.

3. INSTEAD OF дает команду системе выполнить ее вместо выполнения оператора DML.

Не забудьте проверить! — Заявления SQL в SAP HANA

Как создавать триггеры SQL в SAP HANA Studio

В этом разделе мы узнаем, как создавать и использовать триггеры в SAP HANA Studio.

Шаг 1: Откройте SAP HANA Studio и убедитесь, что выбрана перспектива Консоль администрирования .Войдите в систему базы данных SAP HANA и разверните узел Каталог .

Шаг 2: Откройте схему, в которой вы хотите создать триггер. После развертывания узла схемы по вашему выбору вы получите список объектов, доступных в этой схеме. Также существует папка с именем « Triggers », в которой содержатся все триггеры, созданные в соответствии со схемой.

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

Шаг 3: Теперь, чтобы создать триггер SQL , откройте SQL Editor , щелкнув имя системы, а затем щелкнув значок редактора SQL (присутствует на панели).

В редакторе SQL мы создадим операторы SQL для триггера. Это утверждение выглядит следующим образом:

 Создать триггер DTF.DTFTRIGGER
После вставки «DTF». «DIMEMPLOYEE» для каждой строки
Начинать
ВСТАВИТЬ В «DTF». «DTFAUDIT»
ЗНАЧЕНИЯ
(
CURRENT_TIMESTAMP, CURRENT_USER
)
;
Конец
;

В этом наборе операторов первый оператор присваивает имя текущему клиенту, следующий оператор решает, что триггер сработает после выполнения операции в таблице DIMEMPLOYEE .

Фрагмент кода, заключенный в операторы Begin и End, представляет действие, которое триггер будет выполнять при выполнении. В этом случае потребуется метка времени и имя пользователя, который сделал запись в таблице DIMEMPLOYEE . Эта информация будет сохранена в другой таблице, DTFAUDIT .

Шаг 4: Выполните этот оператор, щелкнув зеленую кнопку «Выполнить» на верхней панели. Триггер создаст. Вы можете проверить это в папке Triggers , имя нового триггера должно отображаться.

Шаг 5: Теперь, чтобы проверить триггер, перейдите к таблице данных, с которой связан наш триггер. Щелкните правой кнопкой мыши имя таблицы и выберите Open Content .

Будет доступно содержимое таблицы.

Мы добавим данные в новую строку этой таблицы и выполним оператор. Оператор SQL для добавления новой строки:

 Вставить в «DTF». Значение «DIMEMPLOYEE» (125, 30, 5, «DataFlair», «Indore», «MP»)

Будет успешно добавлена новая строка с идентификатором сотрудника 125 и соответствующими данными.

Теперь, согласно нашему триггеру, мы должны получить информацию об этой новой записи записи в таблице DTFAUDIT .

В этой таблице вы получите метку времени и имя пользователя, выполнившего новую запись в таблице DIMEMPLOYEE . Это показывает, что триггер сработал успешно.

Сводка

На этом мы завершаем наше руководство по триггерам SAP HANA SQL. Мы надеемся, что наше объяснение было полезным для вас, и вы поняли концепцию триггеров SQL и то, как создавать триггеры в консоли администрирования SAP HANA.

Есть вопросы или отзывы для нас? Поделитесь своими мыслями в разделе комментариев.

Проверьте, как компания Kellogg повысила свою производительность с помощью SAP HANA, в последнем примере использования SAP HANA

Знаете ли вы, что мы работаем круглосуточно, чтобы предоставить вам лучшие учебные материалы
Пожалуйста, поддержите нас — напишите отзыв на Google | Facebook

Введение в триггеры в SQL

Триггеры SQL Server используются для выполнения после или перед операцией INSERT, DELETE или UPDATE в таблице.Вы можете использовать эти триггеры SQL в представлениях или таблицах для выполнения любых из указанных выше действий. Помните, что вы можете связать триггер только с одной таблицей.

Типы триггеров в SQL Server

В SQL Server существует два типа триггеров: AFTER TRIGGERS и INSTEAD OF TRIGGERS.

Для демонстрации триггеров Sql Server мы работаем с таблицей сотрудников и таблицей аудита сотрудников

ПОСЛЕ ТРИГГЕРОВ в SQL Server

Триггеры после / для в SQL запускаются после INSERT, DELETE или UPDATE в таблице.Это означает, что перед запуском триггера должны выполняться все операции, а также оператор должен успешно пройти проверку ограничений.

После того, как триггеры в SQL Server не поддерживаются в представлениях, используйте их только в таблицах. Далее его можно разделить на

AFTER INSERT TRIGGERS: Этот триггер срабатывает после завершения операции Insert в таблице Employee. Как только он завершит вставку в таблицу Employee, он начнет вставку в таблицу аудита.Скажем, если он не вставлен в таблицу сотрудников, он не будет вставлен в таблицу аудита.
ТРИГГЕРЫ ПОСЛЕ ОБНОВЛЕНИЯ: этот триггер SQL Server срабатывает после завершения операции обновления в таблице «Сотрудники». Как только он закончит обновление таблицы сотрудников, он начнет вставку / обновление в таблицу аудита. Например, если не удается обновить таблицу «Сотрудники», она не будет вставлена в таблицу аудита.
ТРИГГЕРЫ ПОСЛЕ УДАЛЕНИЯ: Триггер после удаления срабатывает после завершения операции удаления в таблице сотрудников.Как только он завершит удаление записей из сотрудников, он начнет вставку / удаление из таблицы аудита. Скажем, если его не удастся удалить из таблицы Employee, он не будет вставлен в таблицу аудита.

INSTEAD OF TRIGGERS в SQL Server

SQL Server вместо триггеров, запускаемых перед выполнением INSERT, DELETE или UPDATE при запуске таблицы.

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

INSTEAD OF INSERT TRIGGERS: Этот триггер вместо вставки срабатывает перед запуском операции Insert для таблицы Employee. Как только он завершит вставку в таблицу аудита сотрудников, он начнет вставку в таблицу сотрудников. И если по какой-то причине он не работает, он не будет вставлен в таблицу Employee.
ВМЕСТО ТРИГГЕРОВ ОБНОВЛЕНИЯ: этот триггер обновления срабатывает перед обновлением записей в таблице сотрудников. Как только он завершит выполнение триггера, он начнет обновлять записи в таблице Employee.А если не удастся, таблица не обновится.
ВМЕСТО ТРИГГЕРОВ УДАЛЕНИЯ: Этот триггер удаления срабатывает перед началом удаления записей из таблицы сотрудников. После успешного выполнения триггера он начнет удалять записи из таблицы «Сотрудники». А в случае сбоя никакая запись из таблицы Employee не удаляется.

Синтаксис триггеров сервера Sql

Синтаксис после триггеров в SQL Server:

 - Создание триггеров в SQL Server
СОЗДАТЬ [ИЛИ ИЗМЕНИТЬ] ТРИГГЕР [имя_схемы].Trigger_Name
На столе
ПОСЛЕ ВСТАВКИ | ОБНОВЛЕНИЕ | УДАЛИТЬ
В ВИДЕ
   НАЧИНАТЬ
      - Заявления о запуске
      - Вставка, обновление или удаление заявлений
   КОНЕЦ

Schema_name: укажите имя схемы. Например, dbo или Human Resource и т. Д.
Trigger_Name: Вы можете указать любое имя, которое хотите дать, кроме зарезервированных системой ключевых слов. Пожалуйста, старайтесь использовать значимые имена, чтобы вы могли легко их идентифицировать.

Основной синтаксис вместо триггеров в SQL Server показан ниже:

 - Создание триггеров в SQL Server
СОЗДАТЬ [ИЛИ ИЗМЕНИТЬ] ТРИГГЕР [имя_схемы].Trigger_Name
ON Table | Вид
ВМЕСТО ВСТАВКИ | ОБНОВЛЕНИЕ | УДАЛИТЬ
В ВИДЕ
   НАЧИНАТЬ
      - Заявления о запуске
      - Вставка, обновление или удаление заявлений
   КОНЕЦ

Давайте посмотрим, как создать триггер, изменить существующие триггеры и удалить триггеры в SQL Server на примере.

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

и наша таблица аудита также пуста

Создание триггеров в SQL Server, пример

Триггеры можно создавать двумя способами:

Создание триггеров SQL в SQL Server Management Studio

В этом примере мы покажем вам этапы создания триггера с помощью Management Studio (SSMS).Прежде чем мы начнем генерировать какой-либо триггер, давайте посмотрим, есть ли в нашей базе данных какие-либо триггеры или нет.

Чтобы просмотреть существующие триггеры, выберите База данных -> Разверните имя таблицы (в которой существует триггер) -> Перейдите в папку триггеров и разверните ее. На приведенном ниже снимке экрана вы можете заметить, что наша таблица Employee в базе данных [SQL Tutorial] не имеет триггеров.

Щелкните правой кнопкой мыши папку «Триггеры», чтобы открыть контекстное меню. Пожалуйста, выберите Новые триггеры.. вариант от него.

После того, как вы нажмете на кнопку «Новые триггеры…», откроется новое окно запроса с шаблоном триггера по умолчанию, как показано ниже.

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

Создание триггеров SQL с помощью SQL-запроса

Давайте посмотрим, как создавать триггеры в SQL Server с помощью оператора SQL Create Trigger.

Для демонстрации этих триггеров SQL мы создаем триггер после вставки для вставки записей в аудит таблицы сотрудников вместе с информацией аудита.

 - Триггеры в SQL Server: введение
ИСПОЛЬЗУЙТЕ [Учебное пособие по SQL]
ИДТИ

СОЗДАТЬ ТРИГГЕР triggers_in_sql
   ON EmployeeTable
   ПОСЛЕ ВСТАВКИ
В ВИДЕ
НАЧИНАТЬ
     - Добавлен SET NOCOUNT ON, чтобы предотвратить появление дополнительных наборов результатов.
     - вмешательство в операторы SELECT.УСТАНОВИТЬ NOCOUNT ON;

     - Вставьте здесь инструкции для триггера
ВСТАВИТЬ В [EmployeeTableAudit] (
       [Я БЫ]
      ,[Имя]
      ,[Образование]
      ,[Занятие]
      ,[Годовой доход]
      ,[Продажи]
      ,[Имя сервера]
      , [ServerInstanceName]
      , [Insert Time])
ВЫБЕРИТЕ ID,
Имя,
Образование,
Занятие,
Годовой доход,
Продажи,
CAST (SERVERPROPERTY ('MachineName') AS VARCHAR (50)),
CAST (СВОЙСТВО СЕРВЕРА ('ServerName') КАК VARCHAR (50)),
GETDATE ()
ОТ ВСТАВЛЕННОГО;
PRINT 'Мы успешно активировали триггеры AFTER INSERT в SQL Server.'
КОНЕЦ
ИДТИ

Мы уже объясняли операторы внутри триггеров SQL в нашей предыдущей статье. Я предлагаю вам сослаться на статью AFTER INSERT Triggers под этим кодом.

Позвольте мне показать вам только что созданный триггер, открыв Обозреватель объектов -> Перейти к учебной базе данных SQL -> Перейти и развернуть таблицу сотрудников ->, а затем развернуть папку триггеров

Позвольте мне вставить 5 случайных записей в таблицу Employee в демонстрационных целях. Это поможет нам проверить, срабатывает ли триггер After Insert Trigger или нет.

 ИСПОЛЬЗОВАНИЕ [Учебное пособие по SQL]
ИДТИ
ВСТАВИТЬ В [Таблица сотрудников] (
[Имя]
,[Образование]
,[Занятие]
,[Годовой доход]
,[Продажи]
)
ЗНАЧЕНИЯ ('Tutorial Gateway', 'Masters Degree', 'Admin', 250000, 1900)
      , ('Aamir Khan', 'Bachelors', 'Skilled Professional', 69000, 100)
      , ('Лара Диас', 'Бакалавры', 'Менеджмент', 85000, 60)
      , («Махеш кумар», «Степень», «Профессионал», 45000, 630)
      , ('Руис Джон', 'Аспирант', 'Канцелярия', 40000, 220)

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

 ИСПОЛЬЗОВАНИЕ [Учебное пособие по SQL]
ИДТИ
ВЫБЕРИТЕ [ID]
      ,[Имя]
      ,[Образование]
      ,[Занятие]
      ,[Годовой доход]
      ,[Продажи]
  ОТ [EmployeeTable]

Затем проверьте записи в таблице аудита, используя следующий запрос.

 ИСПОЛЬЗОВАНИЕ [Учебное пособие по SQL]
ИДТИ

ВЫБЕРИТЕ [ID]
      ,[Имя]
      ,[Образование]
      ,[Занятие]
      ,[Годовой доход]
      ,[Продажи]
      ,[Имя сервера]
      , [ServerInstanceName]
      , [Вставить время]
  ОТ [EmployeeTableAudit]

Изменение триггеров в SQL Server, пример

Следующие примеры помогут вам понять шаги, необходимые для изменения существующих триггеров SQL с помощью SQL Management Studio и Transact-SQL Query.

Пример изменения триггеров SQL с использованием SSMS

Прежде чем мы перейдем к модификации SQL Trigger, позвольте мне показать вам список доступных опций, которые предоставляет вам Management Studio. Как показано ниже, вы можете отбросить его, отбросить и воссоздать, изменить и создать.

Чтобы изменить триггер с помощью Management Studio, перейдите к таблице, содержащей триггер. Затем выберите триггер, который вы хотите изменить (triggers_in_sql), и щелкните его правой кнопкой мыши, чтобы открыть контекстное меню.Здесь выберите опцию Modify

После выбора опции «Изменить» откроется новое окно запроса с автоматически сгенерированным кодом ALTER TRIGGER. Вы можете изменить его в соответствии с вашими требованиями.

Использование Alter Trigger для изменения триггеров сервера Sql, пример

Шаги, связанные с изменением существующих триггеров с помощью оператора ALTER TRIGGER. Для этого нажмите «Новый запрос» и верните следующий запрос.

 ALTER TRIGGER [dbo]. [Triggers_in_sql]
   ВКЛ [dbo]. [EmployeeTable]
   ПОСЛЕ ВСТАВКИ
В ВИДЕ
НАЧИНАТЬ
 - Измените в соответствии с вашими требованиями
КОНЕЦ

Удаление триггеров в SQL Server, пример

В следующих примерах показано, как отбросить или удалить триггеры с помощью запросов Transact-SQL и Management Studio (SSMS).

Удалить триггер в Management Studio

Здесь мы покажем вам, как удалить триггер с помощью SSMS. Чтобы удалить триггер с помощью SSMS, щелкните правой кнопкой мыши имя триггера, которое вы хотите удалить, откроется контекстное меню. Вы можете нажать на опцию Удалить

Удалить триггер SQL с помощью DROP TRIGGER

Мы покажем вам, как удалять триггеры с помощью оператора Drop Trigger.

 ИСПОЛЬЗОВАНИЕ [Учебное пособие по SQL]
ИДТИ

DROP TRIGGER [dbo]. [Triggers_in_sql]

Триггеров SQL Server: хорошее и страшное

Триггеры

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

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

Хотя все демонстрации в этой статье относятся к SQL Server, представленные здесь советы универсальны для всех платформ баз данных.Проблемы, создаваемые триггерами, также наблюдаются в MySQL, PostgreSQL, MongoDB и многих других.

Что такое триггеры?

Триггеры

SQL Server могут быть определены на сервере, в базе данных или в таблице и позволяют коду автоматически выполняться при возникновении определенных действий. В этой статье основное внимание уделяется триггерам DML для таблиц, поскольку они, как правило, несут основную тяжесть чрезмерного использования. Напротив, триггеры DDL (языка определения данных) в базе данных или сервере обычно более целенаправленны и менее вредны для производительности.

Триггер — это набор кода, который оценивается при изменении данных в таблице. Триггеры могут быть определены для выполнения при INSERT , UPDATE , DELETE или любой комбинации этих операций. Операции MERGE запускают триггеры на основе операций, выполняемых каждой операцией в операторе MERGE.

Триггеры могут быть определены как INSTEAD OF или AFTER операции записи. ПОСЛЕ триггеры срабатывают после записи данных в таблицу и представляют собой отдельный и отдельный набор операций, которые выполняются как часть той же транзакции, которая была записана в таблицу, но после этого происходит запись.Если триггер не работает, исходная операция также не выполняется. INSTEAD Триггеры OF заменяют вызывающую операцию записи. В этих сценариях операции INSERT , UPDATE или DELETE никогда не выполняются, а вместо этого выполняется содержимое триггера. Каждый из них может быть ценным в зависимости от обстоятельств и типа кода, которым нужно управлять с помощью триггеров.

В конечном итоге триггеры позволяют выполнять TSQL при выполнении операций записи, независимо от источника этих операций записи.Обычно они используются для выполнения критических операций, таких как ведение журнала, проверка или дополнительный DML в сценариях, где есть желание гарантировать, что добавленный код всегда выполняется. Это удобно, поскольку источник кода не имеет значения. Это может быть API, код приложения, сценарий выпуска или внутренний процесс, и триггеры будут срабатывать независимо.

Как выглядят триггеры?

Рассмотрим таблицу Sales.Orders в образце базы данных WideWorldImporters .Представьте, что возникла необходимость регистрировать все действия по обновлению или удалению в этой таблице вместе с некоторыми подробностями о том, кто и когда произошло изменение. Это действие может быть выполнено с помощью хранимой процедуры или кода, но для этого потребуются изменения в каждом месте кода, записываемого в таблицу.

Чтобы решить эту проблему с помощью триггеров, можно предпринять следующие шаги:

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

33 34

СОЗДАТЬ ТАБЛИЦУ Продажи.Orders_log

(Orders_log_ID int NOT NULL IDENTITY (1,1)

CONSTRAINT PK_Sales_Orders_log PRIMARY KEY CLUSTERED,

OrderID int NOT NULL,

CustomerID_Old int NOT NULL,

CustomerID_New int NOT NULL,

SalespersonPersonID_New int NOT NULL,

PickedByPersonID_Old int NULL,

PickedByPersonID_New int NULL,

ContactPersonID_Old int NOT NULL,

ContactPersonID_D_New int NOT NULL_ORDER

NULL,

OrderDate_New date NOT NULL,

ExpectedDeliveryDate_Old date NOT NULL,

ExpectedDeliveryDate_New date NOT NULL,

CustomerPurchaseOrderNumber_Old nvarchar (20) NULL,

CustomerPurchasenumber (20) NULL,

CustomerPurchaseNumber Backordered_Old bit NOT NULL,

IsUndersupplyBackordered_New bit NOT NULL,

Comments_Old nvarchar (max) NULL,

Comments_New nvarchar (max) NULL,

DeliveryInstructions_Old nvarchar (max) NULLInstructions,

Delivery InternalComments_Old nvarchar (max) NULL,

InternalComments_New nvarchar (max) NULL,

PickingCompletedWhen_Old datetime2 (7) NULL,

PickingCompletedWhen_New datetime2 (7) NULL,

LastEdited NOT8 NULL_NULL

LastEdited NOT8 NULL_Old

LastEditedWhen_Old datetime2 (7) NOT NULL,

LastEditedWhen_New datetime2 (7) NOT NULL,

ActionType VARCHAR (6) NOT NULL,

ActionTime DATETIME2 (3) NOT NULL,

UserName VARCHAR) (128);

В этой таблице регистрируются старые и новые значения для всех столбцов.Это очень тщательно и реалистично, мы могли бы просто регистрировать старую версию строк и иметь возможность делать выводы об изменениях, объединяя вместе новую и старую версии. Выбор остается за разработчиком, чтобы определить, являются ли дополнительные столбцы приемлемыми или расточительными, и это решение будет варьироваться в зависимости от каждой ситуации. Последние 3 столбца являются новыми и содержат контекст относительно типа выполненного действия ( INSERT , UPDATE или DELETE ), времени и того, какой пользователь это сделал.

2.Создайте триггер для регистрации изменений в новой таблице:

33 34

9 0008 64

СОЗДАТЬ ТРИГГЕР TR_Sales_Orders_Audit

ON Продажи.Заказы

ПОСЛЕ ВСТАВКИ, ОБНОВЛЕНИЯ, УДАЛЕНИЯ

КАК

НАЧАТЬ

УСТАНОВИТЬ БЕЗ СЧЕТА;

INSERT INTO Sales.Orders_log

(КодЗаказа, CustomerID_Old, CustomerID_New,

SalespersonPersonID_Old, SalespersonPersonID_New,

PickedByPersonID_Old, PickedByPersonID_New,

ContactPersonID_Old, ContactPersonID_New,

BackorderOrderID_Old, BackorderOrderID_New,

OrderDate_Old, OrderDate_New, ExpectedDeliveryDate_Old,

ExpectedDeliveryDate_New,

CustomerPurchaseOrderNumber_Old,

CustomerPurchaseOrderNumber_New,

IsUndersupplyBackordered_Old,

IsUndersupplyBackordered_New,

комментариев_Old_Old 0008 PickingCompletedWhen_Old,

PickingCompletedWhen_New, LastEditedBy_Old,

LastEditedBy_New, LastEditedWhen_Old,

LastEditedWhen_New, ActionType, ActionTime, UserName

SELECT IS.OrderID, Deleted.OrderID) AS OrderID,

Deleted.CustomerID AS CustomerID_Old,

Inserted.CustomerID AS CustomerID_New,

Удалено.

Inserted.PickedByPersonID AS PickedByPersonID_New,

Deleted.ContactPersonID AS ContactPersonID_Old,

Inserted.ContactPersonID А.С. ContactPersonID_New,

Deleted.BackorderOrderID А.С. BackorderOrderID_Old,

Inserted.BackorderOrderID А.С. BackorderOrderID_New,

Deleted.OrderDate А.С. OrderDate_Old,

Inserted.OrderDate А.С. OrderDate_New,

Deleted.ExpectedDeliveryDate А.С. ExpectedDeliveryDate_Old,

Поставлен. ExpectedDeliveryDate

AS ExpectedDeliveryDate_New,

Удалено.CustomerPurchaseOrderNumber

AS CustomerPurchaseOrderNumber_Old,

Inserted.CustomerPurchaseOrderNumber AS CustomerPurchaseOrderNumber_New,

Deleted.IsUndersupplyBackordered AS IsUndersupplyBackordered_Old,

Inserted.IsUndersupplyBackordered AS IsUndersupplyBackordered_New,

Deleted.Comments AS Comments_Old,

вставленной.Комментарии AS Comments_New,

Deleted.DeliveryInstructions

AS DeliveryInstructions_Old,

Inserted.DeliveryInstructions

AS DeliveryInstructions_New,

Deleted.InternalComments AS InternalComments_Old,

Deleted_InternalComments8

Вставлено.PickingCompletedWhen

А.С. PickingCompletedWhen_New,

Deleted.LastEditedBy А.С. LastEditedBy_Old,

Inserted.LastEditedBy А.С. LastEditedBy_New,

Deleted.LastEditedWhen А.С. LastEditedWhen_Old,

Inserted.LastEditedWhen А.С. LastEditedWhen_New,

ПРИМЕР, КОГДА Inserted.OrderID IS NULL ТОГДА ‘DELETE’

WHEN Deleted.OrderID IS NULL THEN ‘INSERT’

ELSE ‘UPDATE’

END AS ActionType,

SYSUTCDATETIME () ActionTime,

SUSER_SNAME () Deleted AS UserName3

FULL AS UserName

FROED как UserName

FROED

ON Вставлено.OrderID = Deleted.OrderID;

КОНЕЦ

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

3. Последний шаг — проверить и подтвердить правильность заполнения таблицы журнала.Ниже приведен тест UPDATE в таблице после добавления триггера:

UPDATE Orders

SET InternalComments = ‘Товар больше не отсортирован’,

BackorderOrderID = NULL,

IsUndersupplyBackordered = 0,

LastEditedBy = 1,

LastEditedWhenIME = SYSUTCDAT13

ГДЕ Заказы.OrderID = 10;

Выбор * из Sales.Orders_log показывает результаты выполнения триггера:

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

ВСТАВЛЕННЫЕ и УДАЛЕННЫЕ таблицы

В предыдущем примере данные, используемые в таблице журнала, были прочитаны из INSERTED и DELETED .Это специальные таблицы, которые доступны как часть любой соответствующей операции записи. Операции INSERT будут включать таблицу INSERTED , операции DELETE будут включать таблицу DELETED , а операции UPDATE будут включать таблицы INSERTED и DELETED .

Для операций INSERT и UPDATE таблица INSERTED будет включать снимок новых значений для каждого столбца в таблице.Для операций DELETE и UPDATE таблица DELETED будет содержать снимок старых значений для каждого столбца в таблице до операции записи.

Для операций INSERT таблица DELETED будет существовать, но не будет содержать данных. Точно так же для операций DELETE таблица INSERTED не будет содержать никаких данных. Благодаря этому в триггере можно программно определить, какая операция вызова относится к таблице.Если DELETED не содержит данных, то операция — INSERT ; если INSERTED не содержит данных, то операция DELETE ; если обе таблицы содержат данные, то операция ОБНОВЛЕНИЕ .

Эти таблицы исключительно удобны, поскольку они предоставляют способ доступа к данным, затронутым операцией записи, без необходимости возвращаться к базовой таблице и запрашивать ее. Обратите внимание, что INSERTED и DELETED находятся в tempdb и имеют любые ограничения по пространству или производительности, которые tempdb имеет на данном SQL Server.

Поскольку триггеры INSTEAD OF INSERT действуют на данные, которые еще не были записаны в таблицу, значения идентичности еще не были определены. Если таблица содержит значение идентификатора, тогда оно будет NULL в пределах триггера INSTEAD OF INSERT .

Когда триггеры наиболее полезны?

Оптимальное использование триггеров DML — это короткие, простые и легкие в обслуживании операции записи, которые действуют в значительной степени независимо от бизнес-логики приложения.

Вот некоторые отличные варианты использования триггеров:

Регистрация изменений в таблице истории
Аудит пользователей и их действий с конфиденциальными таблицами.
Добавление дополнительных значений в таблицу, которые могут быть недоступны для приложения (из-за ограничений безопасности или других ограничений), например:
- Логин / имя пользователя
- Время выполнения операции
- Имя сервера / базы данных
Простая проверка.

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

Чтобы триггеры работали эффективно, они должны быть написаны так, чтобы они основывались на наборах. Если триггер содержит циклы ( WHILE или CURSOR ) или хранимые процедуры со скалярными параметрами, то операции с несколькими строками будут принудительно работать с данными по одной строке за раз.

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

СОЗДАТЬ ТРИГГЕР TR_Sales_Orders_Process

ON Продажи.Заказы

ПОСЛЕ ВСТАВКИ

КАК

НАЧАТЬ

УСТАНОВИТЬ БЕЗ СЧЕТА;

DECLARE @count INT;

SELECT @count = COUNT (*) FROM вставлено;

DECLARE @min_id INT;

SELECT @min_id = MIN (OrderID) FROM вставлен;

DECLARE @current_id INT = @min_id;

WHILE @current_id <@current_id + @count

BEGIN

EXEC dbo.process_order_fulfillment

@OrderID = @current_id;

ВЫБРАТЬ @current_id = @current_id + 1;

КОНЕЦ

Хотя относительно просто, производительность INSERT операций против продаж.Заказы пострадают при одновременной вставке нескольких строк, поскольку SQL Server будет вынужден выполнять итерацию одну за другой при выполнении хранимой процедуры process_order_fulfillment . Простое исправление — переписать хранимую процедуру и этот код, чтобы передавать набор идентификаторов заказов в хранимую процедуру, а не делать это по одному:

СОЗДАТЬ ТИП dbo.udt_OrderID_List КАК ТАБЛИЦА (

OrderID INT NOT NULL,

ПЕРВИЧНЫЙ КЛЮЧ КЛАСТЕРИРОВАН

(OrderID ASC));

CREATE TRIGGER TR_Sales_Orders_Process

ON Sales.Заказы

ПОСЛЕ ВСТАВКИ

КАК

НАЧАТЬ

УСТАНОВИТЬ БЕЗ СЧЕТА;

DECLARE @OrderID_List dbo.udt_OrderID_List;

EXEC dbo.process_order_fulfillment @OrderIDs = @OrderID_List;

КОНЕЦ

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

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

Когда опасны триггеры?

Триггеры похожи на Pringles: когда ты хлопаешь, ты не можешь остановиться. Одна из самых больших проблем для архитекторов и разработчиков — обеспечить использование триггеров только по мере необходимости и не позволить им стать универсальным решением для любых возникающих потребностей в данных. Добавление TSQL к триггерам часто считается более быстрым и простым, чем добавление кода в приложение, но затраты на это со временем увеличиваются с каждой добавленной строкой кода.

Триггеры могут стать опасными, когда:

Их слишком много. Используйте как можно меньше триггеров, чтобы минимизировать сложность.
Код триггера становится сложным. Если обновление строки в таблице приводит к выполнению тысяч строк добавленного кода триггера, разработчикам становится трудно полностью понять, что происходит, когда данные записываются в таблицу. Хуже того, устранение неполадок может быть очень сложной задачей, когда что-то идет не так.
Триггеры переходят между серверами.Это вводит сеть в операции триггера и может привести к замедлению или сбоям записи при возникновении проблем с подключением. Даже триггеры между базами данных могут быть проблематичными, если целевая база данных подлежит обслуживанию.
Запускает триггеры вызова. Наибольшая проблема триггеров возникает, когда вставляется одна строка, и эта операция записи приводит к выполнению 100 триггеров в 75 таблицах. При написании кода триггера убедитесь, что триггеры могут выполнять всю необходимую логику, не вызывая дополнительных триггеров.Дополнительные триггерные вызовы часто не нужны, но их может быть трудно избежать, когда они окажутся глубоко в кроличьей норе.
Рекурсивные триггеры установлены на ON . Это параметр уровня базы данных, который по умолчанию отключен. Когда он включен, он позволяет содержимому триггера вызывать тот же триггер. Если требуется рекурсия, реализуйте ее, используя менее запутанный метод. Рекурсивные триггеры могут сильно снизить производительность и чрезвычайно затрудняют отладку. Часто рекурсивные триггеры используются, когда DML в одном триггере запускает другие триггеры как часть операции.Уменьшение количества операций записи в таблицу может устранить необходимость в этом. Рассмотрим триггеры INSTEAD OF как способ разрешить однократное изменение данных без необходимости дополнительных операций постфактум.
Функции, хранимые процедуры или представления находятся в триггерах. Инкапсуляция большего количества бизнес-логики в триггерах усложняет их и создает ложное впечатление, что код триггера короткий и простой, хотя на самом деле это не так. По возможности избегайте использования хранимых процедур и функций в триггерах и ограничивайте представления сценариями, в которых они просты и понятны.
Итерация. WHILE циклов и КУРСОРОВ по своей природе работает построчно и может привести к операции с 1000 строками, которые встречаются по одной строке за раз, 1000 раз, что значительно снижает производительность запроса.

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

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

Как улучшить триггеры

Есть много способов сделать триггеры более удобными в обслуживании, более понятными и более производительными. Ниже приведены некоторые дополнительные мысли о том, как эффективно управлять триггерами и избегать попадания в ловушки.

Документ!

Сам триггер должен быть хорошо задокументирован:

Почему существует этот триггер?
Что он делает?
Как это работает (если не очевидно)?
Есть ли исключения или предостережения относительно того, как работает триггер?

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

33 34

9 0008 64

08 80

/ * 29.12.2020 EHP

Этот триггер регистрирует все изменения в таблице Orders_log

, которые происходят для внешних клиентов.

CustomerID = -1 означает внутреннего / тестового клиента, а

они не проверены.

* /

CREATE TRIGGER TR_Sales_Orders_Audit

ON Sales.Orders

FOR INSERT, UPDATE, DELETE

BEGIN

SET NOCOUNT ON;

INSERT INTO Sales.Orders_log

(КодЗаказа, CustomerID_Old, CustomerID_New,

SalespersonPersonID_Old, SalespersonPersonID_New,

PickedByPersonID_Old, PickedByPersonID_New,

ContactPersonID_Old, ContactPersonID_New,

BackorderOrderID_Old, BackorderOrderID_New,

OrderDate_Old, OrderDate_New,

ExpectedDeliveryDate_Old,

ExpectedDeliveryDate_New,

CustomerPurchaseOrderNumber_Old,

CustomerPurchaseOrderNumber_New,

IsUndersupplyBackordered_Old,

IsUndersupply_pplyBackordered_Old,

Комментарии_pply_pplyBackordered_Instruments8,

33 ternalComments_New,

PickingCompletedWhen_Old, PickingCompletedWhen_New,

LastEditedBy_Old, LastEditedBy_New,

LastEditedWhen_Old, LastEditedWhen_New,

INNAME

, SELECTNECT 900, ActionTime13, ПользовательOrderID, Deleted.OrderID) AS OrderID,

— OrderID никогда не может измениться.

— это гарантирует, что мы получим идентификатор правильно,

— независимо от типа операции.

Deleted.CustomerID AS CustomerID_Old,

Inserted.CustomerID AS CustomerID_New,

Deleted.SalespersonPersonID AS SalespersonID_Old,

Inserted.SalespersonPersonID AS SalespersonPersonID_New,

.PickedByPersonID А.С. PickedByPersonID_Old,

Inserted.PickedByPersonID А.С. PickedByPersonID_New,

Deleted.ContactPersonID А.С. ContactPersonID_Old,

Inserted.ContactPersonID А.С. ContactPersonID_New,

Deleted.BackorderOrderID А.С. BackorderOrderID_Old,

Inserted.BackorderOrderID А.С. BackorderOrderID_New,

Deleted.OrderDate А.С. OrderDate_Old,

Inserted.OrderDate AS OrderDate_New,

Deleted.ExpectedDeliveryDate AS ExpectedDeliveryDate_Old,

Inserted.ExpectedDeliveryDate А.С. ExpectedDeliveryDate_New,

Deleted.CustomerPurchaseOrderNumber А.С. CustomerPurchaseOrderNumber_Old,

Inserted.CustomerPurchaseOrderNumber А.С. CustomerPurchaseOrderNumber_New,

Deleted.IsUndersupplyBackordered AS IsUndersupplyBackordered_Old,

Inserted.IsUndersupplyBackordered AS IsUndersupplyBackordered_New,

удалена.Комментарии AS Comments_Old,

Inserted Comments AS Comments_New,

Deleted.DeliveryInstructions

AS DeliveryInstructions_Old,

Inserted.DeliveryInstructions

AS DeliveryInstructions_New,

Deleted.InternalComments

_Oldternal.InternalComments Deleted.PickingCompletedWhen AS PickingCompletedWhen_Old,

Вставлено.PickingCompletedWhen

AS PickingCompletedWhen_New,

Deleted.LastEditedBy AS LastEditedBy_Old,

Inserted.LastEditedBy AS LastEditedBy_New,

Deleted.

— вставлено существует, удалено существует или оба существуют.

КОГДА вставлен.OrderID IS NULL THEN ‘DELETE’

WHEN Deleted.OrderID IS NULL THEN ‘INSERT’

ELSE ‘UPDATE’

END AS ActionType,

SYSUTCDATETIME () ActionTime,

SUSER_SNAME

AS UserName

FULL JOIN Deleted

ON Inserted.OrderID = Deleted.OrderID

WHERE Inserted.CustomerID <> -1

— -1 указывает на внутреннего / непроизводственного

—клиента, который не должен подвергаться аудиту.

ИЛИ Deleted.CustomerID <> -1;

— -1 указывает на внутреннего / непроизводственного

— клиента, который не подлежит аудиту.

КОНЕЦ

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

Исключение CustomerID = -1. Это не будет очевидно для тех, кто этого не знает, так что это отличный комментарий.
Для чего предназначен оператор CASE для ActionType .
Почему ISNULL используется в столбце OrderID между вставленными и удаленными.

Используйте IF UPDATE

Внутри триггеров функция UPDATE позволяет определить, пыталась ли операция обновления или вставки записать данные в данный столбец. Это может позволить триггеру проверять, изменился ли столбец перед объединением таблиц и выполнением операций. Ниже приведен пример этого синтаксиса:

СОЗДАТЬ ТРИГГЕР TR_Sales_Orders_Log_BackorderID_Change

ON Продажи.Заказы

ПОСЛЕ ОБНОВЛЕНИЯ

КАК

НАЧАТЬ

УСТАНОВИТЬ БЕЗ СЧЕТА;

ЕСЛИ ОБНОВЛЕНИЕ (BackorderOrderID)

НАЧАТЬ ОБНОВЛЕНИЕ OrderBackorderLog

SET BackorderOrderID = Inserted.BackorderOrderID,

PreviousBackorderOrderID = Deleted.BackorderOrderID

ОТ dbo.OrderBackorderLog

внутреннее соединение Поставлен

ПО Inserted.OrderID = OrderBackorderLog. Код заказа

КОНЕЦ

Если сначала проверить, был ли обновлен BackorderID , триггер может обойти последующие операции, когда они не нужны, тем самым избегая необходимости запрашивать Inserted и OrderBackorderLog .Для триггеров, где TSQL не требуется часто, это отличный способ повысить производительность, позволяя триггеру полностью пропускать код на основе значений UPDATE для необходимых столбцов.

COLUMNS_UPDATED — это битовый шаблон VARBINARY , который указывает, какие столбцы в таблице были обновлены как часть операции записи, и может использоваться в триггере, чтобы быстро определить, был ли определенный набор столбцов затронут INSERT или ОБНОВЛЕНИЕ операции.Хотя это задокументированный и законный способ определения столбцов, затронутых операцией записи, его сложно использовать и трудно задокументировать. Я обычно не рекомендую использовать его, так как он почти гарантированно запутает разработчиков, незнакомых с ним.

IF UPDATE может быть более подробным, но эффективным и легким для понимания, даже если задействовано много столбцов.

Обратите внимание, что не имеет значения, изменяется ли столбец для UPDATE или COLUMNS_UPDATED , чтобы пометить этот столбец как обновленный.Операция, выполняющая запись в столбец, даже если значение не изменится, все равно вернет 1 для UPDATE или 1 в битовом шаблоне для COLUMNS_UPDATED . Эти биты отслеживают только то, был ли столбец целью операции записи, а не то, изменилось ли само значение.

Один триггер на операцию

Сделайте код триггера максимально простым. Поддерживайте не более одного триггера вставки, обновления и удаления. Если позволить количеству триггеров в таблице расти, она значительно усложняется и усложняет понимание ее работы.Один триггер на действие гарантирует, что порядок триггеров никогда не будет важным фактором, а также улучшает ремонтопригодность кода и снижает сложность.

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

СОЗДАТЬ ТРИГГЕР TR_Sales_Orders_I

ON Продажи.Заказы

ПОСЛЕ ВСТАВКИ

СОЗДАТЬ ТРИГГЕР TR_Sales_Orders_IU

ПО Sales.Orders

ПОСЛЕ ВСТАВКИ, ОБНОВЛЕНИЯ

СОЗДАТЬ ТРИГГЕР TR_Sales_Orders_UD

ПО Sales.Orders8 DELETE_Orders_UD

ПО Sales.Orders_GET_Orders

В ПО Sales.Orders_GET_Orders_Orders

ПО Sales.Orders_GET_Orders_Orders

9000 НА ПРОДАЖАХ 9000 НА ПРОДАЖАХ ПРОДАЖИ 9000 ПОСЛЕДНЯЯ ПОДДЕРЖКА 9000,

НА ПРОДАЖАХ ПРОДАЖИ 9000 ПОСЛЕДНЯЯ ИНФОРМАЦИЯ

ПОСЛЕ ОБНОВЛЕНИЯ, ВСТАВИТЬ, УДАЛИТЬ

СОЗДАТЬ ТРИГГЕР TR_Sales_Orders_ID

ПО Sales.Orders

ПОСЛЕ ВСТАВКИ, УДАЛИТЬ

Что происходит при вставке строки? Как насчет операции MERGE , которая вставляет и обновляет? В каком порядке срабатывают триггеры? Ответ на эти вопросы требует некоторого размышления и исследования.Эта сложность не стоит той головной боли, которую она вызывает. Поддержание меньшего количества триггеров - простое решение, которое избавляет от догадок при понимании того, как происходит запись в данной таблице.

Для справки, порядок триггеров можно изменить с помощью системной хранимой процедуры sp_settriggerorder , хотя это применимо только к триггерам AFTER.

Будьте проще

Оптимальные триггеры выполняют простые операции, работают быстро и не вызывают срабатывания дополнительных триггеров или модулей из-за их выполнения.

Не существует твердого правила относительно того, насколько простым или сложным должен быть триггер, но простое руководство состоит в том, что идеальные триггеры достаточно просты, чтобы, если бы содержащаяся внутри логика должна была быть удалена из триггера в другое место, перемещение не было бы непомерно дорого. То есть, если бизнес-логика в триггере настолько сложна, что ее перемещение становится слишком дорогостоящим для рассмотрения, то вполне вероятно, что эти триггеры стали слишком сложными.

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

Это количество часов можно рассматривать как часть затрат на ремонтопригодность для любого данного триггера. То есть цена, которую нужно будет заплатить, чтобы избавиться от триггера в случае необходимости.Это может показаться абстрактным, но миграции базы данных между платформами - обычное дело. Набор триггеров, которые хорошо работают в SQL Server, может не работать в Oracle или PostgreSQL. Точно так же переход на вариант базы данных, который не поддерживает тот же уровень функциональности триггеров, может потребовать удаления или упрощения триггеров.

Табличные переменные, оптимизированные для памяти

Иногда временные таблицы необходимы в триггере, чтобы разрешить несколько обновлений данных или облегчить чистую вставку из триггера INSTEAD OF INSERT .Временные таблицы хранятся в tempdb и зависят от размера, скорости и производительности, присутствующих в базе данных tempdb .

Для временных таблиц, к которым часто обращаются, оптимизированная для памяти табличная переменная - отличный способ сохранить временные данные в памяти, а не в tempdb .

Следующий TSQL настраивает базу данных для данных, оптимизированных для памяти (при необходимости):

ALTER DATABASE WideWorldImporters

SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

ALTER DATABASE WideWorldImporters ADD FILEGROUP WWI_InMemory_Data

CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE WideWorldImporters ДОБАВИТЬ ФАЙЛ

(NAME = 'WideWorldImporters_IMOLTP_File_1',

FILENAME = 'C: \ SQLData \ WideWorldImporters_IMOLTP_File_1.mem ')

ДЛЯ ГРУППЫ ФАЙЛОВ WWI_InMemory_Data;

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

СОЗДАТЬ ТИП dbo.SalesOrderMetadata

КАК ТАБЛИЦА

(OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,

CustomerID INT NOT NULL,

SalespersonPersonID INT NOT NULL,

ContactPersonder NULL,

ContactPersonID HULL,

ContactPersonder HULL,

ContactPata_Data_Data_INDER 13

(CustomerID) WITH (BUCKET_COUNT = 1000))

WITH (MEMORY_OPTIMIZED = ON);

Этот TSQL создает таблицу, необходимую для триггера, показанного ниже:

СОЗДАТЬ ТАБЛИЦУ dbo.OrderAdjustmentLog

(OrderAdjustmentLog_ID int NOT NULL IDENTITY (1,1)

CONSTRAINT PK_OrderAdjustmentLog PRIMARY KEY CLUSTERED,

OrderID INT NOT NULL,

CustomerID INT NOT NULL,

INTONPers

CreateTimeUTC DATETIME2 (3) NOT NULL);

Ниже приводится демонстрация триггера, использующего таблицу, оптимизированную для памяти:

33 34

CREATE TRIGGER TR_Sales_Orders_Mem_Test

ON Продажи.Заказы

ПОСЛЕ ОБНОВЛЕНИЯ

КАК

НАЧАТЬ

УСТАНОВИТЬ БЕЗ СЧЕТА;

DECLARE @OrderData dbo.SalesOrderMetadata;

INSERT INTO @OrderData

(OrderID, CustomerID, SalespersonPersonID,

ContactPersonID)

SELECT

OrderID,

CustomerID,

SalespersonPersonID,

ContactPersonID

УДАЛИТЬ OrderData

FROM @OrderData OrderData

ВНУТРЕННЕЕ СОЕДИНЕНИЕ продаж.Клиенты

ON Customers.CustomerID = OrderData.CustomerID

ГДЕ Customers.IsOnCreditHold = 0;

ОБНОВЛЕНИЕ OrderData

SET ContactPersonID = 1

FROM @OrderData OrderData

ГДЕ OrderData.ContactPersonID IS NULL;

ВСТАВИТЬ В dbo.OrderAdjustmentLog

(OrderID, CustomerID, SalespersonPersonID,

ContactPersonID, CreateTimeUTC)

SELECT

OrderData.OrderID,

OrderData.CustomerID,

OrderData.SalespersonPersonID,

OrderData.ContactPersonID,

SYSUTCDATETIME ()

FROM @OrderData OrderData;

КОНЕЦ

Чем больше операций требуется в триггере, тем больше будет заметна экономия, поскольку табличная переменная, оптимизированная для памяти, не требует ввода-вывода для чтения / записи. После чтения исходных данных из таблицы INSERTED оставшаяся часть триггера может оставить tempdb в покое, уменьшая потенциальную конкуренцию, которая может возникнуть при использовании стандартных табличных переменных или временных таблиц.

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

ОБНОВЛЕНИЕ клиентов

УСТАНОВИТЬ IsOnCreditHold = 1

ОТ Sales.Customers

ГДЕ Customers.CustomerID = 832;

UPDATE Orders

SET SalespersonPersonID = 2

FROM sales.Orders

WHERE CustomerID = 832;

После выполнения можно проверить содержимое таблицы OrderAdjustmentLog :

Результаты соответствуют ожиданиям.Таблицы, оптимизированные для памяти, позволяют значительно повысить скорость запуска за счет уменьшения зависимости от стандартного хранилища и перемещения промежуточных таблиц в память. Это ограничено сценариями, в которых будет большое количество конфликтов или операций ввода-вывода против временных объектов, но также может быть полезно в хранимых процедурах или других процедурных TSQL.

Альтернативы триггерам

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

Временные таблицы

Темпоральные таблицы были представлены в SQL Server 2016 и обеспечивают простой способ добавления управления версиями в таблицу без создания собственных структур данных и ETL. Это ведение журнала невидимо для приложений и обеспечивает полную поддержку управления версиями, совместимую с ANSI, что позволяет легко решить проблему сохранения старых версий данных.

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

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

Проверить ограничения

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

ИЗМЕНИТЬ ТАБЛИЦУ Sales.Invoices WITH CHECK ADD CONSTRAINT

CK_Sales_Invoices_ReturnedDeliveryData_Must_Be_Valid_JSON

CHECK ([ReturnedDeliveryData] IS NULL OR

ISJSONi

Этот код проверяет, является ли столбец допустимым JSON или нет.Если это так, то выполнение продолжается в обычном режиме. В противном случае SQL Server выдаст ошибку, и операция записи завершится неудачно. Ограничения проверки могут проверять любую комбинацию столбца и значений и, следовательно, могут управлять как простыми, так и сложными задачами проверки.

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

Уникальные ограничения

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

Ниже приведен пример уникального ограничения:

ИЗМЕНИТЬ ТАБЛИЦУ Warehouse.Colors ДОБАВИТЬ ОГРАНИЧЕНИЕ

UQ_Warehouse_Colors_ColorName UNIQUE NONCLUSTERED (ColorName ASC);

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

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

Ограничения внешнего ключа

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

Это простой пример внешнего ключа:

ALTER TABLE Продажи.Заказы С ОГРАНИЧЕНИЕМ ПРОВЕРКИ ДОБАВЛЕНИЯ

FK_Sales_Orders_CustomerID_Sales_Customers ИНОСТРАННЫЙ КЛЮЧ (CustomerID)

ССЫЛКИ Sales.Customers (CustomerID);

Когда данные записываются в Sales.Orders , столбец CustomerID будет сравниваться со столбцом CustomerID в Sales.Customers .

Подобно уникальным ограничениям, внешние ключи имеют только одну цель: проверить, что данные, записываемые в одну таблицу, существуют в другой таблице.Его легко документировать, легко понять и эффективно реализовать.

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

Триггеры - неподходящее место для выполнения этих проверок и будут менее эффективным решением по сравнению с использованием внешних ключей.

Хранимые процедуры

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

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

Хотя SQL Server (и большинство СУБД) предоставляют ACID-гарантию, что транзакции будут атомарными, согласованными, изолированными и долговечными, наборы транзакций в нашем собственном коде могут или не должны следовать одним и тем же правилам.Реальные приложения различаются по требованиям к целостности данных: от реального времени и атомарной до конечной согласованности.

Хранимая процедура позволяет настраивать код для достижения целостности данных, требуемой приложением, гарантируя, что производительность и вычислительные ресурсы не будут тратиться на ненужную целостность данных. Например, приложение для социальных сетей, которое позволяет пользователям публиковать фотографии кошек, вряд ли потребует, чтобы его транзакции были полностью атомарными и последовательными. Если моя фотография кошки появится на секунду раньше или позже вашей, никому будет до этого дела.Точно так же, если вы прокомментируете мою фотографию, пока я ее редактирую, время, скорее всего, не будет иметь значения для тех, кто использует эти данные.

С другой стороны, банковское приложение, которое управляет денежными транзакциями, должно гарантировать, что транзакции выполняются аккуратно, чтобы не было возможности пропустить деньги или указать неправильные числа. Если у меня есть банковский счет, содержащий 20 долларов, и я снимаю 20 долларов в то же время, что и кто-то другой, у нас обоих не может быть возможности добиться успеха. Один из нас идет первым и получает 20 долларов, а другой встречает соответствующее сообщение об ошибке, касающееся баланса 0 долларов.

Функции

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

Рассмотрим следующую функцию:

33 34

9 0008 64

08 80

СОЗДАТЬ ФУНКЦИЮ Веб-сайт.CalculateCustomerPrice

(@CustomerID INT, @StockItemID INT, @PricingDate DATE)

ВОЗВРАТ ДЕСЯТИЧНО (18,2)

С ВЫПОЛНИТЬ КАК ВЛАДЕЛЕЦ

НАЧАЛО

DECLARE @CLARE

DECLARE @UnitPrice decimal (18,2);

DECLARE @LowestUnitPrice decimal (18,2);

DECLARE @HighestDiscountAmount decimal (18,2);

DECLARE @HighestDiscountPercentage decimal (18,3);

DECLARE @BuyingGroupID int;

DECLARE @CustomerCategoryID int;

DECLARE @DiscountedUnitPrice decimal (18,2);

ВЫБРАТЬ @BuyingGroupID = BuyingGroupID,

@CustomerCategoryID = CustomerCategoryID

ИЗ отдела продаж.Клиенты

ГДЕ CustomerID = @CustomerID;

ВЫБРАТЬ @UnitPrice = si.UnitPrice

ИЗ Warehouse.StockItems AS si

ГДЕ si.StockItemID = @StockItemID;

НАБОР @CalculatedPrice = @UnitPrice;

SET @LowestUnitPrice = (

SELECT MIN (sd.UnitPrice)

FROM Sales.SpecialDeals AS sd

WHERE ((sd.StockItemID = @StockItemID)

OR (sd.StockItemID IS NULL))

AND ((sd.CustomerID = @CustomerID)

OR (sd.CustomerID IS NULL))

AND ((sd.BuyingGroupID = @BuyingGroupID)

OR (sd.BuyingGroupID IS NULL) )

И ((sd.CustomerCategoryID = @CustomerCategoryID)

ИЛИ (sd.CustomerCategoryID IS NULL))

И ((sd.StockGroupID IS NULL) ИЛИ СУЩЕСТВУЕТ (ВЫБЕРИТЕ 1

ИЗ Warehouse.StockItemStockGroups AS sisg

ГДЕ sisg.StockItemID = @StockItemID

И sisg.StockGroupID = sd.StockGroupID))

И sd.UnitPrice НЕ ЯВЛЯЕТСЯ NULL

AND @PricingDate BETWEndate AND sd.Date AND @PricingDate BETWEndate И sd.

ЕСЛИ @LowestUnitPrice НЕ НУЛЬ И @LowestUnitPrice <@UnitPrice

НАЧАТЬ

SET @CalculatedPrice = @LowestUnitPrice;

КОНЕЦ;

НАБОР @HighestDiscountAmount = (

ВЫБРАТЬ МАКС. (Сд.DiscountAmount)

ОТ Sales.SpecialDeals AS sd

ГДЕ ((sd.StockItemID = @StockItemID)

OR (sd.StockItemID IS NULL))

AND ((sd.CustomerID = @CustomerID)

OR (sd .CustomerID IS NULL))

AND ((sd.BuyingGroupID = @BuyingGroupID)

OR (sd.BuyingGroupID IS NULL))

AND ((sd.CustomerCategoryID = @CustomerCategoryID)

OR (sd.CustomerCategoryID IS NULL))

AND ((sd.StockGroupID IS NULL) ИЛИ СУЩЕСТВУЕТ

(ВЫБЕРИТЕ 1 ИЗ Warehouse.StockItemStockGroups AS sisg

ГДЕ sisg.StockItemID = @StockItemID

AND sisg.StockGroupID ).StockGroupID =

И sd.DiscountAmount НЕ НУЛЬ

И @PricingDate МЕЖДУ sd.StartDate И sd.EndDate);

ЕСЛИ @HighestDiscountAmount НЕ НУЛЬ И (

@UnitPrice - @HighestDiscountAmount) <@CalculatedPrice

BEGIN

SET @CalculatedPrice = @UnitPrice - @HighestDiscount;

КОНЕЦ;

SET @HighestDiscountPercentage = (

SELECT MAX (sd.DiscountPercentage)

FROM Sales.SpecialDeals AS sd

WHERE ((sd.StockItemID = @StockItemID)

OR (sd.StockItemID IS NULL))

AND ((sd.CustomerID = @CustomerID)

OR (sd.CustomerID IS NULL))

AND ((sd.BuyingGroupID = @BuyingGroupID)

OR (sd.BuyingGroupID IS NULL) )

И ((sd.CustomerCategoryID = @CustomerCategoryID)

ИЛИ (sd.CustomerCategoryID IS NULL))

И ((sd.StockGroupID IS NULL) ИЛИ СУЩЕСТВУЕТ

(ВЫБЕРИТЕ 1 ИЗ Warehouse.StockItemStockGroups AS sisg

ГДЕ sisg.StockItemID = @StockItemID

И sisg.StockGroupID = sd.StockGroupID))

И sd.DiscountPercentage НЕ НУЖЕН

И @PricingDate BETWEndate И @PricingDate BETWEndate между sd.Start.Start.Date).

ЕСЛИ @HighestDiscountPercentage НЕ НУЖНО

НАЧАТЬ

SET @DiscountedUnitPrice = ROUND (@UnitPrice *

@HighestDiscountPercentage / 100.0, 2);

IF @DiscountedUnitPrice <@CalculatedPrice

SET @CalculatedPrice = @DiscountedUnitPrice;

КОНЕЦ;

ВОЗВРАТ @CalculatedPrice;

КОНЕЦ;

Абсолютный зверь с точки зрения сложности. Хотя он принимает скалярные параметры для определения расчетной цены, он выполняет обширные операции и даже включает дополнительные чтения в Warehouse.StockItemStockGroups , Warehouse.StockItems и Sales.Customers . Если это критическое вычисление, которое часто используется для отдельных строк данных, то его инкапсуляция в функцию - простой способ получить необходимое вычисление без дополнительной сложности триггеров. Будьте осторожны с функциями и обязательно проверяйте большие наборы данных. Простая скалярная функция обычно хорошо масштабируется с большими данными, но более сложные функции могут работать плохо. Скалярное встраивание UDF в SQL Server 2019 помогает решить эту проблему, но необходимо провести достаточное тестирование независимо от версии SQL Server.

Можно также использовать хранимую процедуру, но функция может предоставить дополнительное преимущество в виде встраивания операции, позволяя выполнять вычисление как часть операции записи. Дополнительным преимуществом этого подхода является то, что код находится в одном месте и может быть изменен при необходимости в одном месте. Точно так же можно отрегулировать места, где используется эта функция, чтобы прекратить ее использование или изменить способ ее использования. Все это может быть выполнено независимо от триггеров, гарантируя, что код остается относительно простым для документирования и сопровождения, даже если логика под ним столь же сложна, как в функции, представленной выше.

Код

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

Распространенное объяснение для помещения кода в триггеры вместо этого состоит в том, что это позволяет избежать необходимости изменять код, отправлять сборки и иным образом нести время и риск внесения изменений в приложение. Это прямо противоречит любому риску, связанному с внесением изменений в базу данных.Часто между разработчиками приложений и разработчиков баз данных обсуждается вопрос о том, кто будет отвечать за новый код.

В общем, если адреса функций триггера изменяются исключительно из-за приложения, то приложение является идеальным местом для этого кода (а не триггера). Если функция триггера обращается к DML, который может возникать где угодно (приложение, API, хранимые процедуры и т. Д.), То триггер вполне может быть лучшим решением, поскольку он избавляет от необходимости пытаться добавить код во все эти места, а затем быть вынужден поддерживать его постфактум.

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

Расчетные колонки

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

Вычисляемые столбцы автоматически поддерживаются SQL Server при изменении каких-либо базовых значений и не могут быть вставлены или обновлены.

Ниже приведены некоторые примеры вычисляемых столбцов в WideWorldImporters :

[ConfirmedDeliveryTime] AS

(TRY_CONVERT ([datetime2] (7), json_value ([ReturnedDeliveryData],

N '$. DeliveredWhen'), (126))),

[ConfirmedReceivedByue] ([ReturnedDeliveryData], N '$.ReceivedBy ')),

[IsFinalized] AS (случай, когда [FinalizationDate]

IS NULL, затем CONVERT ([bit], (0)) else CONVERT ([bit], (1)) end) PERSISTED,

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

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

Сервисный брокер

Триггеры

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

Service Broker создан для управления отправкой сообщений между очередями и делает это эффективно. Его можно использовать для простых случаев использования, таких как постановка задач в очередь для обработки или протоколирование процессов для выполнения в будущем. Его также можно использовать для разделения рабочих нагрузок обработки между серверами, позволяя переносить дорогостоящие ETL или другие дорогостоящие процессы из критически важной производственной системы в другую систему, предназначенную для этих целей.

Service Broker - это зрелая функция SQL Server, но она включает в себя довольно много нового синтаксиса для изучения и использования, поэтому эта статья не является идеальным местом для ознакомления с ее использованием. Документации довольно много, и документация Microsoft - хорошее место для начала.

При рассмотрении использования триггеров для постановки в очередь и / или обмена сообщениями сначала рассмотрите возможность использования Service Broker. Обычно это подходящий инструмент для работы, позволяющий избежать необходимости вручную поддерживать таблицы очередей и процессы управления очередями.Базы данных не предназначены для использования в качестве хороших очередей, поэтому реализация Service Broker или сторонней утилиты 3 ^rd, разработанной для этой цели, обеспечит более простое в обслуживании решение, которое работает лучше.

Использование триггеров SQL Server

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

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

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

Если вам понравилась эта статья, вам также может понравиться Triggers: Threat or Menace?

Триггеры Введение, создание триггера DML

Триггер - это механизм, который вызывается, когда определенное действие происходит в определенной таблице. Каждый триггер состоит из трех основных частей:

Имя
Действие
Выполнение

Максимальный размер имени триггера составляет 128 символов. Действие триггера может быть либо оператором DML (INSERT, UPDATE или DELETE), либо оператором DDL.Следовательно, существует две формы триггеров: триггеры DML и триггеры DDL. Часть выполнения триггера обычно содержит хранимую процедуру или пакет.

Примечание - Компонент Database Engine позволяет создавать триггеры с использованием языков программирования Transact-SQL или CLR, таких как C # и Visual Basic. В этом разделе описывается использование Transact-SQL для реализации триггеров. Реализация триггеров с использованием языков программирования CLR показана в конце главы.

Создание триггера DML

Триггер создается с помощью оператора CREATE TRIGGER, который имеет следующую форму:

 CREATE TRIGGER [имя_схемы.] trigger_name
ВКЛ {table_name | view_name}
[WITH dml_trigger_option [,…]]
{ДЛЯ | ПОСЛЕ | ВМЕСТО} {[ВСТАВИТЬ] [,] [ОБНОВЛЕНИЕ] [,] [УДАЛИТЬ]}
[С ПРИЛОЖЕНИЕМ]
{AS sql_statement | ВНЕШНЕЕ ИМЯ имя_метода}

Примечание - Предыдущий синтаксис охватывает только триггеры DML. Триггеры DDL имеют немного другой синтаксис, который будет показан позже в этой главе.

имя_схемы - это имя схемы, которой принадлежит триггер. trigger_name - это имя триггера.table_name - это имя таблицы, для которой указан триггер. (Триггеры для представлений также поддерживаются, на что указывает включение view_name.)

AFTER и INSTEAD OF - это две дополнительные опции, которые вы можете определить для триггера. (Предложение FOR является синонимом AFTER.) Триггеры AFTER срабатывают после того, как происходит действие триггера. Триггеры INSTEAD OF выполняются вместо соответствующего триггерного действия. Триггеры AFTER могут быть созданы только для таблиц, а триггеры INSTEAD OF могут быть созданы как для таблиц, так и для представлений.Примеры, показывающие использование этих двух типов триггеров, приведены далее в этой главе.

Опции INSERT, UPDATE и DELETE определяют действие триггера. (Действие триггера - это тип оператора Transact-SQL, который активирует триггер.) Эти три оператора можно записать в любой возможной комбинации. Оператор DELETE не допускается, если используется опция IF UPDATE.

Как видно из синтаксиса оператора CREATE TRIGGER, спецификация AS sql_statement используется для определения действия (й) триггера.(Вы также можете использовать параметр EXTERNAL NAME, который будет объяснен далее в этой главе.)

Примечание . Компонент Database Engine позволяет создавать несколько триггеров для каждой таблицы и для каждого действия (INSERT, UPDATE и DELETE). По умолчанию не существует определенного порядка, в котором выполняются несколько триггеров для данного действия модификации. (Вы можете определить порядок, используя первый и последний триггеры, как описано далее в этой главе.)

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

Изменение структуры триггера

Transact-SQL также поддерживает оператор ALTER TRIGGER, который изменяет структуру триггера. Оператор ALTER TRIGGER обычно используется для изменения тела триггера. Все предложения и опции оператора ALTER TRIGGER соответствуют предложениям и параметрам с такими же именами в операторе CREATE TRIGGER.

Оператор DROP TRIGGER удаляет один или несколько существующих триггеров из текущей базы данных.

В следующем разделе описаны удаленные и вставленные таблицы, которые играют важную роль в инициированном действии.

Использование удаленных и вставленных виртуальных таблиц

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

удалено Содержит копии строк, удаленных из инициированной таблицы
вставлено Содержит копии вставленных строк в инициированную таблицу

Структура этих таблиц эквивалентна структуре таблицы, для которой указан триггер.

Удаленная таблица используется, если в операторе CREATE TRIGGER указано предложение DELETE или UPDATE. Вставленная таблица используется, если в операторе CREATE TRIGGER указано предложение INSERT или UPDATE. Это означает, что для каждого оператора DELETE, выполняемого в инициированном действии, создается удаленная таблица. Точно так же для каждого оператора INSERT, выполняемого в инициированном действии, создается вставленная таблица.

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

Создание триггера для формата телефона по умолчанию с помощью T-SQL

НОУ ИНТУИТ | Лекция | Триггеры: создание и применение

Определение триггера в стандарте языка SQL

Ms sql создание триггера — Вэб-шпаргалка для интернет предпринимателей!

Определение триггеров

Удаление триггера

Отключение триггера

Создание триггера DML

Изменение структуры триггера

Использование виртуальных таблиц deleted и inserted

Области применения DML-триггеров

Триггеры AFTER

Создание журнала логов

Реализация бизнес-правил

Принудительное обеспечение ограничений целостности

Триггеры INSTEAD OF

Триггеры first и last

Триггеры DDL и области их применения

Триггеры DDL уровня базы данных

Триггеры DDL уровня сервера

Триггеры и среда CLR

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

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

Рекомендуем к прочтению

Создание триггера

Создание пользователей

Создание триггеров. Обработка баз данных на Visual Basic®.NET

Читайте также

Создание

Увеличение глубины рекурсии процедур и триггеров

5.5. Двоичная регистрация сохраненных подпрограмм и триггеров

Действия триггеров по изменению правил целостности

Преимущества использования триггеров

Элементы процедур и триггеров

Компиляция хранимых процедур и триггеров

Создание триггеров

Особенности PSQL для триггеров

Изменение триггеров

Синтаксис для изменения триггеров

Удаление триггеров

Почему следует избегать триггеров | Windows IT Pro/RE

Проблемы с триггерами

Когда использовать триггеры

Как создать триггер Oracle

Комментарии:

CREATE TRIGGER — База знаний MariaDB

Синтаксис

Описание

MariaDB, начиная с 10.2.3

ИЛИ ЗАМЕНИТЬ

MariaDB, начиная с 10.1.4

DEFINER

ЕСЛИ НЕ СУЩЕСТВУЕТ

MariaDB начиная с 10.1.4

trigger_time

trigger_event

ПОДПИСЫВАЕТСЯ / ПРЕДЫДУЩАЕТСЯ other_trigger_name

MariaDB, начиная с 10.2.3

Примеры

См. Также

Как создавать и использовать триггеры DML в SQL Server на реальных примерах

Как создать и запустить триггер SQL Server

Несколько фактов

Шаблон триггера

Практический пример

Пробные стрельбы

Перезарядка…

Создание триггеров SQL в SAP HANA — процесс, который нельзя игнорировать!

Триггеры SQL в SAP HANA

Ключевые использования триггеров SQL в SAP HANA

Синтаксис для создания триггеров SQL

Элементы синтаксиса для создания триггеров SQL

Как создавать триггеры SQL в SAP HANA Studio

Сводка

Введение в триггеры в SQL

Типы триггеров в SQL Server

ПОСЛЕ ТРИГГЕРОВ в SQL Server

INSTEAD OF TRIGGERS в SQL Server

Синтаксис триггеров сервера Sql