Ms sql триггеры примеры: MS SQL Server и T-SQL

Содержание

Transact-SQL | Триггеры

163

Работа с базами данных в .NET Framework — SQL Server 2012 — Триггеры

Исходники баз данных

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

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

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

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

CREATE TRIGGER [schema_name.]trigger_name
    ON {table_name | view_name}
        [WITH dml_trigger_option [,…]]
    {FOR | AFTER | INSTEAD OF} { [INSERT] [,] [UPDATE] [,] [DELETE]}
    [WITH APPEND]
    {AS sql_statement | EXTERNAL NAME method_name} 


Соглашения по синтаксису

Предшествующий синтаксис относится только к триггерам 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. Поэтому для каждой и

Триггеры в SQL — CodeTown.ru

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

Общие сведения

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

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

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

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

CREATE TRIGGER name_trigger

После оператора и имени триггера необходимо указать в каком случае будет срабатывать триггер. Возможно 6 вариантов:

BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE
AFTER INSERT
AFTER UPDATE
AFTER DELETE

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

Пример работы в SQL

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

Рассмотрим тестовую задачу, которая покажет возможности триггеров. Предположим, что в таблице orders нам нужно поменять цену (поле amt), а новое значение, которое мы введем, увеличить еще на 20%. Задача бывает полезна, когда нужно сделать наценку на товар.

Чтобы нам не высчитывать 20% вручную от новой цены — создадим триггер. Он автоматически будет увеличивать новую цену на 20%.
Вот код создания такого триггера:

DELIMITER //
CREATE TRIGGER Before_Update_amt
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
    SET NEW.amt = NEW.amt * 1.2;
END //
DELIMITER ;

Заметьте, что название триггера (Before_Update_amt) лучше всего давать такое, чтобы было понятно при каком случае он срабатывает. Триггер срабатывает перед обновлением потому, что сначала мы должны узнать новое значение, а только потом его занести в поле.

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

Следующий момент — цикл FOR EACH ROW. Он необходим потому, что одновременно может изменяться не одно значение, а несколько строк. Вот, для каждой измененной строчки мы и увеличиваем значение на 20%.

Триггер на взаимодействие таблиц

Рассмотрим еще одну задачу: у нас есть продавец (в таблице salespeople), и его продажи отражены в таблицы orders. Представим теперь, что продавец увольняется и все его продажи тоже следует удалить. Если таких продаж много, то легче всего воспользоваться триггером.

DELIMITER //
CREATE TRIGGER After_Delete_salespeople
AFTER DELETE
ON salespeople
FOR EACH ROW
BEGIN
    DELETE FROM orders
    WHERE orders.snum = OLD.snum;
END //
DELIMITER ;

Итак, после удаления продавца из salespeople берется его уникальный номер snum — он записан в коде как OLD.snum. Затем, по этому уникальному номеру удаляются все строчки из таблицы orders.

Можете проверить этот код, или его аналог. После удаления продавца триггер в SQL удаляет все записи из таблицы orders.

Ключевые слова OLD и NEW

На всякий случай, еще раз разберем употребление этих ключевых слов.

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

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

Заключение

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

Поделиться ссылкой:

Похожее

3.4. Триггеры — Transact-SQL В подлиннике : Персональный сайт Михаила Флёнова

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

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

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

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

содержать не нормализованные данные;
сравнивать состояние данных до, и после изменения.

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

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

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

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

Для создания триггеров используйте оператор CREATE TRIGGER. В операторе указывается таблица, для которой объявляется триггер, событие, для которого триггер выполняется и индивидуальные инструкции для триггера. В общем команда показана в листинге 3.2.

Листинг 3.2. Общий вид команды CREATE TRIGGER


CREATE TRIGGER trigger_name 
ON { table | view } 
[ WITH ENCRYPTION ] 
{ 
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } 
        [ WITH APPEND ] 
        [ NOT FOR REPLICATION ] 
        AS 
        [ { IF UPDATE ( column ) 
            [ { AND | OR } UPDATE ( column ) ] 
                [ ...n ] 
        | IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask) 
                { comparison_operator } column_bitmask [ ...n ] 
        } ] 
        sql_statement [ ...n ] 
    } 
}

Прежде чем мы рассмотрим реальный пример, давайте рассмотрим два замечания. Когда вы создаете триггер, информация о триггере вставляется в системные таблицы sysobjects и syscomments. Если триггер создается с таким же именем, как и существующий, новый триггер перезаписывает существующий. Сервер SQL не поддерживает добавления триггеров объявленных пользователем на системные таблицы, поэтому вы не можете создавать их для системных таблиц.

Сервер SQL не позволяет использовать следующие операторы в теле триггера:

ALTER DATABASE;
CREATE DATABASE;
DISK INIT;
DISK RESIZE;
DROP DATABASE;
LOAD DATABASE;
LOAD LOG;
RECONFIGURE;
RESTORE DATABASE;
RESTORE LOG.

Чтобы не запоминать все эти операторы, проще запомнить, что нельзя изменять структуру базы данных.

3.4.2. Откат изменений в триггере

Объявление триггера может содержать оператор ROLLBACK TRANSACTION даже если не существует соответствующего BEGIN TRANSACTION. Как мы уже говорили, для любого изменения SQL сервер требует транзакции. Если она не указано явно, то создается неявная транзакция. Если выполняется оператор ROLLBACK TRANSACTION, то все изменения в триггере и изменения, которые стали причиной срабатывания триггера — откатываются.

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

Если срабатывает оператор ROLLBACK TRANSACTION, содержимое транзакции откатывается. Если есть операторы, следующие за ROLLBACK TRANSACTION, операторы выполняются. Это может быть не обязательным при использовании команды RETURN;
Если триггер откатывает транзакцию, определенную пользователем, то она откатывается полностью. Если триггер сработал, на выполнение модуля, для модуля команды также отменяются. Последующие операторы модуля не выполняются;
Вы должны минимизировать использование ROLLBACK TRANSACTION в коде триггера. Откат транзакции создает дополнительную работу, потому что все работы, которые не были закончены на данный момент в транзакции, будут незавершенными. Это будет негативно сказываться на производительности. Запускайте транзакцию после того, как все проверено, чтобы не пришлось ничего откатывать в триггере.

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


CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples
FOR UPDATE
AS
 ROLLBACK TRANSACTION

Как всегда, я разбил все действия на строки, чтобы их лучше было видно и легче было читать и изучать тему. В первой строке, после оператора CREATE TRIGGER стоит название. При именовании триггеров я следую следующему правилу:

имя начинается одной или сочетания букв u (update или обновление), i (insert или вставка) или d (delete или удаление). По этим буквам вы легко можете определить, на какие действия срабатывает триггер;
после подчеркивания идет имя таблицы, для которого создается триггер.

После имени идет ключевое слово ON и имя таблицы, для которой создается триггер.

Во второй строке идет ключевое слово FOR и событие, на которое срабатывает триггер. В данном примере указано действие UPDATE, т.е. обновление. И, наконец, после ключевого слова AS идет тело триггера, т.е. команды, которые должны выполняться. В данном примере выполняется только одна команда — ROLLBACK TRANSACTION, т.е. откат.

Теперь попробуем изменить данные в таблице tbPeoples, чтобы сработал триггер:


UPDATE tbPeoples
SET vcFamil='dsfg'

В данном примере мы пытаемся изменить содержимое поля «vcFamil» для всех записей таблицы tbPeoples. Почему пытаемся? Да потому что при изменении срабатывает триггер с откатом транзакции. Выполните выборку данных, чтобы убедиться, что все данные на месте и не изменились:


SELECT * 
FROM tbPeoples

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

3.4.3. Изменение триггера

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

Для обновления триггера используется оператор ALTER TRIGGER. Общий вид оператора можно увидеть в листинге 3.3.

Листинг 3.3. Оператор обновления триггера


ALTER TRIGGER trigger_name 
ON ( table | view ) 
[ WITH ENCRYPTION ] 
{ 
    { ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] 
       [ INSERT ] [ , ] [ UPDATE ] } 
        [ NOT FOR REPLICATION ] 
        AS 
        sql_statement [ ...n ] 
    } 
    | 
    { ( FOR | AFTER | INSTEAD OF ) { [ INSERT ] [ , ] [ UPDATE ] } 
        [ NOT FOR REPLICATION ] 
        AS 
        { IF UPDATE ( column ) 
        [ { AND | OR } UPDATE ( column ) ] 
        [ ...n ] 
        |IF(COLUMNS_UPDATED() { bitwise_operator } updated_bitmask) 
        { comparison_operator } column_bitmask [ ...n ] 
        } 
        sql_statement [ ...n ] 
    } 
}

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


ALTER TRIGGER u_tbPeoples ON dbo.tbPeoples
FOR UPDATE, INSERT
AS
 ROLLBACK TRANSACTION

Как видите, оператор обновления похож на создание триггера. Разница в том, что в первой строке стоит оператор ALTER TRIGGER. Во второй строке произошло изменение, и теперь триггер будет срабатывать не только на обновление (UPDATE), но и на добавление (INSERT).

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


INSERT INTO tbPeoples(vcFamil) 
VALUES('ПЕТЕЧКИН')

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


ALTER TABLE table
 {ENABLE | DISABLE} TRIGGER
 {ALL | trigger_name [,..n]}

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


ALTER TABLE tbPeoples
DISABLE TRIGGER u_tbPeoples

В первой строке мы пишем оператор ALTER TABLE и имя изменяемой таблицы. Во второй строке нужно указать ключевое слово DISABLE (отключить) или ENABLE (включить) и ключевое слово TRIGGER. И, наконец, имя триггера.

Попробуйте теперь добавить запить в таблицу tbPeoples. На этот раз, все пройдет успешно.

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


ALTER TABLE tbPeoples
ENABLE TRIGGER ALL

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

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

Пример удаления триггера:


DROP TRIGGER u_tbPeoples

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

3.4.5. Как работают триггеры?

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

Триггер INSERT

Что происходит, когда срабатывает триггер добавления записей? Давайте рассмотрим выполняемые сервером шаги:

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

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

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

Таблица inserted всегда содержит такую же структуру, что и у таблицы, на которую установлен триггер.

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

Листинг 3.4. Использование таблицы inserted


CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples
FOR INSERT 
AS
 DECLARE @Name varchar(50)
 SELECT @Name=vcName
 FROM inserted
 IF @Name='ВАСЯ'
  BEGIN 
   PRINT 'ОШИБКА'
   ROLLBACK TRANSACTION
  END

В данном примере мы создаем триггер на добавление записей. Внутри триггера мы объявляем переменную @Name типа varchar длиной в 50 символов. В эту переменную мы сохраняем содержимое поля «vcName» таблицы inserted. Далее проверяем, если имя равно Вася, то сообщаем об ошибке и откатываем транзакцию. Иначе, строка будет удачно добавлена.

Давайте для закрепления материала, напишем триггер, который запретит нулевые значения для поля «vcName». Код такого триггера можно увидеть в листинге 3.5.

Листинг 3.5. Запрет нулевых значений в поле с помощью триггера


CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples
FOR INSERT 
AS
 IF EXISTS (SELECT *
            FROM inserted
            WHERE vcName is NULL)
  BEGIN 
   PRINT 'ОШИБКА, вы должны заполнить поле vcName'
   ROLLBACK TRANSACTION
  END

В этом примере мы проверяем, если в таблице inserted есть записи с нулевым значением поля «vcName», то откатываем попытку добавления.

Триггер DELETE

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

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

Удаляемые строки помещаются в таблицу deleted, с помощью которой вы можете увидеть удаляемые строки. Это логическая таблицf, которая ссылается на данные журнала оператора DELETE.

Вы должны учитывать:

когда строки добавляются в таблицу deleted, они еще существуют в таблице базы данных;
для таблицы deleted выделяется память, поэтому она всегда в кэше;
триггер удаления не выполняется на операцию TRUNCATE TABLE (очистка таблицы) потому что эта операция не заносится в журнал и не удаляет строк.

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

Листинг 3.6. Пример запрета удаления с помощью триггера


CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples
FOR DELETE
AS
 IF EXISTS (SELECT *
            FROM deleted
            WHERE vcName='рлр')
  BEGIN 
   PRINT 'ОШИБКА, нельзя удалить этого пользователя'
   ROLLBACK TRANSACTION
  END

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

А что если попытаться удалить несколько записей? Например, в следующем примере удаляются записи две записи:


DELETE 
FROM tbPeoples
WHERE vcName='рлр' or 
     vcName='ВАСИЛИЙ'

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

Посмотрим на еще один пример в котором запрещается удаление генерального директора. Без триггера такое сделать невозможно:


CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples
FOR DELETE
AS
 IF EXISTS (SELECT *
            FROM deleted
            WHERE idPosition=1)
  BEGIN 
   PRINT 'ОШИБКА, нельзя удалить этого пользователя'
   ROLLBACK TRANSACTION
  END

В этом примере, запрещается удаление записи, если поле «idPosition» равно 1. Попробуйте удалить такую запись:


DELETE 
FROM tbPeoples
WHERE idPosition=1

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

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

Триггер UPDATE

Обновление происходит в два этапа – удаление и вставка. Нет, физически в базе данных происходит изменение, это триггер видит два этапа. Поэтому существующие строки помещаются в таблицу deleted (то есть то, что было), а новые данные помещаются в таблицу inserted. Триггер может проверять эти таблицы для определения, какие строки и как могут измениться.

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

Давайте создадим триггер на таблицу tbPeoples, который будет выводить на экран сообщение, если изменяется поле «vcName»


CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples
FOR UPDATE
AS
 IF UPDATE (vcName)
   PRINT 'Я надеюсь, что вы правильно указали имя'

После оператора IF UPDATE, в скобках указано поле, которое необходимо проверить, было ли оно изменено. Если да, то будет выполнен следующий за проверкой оператор. В данном случае, это вывод на экран сообщения с помощью PRINT. Когда указанное поле не изменяется, то оператор конечно же не выполняется. Если нужно выполнить несколько операторов, то объедините их с помощью BEGIN и END.

Следующий запрос тестирует триггер:


UPDATE tbPeoples
SET vcName='ИВАНУШКА'
WHERE vcFamil='ПОЧЕЧКИН'

Убедитесь, что сообщение из триггера выводится на экран.

Давайте с помощью триггера попробуем запретить изменение полей, составляющих ФИО («vcFamil», «vcName» и «vcSurName»). Для этого, если изменено одно из этих полей, то выводим на экран сообщение о запрете и откатываем транзакцию:


CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples
FOR UPDATE
AS
 IF UPDATE (vcName) 
    OR UPDATE (vcFamil)
    OR UPDATE (vcSurname)
  BEGIN
   PRINT 'Нельзя изменять фамилию, имя и отчество'
   ROLLBACK TRANSACTION
  END

С помощью такого запроса легко увидеть, как проверять обновление сразу нескольких полей и выводить несколько операторов. Обратите внимание, что проверку делает именно оператор UPDATE, а не IF UPDATE. Я даже не знаю, почему разработчики SQL Server объединяют эти два оператора. Первый, это логический оператор, а второй – проверка, было ли обновлено поле.

3.4.6. INSTEAD OF

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

Каждая таблица или просмотрщик ограничены одним триггером INSTEAD OF на каждое событие. Вы не можете создавать триггеры INSTEAD OF на просмотрщик у которого включена опция CHECK OPTIONS.

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

Давайте создадим объект просмотра, который будет выбирать фамилию работника и название должности. Назовем этот объект просмотра Peoples:


CREATE VIEW People
AS
 SELECT vcFamil, vcPositionName
 FROM tbPosition ps, tbPeoples pl
 WHERE ps.idPosition=pl.idPosition

Теперь создадим триггер INSTEAD OF на этот объект просмотра, с помощью которого, можно будет добавлять записи и при этом, они корректно будут прописываться, каждая в свою таблицу:

Листинг 3.7. Триггер INSTEAD OF для вставки данных


CREATE TRIGGER i_People ON dbo.People
INSTEAD OF INSERT
AS
 BEGIN
  -- Добавление должности
  INSERT INTO tbPosition (vcPositionName)
  SELECT vcPositionName
  FROM inserted i

  -- Добавление работника
  INSERT INTO tbPeoples (vcFamil, idPosition)
  SELECT vcFamil, idPosition
  FROM inserted i,tbPosition pn
  WHERE i.vcPositionName=pn.vcPositionName
 END

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

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


INSERT INTO tbPeoples (vcFamil, idPosition)
 SELECT vcFamil, idPosition 
 FROM inserted i,tbPosition pn
 WHERE i.vcPositionName=pn.vcPositionName

Попробуйте выполнить следующий запрос на добавление записей в объект просмотра:


INSERT INTO People
VALUES('ИВАНУШКИН', 'Клерк')

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


SELECT * FROM People

При обновлении таблицы есть одна проблема – нужно связать обновляемые данные с существующими. Первым на ум приходит запрос типа:


UPDATE tbPosition
 SET vcPositionName=i.vcPositionName
 FROM tbPosition pn, inserted i
 WHERE i.vcPositionName = pn.vcPositionName

Здесь мы связываем таблицу должностей с таблицей inserted. Но такой запрос никогда не будет выполнен. Почему? В inserted находятся новые значения, а в tbPosition еще старые и названия должностей никогда не свяжутся. Если связать с таблицей deleted, то записи свяжутся, но мы не будем знать новых значений, которые нужно занести в таблицу. Проблему можно решить, но лучшим вариантом будет добавление в объект просмотра ключевых полей:


ALTER VIEW People
AS
 SELECT idPeoples, pl.idPosition, vcFamil, vcPositionName
 FROM tbPosition ps, tbPeoples pl
 WHERE ps.idPosition=pl.idPosition

Теперь INSTEAD OF триггер для обновления данных будет выглядеть, как показано в листинге 3.8.

Листинг 3.8. Обновление связанной вьюшки с помощью триггера


CREATE TRIGGER u_People ON dbo.People
INSTEAD OF UPDATE
AS 
BEGIN
 UPDATE tbPosition
 SET vcPositionName=i.vcPositionName
 FROM tbPosition pn, inserted i
 WHERE i.idPosition=pn.idPosition

 UPDATE tbPeoples
 SET vcFamil=i.vcFamil
 FROM tbPeoples pl, inserted i
 WHERE i.idPeoples=pl.idPeoples
END

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


UPDATE People
SET vcFamil='ИВАНУШКИН', 
   vcPositionName='Генеральный директор'
WHERE idPeoples=40
  AND idPosition=13

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

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

3.4.7. Дополнительно о триггерах

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

Выполнения действий или каскадного обновления или удаления. Целостность ссылок может отличаться при использовании ограничений FOREIGN KEY и REFERENCE в операторе CREATE TABLE. Но триггер выгоден для гарантирования необходимых действий, когда должны быть произведены каскадные удаления или обновления, потому что триггеры более мощные. Если ограничение существует для таблицы с триггером, оно проверяется до выполнения триггера. Если ограничение нарушено, то триггер не работает. Если ограничение не сработает, то с помощью триггера можно реализовать более сложные проверки, которые уж точно будут гарантировать, что данные не нарушат целостность и пользователь внесет только те данные, которые разрешены;
Вы должны учитывать, что в таблицу может вставляться сразу несколько строк. Вы должны учитывать это при написании триггеров, как мы это делали при создании примеров с использованием INSTEAD OF;
Ограничения, правила и значения по умолчанию могут генерировать только стандартные системные ошибки. Если вам нужны собственные сообщения, вы должны использовать триггеры.

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


CREATE TRIGGER iu_tbPeoples ON dbo.tbPeoples
FOR INSERT, UPDATE
AS
 Действие

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

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

Триггеры в MySQL / Хабр

Триггер — это хранимая процедура, которая не вызывается непосредственно, а исполняется при наступлении определенного события ( вставка, удаление, обновление строки ).
Поддержка триггеров в MySQL началась с версии 5.0.2

Синтаксис создания триггера:

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt* This source code was highlighted with Source Code Highlighter.

trigger_name — название триггера
trigger_time — Время срабатывания триггера. BEFORE — перед событием. AFTER — после события.
trigger_event — Событие:
insert — событие возбуждается операторами insert, data load, replace
update — событие возбуждается оператором update
delete — событие возбуждается операторами delete, replace. Операторы DROP TABLE и TRUNCATE не активируют выполнение триггера
tbl_name — название таблицы
trigger_stmt выражение, которое выполняется при активации триггера

Применение

Лог

Исходные данные:

— таблица, за которой мы будем следить
CREATE TABLE `test` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`content` TEXT NOT NULL
) ENGINE = MYISAM
— лог
CREATE TABLE `log` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`msg` VARCHAR( 255 ) NOT NULL,
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`row_id` INT( 11 ) NOT NULL
) ENGINE = MYISAM
— триггер
DELIMITER |
CREATE TRIGGER `update_test` AFTER INSERT ON `test`
FOR EACH ROW BEGIN
INSERT INTO log Set msg = ‘insert’, row_id = NEW.id;
END;* This source code was highlighted with Source Code Highlighter.

Теперь добавьте запись в таблицу test. В таблице log тоже появится запись, обратите внимание на поле row_id, в нем хранится id вставленной вами строки.

Расширенный лог:

Исходные данные:

— Удаляем триггер
DROP TRIGGER `update_test`;
— Cоздадим еще одну таблицу,
— в которой будут храниться резервные копии строк из таблицы test
CREATE TABLE `testing`.`backup` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`row_id` INT( 11 ) UNSIGNED NOT NULL,
`content` TEXT NOT NULL
) ENGINE = MYISAM
— триггеры
DELIMITER |
CREATE TRIGGER `update_test` before update ON `test`
FOR EACH ROW BEGIN
INSERT INTO backup Set row_id = OLD.id, content = OLD.content;
END;
CREATE TRIGGER `delete_test` before delete ON `test`
FOR EACH ROW BEGIN
INSERT INTO backup Set row_id = OLD.id, content = OLD.content;
END
* This source code was highlighted with Source Code Highlighter.

Теперь если мы отредактируем или удалим строку из test она скопируется в backup.

зы: надеюсь статья была интересной и полезной
UPD: для создания триггеров в версии до 5.1.6 требуются полномочия суперпользователя.

Пример использования триггеров SQL для поддержания ссылочной целостности

Ниже написанные триггеры сделаны для базы данных, которая была опубликована ранее (см. Пример создания базы данных из нескольких таблиц на SQL). Хочется отметить то, что все названия триггеров являются русскоязычными. Также напомню, что база данных и триггеры созданы для MS SQL (проверялось).

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

Код триггеров на SQL вместе с описаниями:

— Триггер Ссылцелост_вклады запрещает добавлять записи в таблицу Вклады, если соответствующие значения ид или ид_вклада отсутствуют в родительских таблицах (Клиенты и Вид_вклада)

CREATE TRIGGER Ссылцелост_вид_вклада on [dbo].[Вид_вклада]
after INSERT
as
BEGIN
if not exists (select * from [dbo].[Валюта] INNER JOIN inserted on [dbo].[Валюта].ид_валюта=inserted.ид_валюта)
BEGIN
rollback;
PRINT ‘Отсутствует запись в родительской таблице — Валюта’;
return;
END
END;

— Аналогично триггеры Ссылцелост_вид_кредита, Ссылцелост_вклады, Ссылцелост_кредиты

CREATE TRIGGER Ссылцелост_вид_кредита on [dbo].[Вид_кредита]
after INSERT, UPDATE
as
BEGIN
if not exists (select * from [dbo].[Валюта] INNER JOIN inserted on [dbo].[Валюта].ид_валюта=inserted.ид_валюта)
BEGIN
rollback;
PRINT ‘Отсутствует запись в родительской таблице — Валюта’;
return;
END
END;

CREATE TRIGGER Ссылцелост_вклады on [dbo].[Вклады]
after INSERT, UPDATE
as
BEGIN
if not exists (select * from [dbo].[Клиенты] INNER JOIN inserted on [dbo].[Клиенты].ид=inserted.ид_клиент)
or not exists (select * from [dbo].[Вид_вклада] INNER JOIN inserted on [dbo].[Вид_вклада].ид_вклада=inserted.вид_вклада)
BEGIN
rollback;
PRINT ‘Отсутствует запись в родительской таблице’;
return;
END
END;

CREATE TRIGGER Ссылцелост_кредиты on [dbo].[Кредиты]
after INSERT, UPDATE
as
BEGIN
if not exists (select * from [dbo].[Клиенты] INNER JOIN inserted on [dbo].[Клиенты].ид=inserted.ид_клиент)
or not exists (select * from [dbo].[Вид_кредита] INNER JOIN inserted on [dbo].[Вид_кредита].ид_кредита=inserted.вид_кредита)
BEGIN
rollback;
PRINT ‘Отсутствует запись в родительской таблице’;
return;
END
END;

— Триггер Удаление_клиента при удаление клиента из таблицы Клиенты, удаляет все вклады и кредиты этого клиента в дочерних таблицах (Вклады и Кредиты).

CREATE TRIGGER Удаление_клиента on [dbo].[Клиенты]
after DELETE
AS
BEGIN
IF EXISTS (SELECT * FROM DELETED INNER JOIN [dbo].[Вклады] on [dbo].[Вклады].ид_клиент=DELETED.ид)
OR EXISTS (SELECT * FROM DELETED INNER JOIN [dbo].[Кредиты] on [dbo].[Кредиты].ид_клиент=DELETED.ид)
BEGIN
DELETE FROM [dbo].[Вклады]
WHERE [dbo].[Вклады].ид_клиент = (SELECT ид FROM DELETED)
DELETE FROM [dbo].[Кредиты]
WHERE [dbo].[Кредиты].ид_клиент = (SELECT ид FROM DELETED)
END
END;

— Триггер Удаление_вид_вклада при удаление какого-либо вида вклада (кроме базового) автоматически переведет все вклады на базовый вклад. Для запрета удаления базового вклада существует специальный триггер Запрет_удаления_базового_вклада

CREATE TRIGGER Удаление_вид_вклада on [dbo].[Вид_вклада]
AFTER Delete
AS
BEGIN
IF EXISTS (SELECT * FROM DELETED INNER JOIN [dbo].[Вклады] on [dbo].[Вклады].вид_вклада=DELETED.ид_вклада)
BEGIN
UPDATE [dbo].[Вклады] SET [dbo].[Вклады].вид_вклада=001 WHERE [dbo].[Вклады].вид_вклада = (SELECT ид_вклада FROM DELETED)
END
END;

CREATE TRIGGER Запрет_удаления_базового_вклада on [dbo].[Вид_вклада]
AFTER Delete, update
AS
BEGIN
IF EXISTS (SELECT * FROM DELETED WHERE DELETED.ид_вклада=001)
rollback;
PRINT ‘Запрещено удалять или изменять номер базового вклада’;
return;
END;

CREATE TRIGGER Удаление_вид_кредита on [dbo].[Вид_кредита]
AFTER Delete
AS
BEGIN
IF EXISTS (SELECT * FROM DELETED INNER JOIN [dbo].[Кредиты] on [dbo].[Кредиты].вид_кредита=DELETED.ид_кредита)
BEGIN
DELETE FROM [dbo].[Кредиты]
WHERE [dbo].[Кредиты].вид_кредита = (SELECT ид_кредита FROM DELETED)
END
END;

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

CREATE PROCEDURE avgsum AS
BEGIN
SELECT [dbo].[Вклады].ид_вклад, [dbo].[Вклады].сумма, [dbo].[Вклады].ид_клиент, [dbo].[Вид_вклада].название
FROM [dbo].[Вклады], [dbo].[Вид_вклада]
WHERE ([dbo].[Вклады].сумма >= (select AVG([dbo].[Вклады].сумма) from [dbo].[Вклады])) and
([dbo].[Вклады].вид_вклада=[dbo].[Вид_вклада].ид_вклада)
END;

— Вызов процедуры avgsum :
exec avgsum

— Хранимая функция summa выводит средние значение суммы вклада

CREATE FUNCTION summa() RETURNS int
BEGIN
DECLARE @itog int;
SELECT @itog=AVG([dbo].[Вклады].сумма) from [dbo].[Вклады]
RETURN @itog
END;

— Вызов процедуры avgsum :
exec avgsum

— Представление Предвклады выводит фамилии клиентов, идентификаторы вкладов и суммы вкладов тех записей, у которых сумма вклада больше или равна 30000.

CREATE VIEW Предвклады
AS SELECT Клиенты.ф AS Фамилия, Вклады.ид_вклад AS Идентификатор_вклада, Вклады.сумма AS Сумма
FROM Клиенты, Вклады
WHERE (Вклады.сумма >= 30000) AND (Клиенты.ид=Вклады.ид_клиент)

— Вызов представления Предвклады:
select * from Предвклады

Триггеры в MS SQL Server Обзор триггеров

Главная
О сайте
Политика защиты авторских прав
Контакты

Инструкция CREATE/ALTER TRIGGER советы и хитрости в SQL Server

Платформа SQL Server поддерживает ядро стандарта ANSI с добавлением триггеров типа INSTEAD OF и проверки изменения столбцов. Эта платформа не поддерживает предложения REFERENCING и WHEN. Синтаксис приводится ниже.

{CREATE | ALTER} TRIGGER имя_триггера ON имя_таблицы [WITH ENCRYPTION]

{FOR | AFTER | INSTEAD OF} {[DELETE] [,] [INSERT] [,] [UPDATE]}

[WITH APPEND]

[NOT FOR REPLICATION]

[IF UPDATE(столбец) [{AND | OR} РОАТЕ(столбец)] […]] блок_кода

Где:

(CREATE | ALTER) TRIGGER имя_триггера

Создается новый триггер с именем имя_триггера или изменяется существующий триггер имя_триггера путем добавления или изменения свойств триггера или блока кода. При изменении существующего триггера права доступа и зависимости существующего триггера сохраняются.

ON имя_таблицы

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

WITH ENCRYPTION

Текст инструкции CREATE TRIGGER шифруется так, как это определено в таблице syscomments. Этот параметр полезно использовать для защиты интеллектуальной собственности. Предложение WITH ENCRYPTION не дает использовать триггер в схеме репликации SQL Server.

FOR | AFTER INSTEAD OF

Указывает, когда должен запускаться триггер. (Ключевые слова FOR и AFTER являются синонимами.) Предложение AFTER показывает, что триггер запускается только после запуска успешного выполнения операции по модификации данных (и других каскадно запускаемых действий и проверок ограничений). Триггер INSTEAD OF сходен с триггером BEFORE стандарта ANSI в том, что код триггера может полностью заменить операцию по модификации данных. При этом триггер запускается вместо операции по модификации, которая запустила триггер. Триггеры типа INSTEAD OF DELETE нельзя использовать, если удаление вызывает каскадные действия. Доступ к столбцам TEXT, NTEXT или IMAGE имеют только триггеры INSTEAD OF.

WITH APPEND

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

NOT FOR REPLICATION

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

IF UPDATE (столбец) [{AND | OR) РОАТЕ(столбец)] […]

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

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

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

Платформа SQL Server неявно использует для триггеров форму FOR EACH STATEMENT стандарта ANSI.

При запуске триггера SQL Server записывает значения в две важные псевдотаблицы: deleted и inserted. Они соответственно эквивалентны псевдотаблицам before и after, описанным ранее в разделе «Общие правила», относящемся к стандарту ANSI. Эти таблицы по структуре идентичны таблице, в которой создан триггер, за исключением того, что они содержат старые данные, до выполнения операции по модификации (deleted), и новые значения, имеющиеся в таблице после этой операции (inserted).

При указании предложения AS IF UPDATE (столбец) производится проверка на выполнение операции INSERT или UPDATE в данном столбце или столбцах; это предложение аналогично конструкции с/РШЩстолбец) в стандарте ANSI. Можно указать несколько столбцов, добавив отдельные предложения (УРОЛЩстолбец). Если за предложением AS IF UPDATE (столбец) поставить блок кода Transact-SQL BEGIN…END, можно выполнить в триггере несколько операций Transact-SQL. Это предложение функционально эквивалентно операции IF … THEN … ELSE.

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

Платформа SQL Server не разрешает использовать следующие инструкции в блоке кода Transact-SQL триггера: ALTER, CREATE, DROP, DENY, GRANT, REVOKE, LOAD, RESTORE, RECONFIGURE и TRUNCATE. Кроме того, не разрешаются инструкции DISK и команда UPDA ТЕ STA TISTICS.

Платформа SQL Server позволяет запускать рекурсивные триггеры, используя параметр recursive triggers хранимой системной процедуры sp_dboption. Рекурсивные триггеры в результате своего выполнения запускают самих себя. Например, если триггерINSERT в таблице Т1 выполняет операцию INSERT в таблице Т1, он может выполнить рекурсивную операцию. Поскольку рекурсивные триггеры могут быть опасными, они по умолчанию отключены.

Также SQL Server позволяет использовать вложенные триггеры, до 32 уровней вложенности. Если любой из вложенных триггеров выполняет операцию ROLLBACK, то последующие триггеры не запускаются. Пример вложенных триггеров: Триггер в таблице Т1 запускает операцию над таблицей Т2, в которой также есть триггер, который запускает операцию над таблицей ТЗ. Запуск триггеров отменяется, если формируется бесконечный цикл. Вложенные триггеры можно разрешить при помощи параметра nested triggers хранимой процедуры sp_configure. Если вложенные триггеры отключены, то рекурсивные триггеры также отключены, независимо от соответствующего параметра хранимой процедуры sp_dboption.

В следующем примере мы хотим переадресовать пользовательские действия над таблицей people, особенно транзакции, связанные с обновлением, таким образом, чтобы изменения строк таблицы people записывались вместо этого в таблицу peoplejreroute. (Более сложная форма таблицы people показана в пункте «SQL Server» раздела «Инструкция CREATE/ALTER TABLE».) Наш триггер обновлений будет регистрировать все изменения столбцов 2, 3 и 4 таблицы people и записывать их в таблицу peoplejreroute. Также триггер будет записывать, какой пользователь выполнял транзакцию-обновление и в какое время.

Отметьте, что инструкции CREATE в SQL Server позволяют использовать отложенное разрешение имен (deferred name resolution). Это означает, что команда обрабатывается даже в том случае, если она ссылается на еще не существующий объект базы данных.

триггеров в SQL Server

В этой статье мы рассмотрим триггеры в SQL Server, различные типы триггерных событий, порядок триггеров и НЕ ДЛЯ РЕПЛИКАЦИИ в триггерах. Триггер — это объект базы данных, который запускается автоматически при возникновении события. Есть три разных типа событий.

События DML
DDL-события
Событие LOGON – Триггер входа в систему срабатывает при возникновении события LOGON i.е. когда пользовательский сеанс устанавливается

Триггеры DML в SQL Server

Триггеры DML в SQL Server срабатывают при возникновении события DML. т.е. когда данные вставляются / обновляются / удаляются в таблице пользователем.

Создание триггеров для события DML

Давайте создадим несколько примеров таблиц и триггеров в SQL Server.

СОЗДАТЬ местоположения ТАБЛИЦЫ (LocationID int, LocName varchar (100))

СОЗДАТЬ ТАБЛИЦУ LocationHist (LocationID int, ModifiedDate DATETIME)

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

Ниже приведен пример синтаксиса для создания триггера DML для события обновления.

CREATE TRIGGER TR_UPD_Locations ON Locations

FOR UPDATE

NOT FOR REPLICATION

BEGIN

INSERT INTO LocationHist

000 SELECT Location

9000 FROM

Эти триггеры создаются на уровне таблицы.После успешного создания триггера мы можем увидеть триггеры, перейдя в папку Triggers на уровне таблицы. См. Изображение ниже.

Вместо триггеров в SQL Server

Эти триггеры срабатывают перед событием DML, и фактические данные в таблице не изменяются.

Например, если мы указываем вместо триггера для удаления в таблице, когда для таблицы выдается оператор удаления, запускается триггер вместо, и выполняется блок T-SQL внутри триггеров в SQL Server, но фактическое удаление выполняет не произойдет.

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

CREATE TRIGGER TR_DEL_Locations ON Locations

INSTEAD OF DELETE

BEGIN

Выберите «Sample вместо триггера» в качестве [Message]

END

Если есть несколько триггеров вместе с триггером вместо триггера в таблице, триггер вместо триггера запускается первым в порядке
ВМЕСТО триггеров можно создавать на представлениях
мы можем определить только один вместо триггера для каждого оператора INSERT, UPDATE или DELETE в таблице или представлении

Включение и отключение триггеров DML для таблицы

Перейдите в папку триггеров на уровне таблицы, выберите триггер, щелкните триггер правой кнопкой мыши и нажмите Включить / Отключить , чтобы включить или отключить триггер с помощью SSMS .

Отключение определенного триггера SQL Server для таблицы с помощью T-SQL.

ОТКЛЮЧИТЬ ТРИГГЕР TR_UPD_Locations2 для местоположений

Включение определенного триггера для таблицы с помощью T-SQL.

ВКЛЮЧИТЬ ТРИГГЕР TR_UPD_Locations2 для местоположений

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

ВКЛЮЧИТЬ ТРИГГЕР ВСЕХ В местоположениях

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

ОТКЛЮЧИТЬ ТРИГГЕР ВСЕХ В местоположениях

Падение триггера на стол.

Чтобы поместить триггер DML в таблицу с помощью SQL Server Management Studio, перейдите в папку Triggers под таблицей. Выберите таблицу, которую хотите удалить, щелкните правой кнопкой мыши по триггеру и выберите Удалить . Щелкните Ok .

T-SQL для сброса триггера на таблицу.

ТРИГГЕР КАПЛИ TRL_UPD_Locations2

Удаление таблицы приведет к удалению всех триггеров SQL Server в таблице вместе с таблицей.

Триггеры DDL

Триггеры DDL в SQL Server запускаются при событиях DDL. то есть против операторов create, alter и drop и т. д. Эти триггеры создаются на уровне базы данных или на уровне сервера в зависимости от типа события DDL.

Эти триггеры полезны в следующих случаях.

Предотвратить изменение схемы базы данных
Аудит изменений схемы базы данных
Чтобы отреагировать на изменение схемы базы данных

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

Ниже приведен пример синтаксиса для создания триггера DDL для события ALTER TABLE в базе данных, который записывает все операторы alter для таблицы.Вы можете написать свой собственный код для отслеживания или аудита изменений схемы с помощью EVENTDATA ().

СОЗДАТЬ ТАБЛИЦУ TableSchemaChanges (ChangeEvent xml, DateModified datetime)

СОЗДАТЬ ТРИГГЕР TR_ALTERTABLE В БАЗЕ ДАННЫХ

ДЛЯ ALTER_TABLE

000 НАЧАЛО

000

НАЧАЛО

ВНУТР.

КОНЕЦ

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

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

Чтобы просмотреть триггеры уровня базы данных, войдите на сервер с помощью студии управления SQL Server и перейдите к базе данных. Разверните базу данных и перейдите к Программируемость -> Триггеры базы данных.

Чтобы просмотреть триггеры на уровне сервера, войдите на сервер с помощью SSMS и перейдите к Server Objects , а затем к папке Triggers .

Включение и отключение триггеров DDL

Используйте приведенный ниже синтаксис T-SQL, чтобы отключить или включить триггер DDL на уровне базы данных.

ВКЛЮЧИТЬ ТРИГГЕР TR_DATABASEEVENTS НА БАЗЕ ДАННЫХ

ОТКЛЮЧИТЬ ТРИГГЕР TR_DATABASEEVENTS НА БАЗЕ ДАННЫХ

Используйте приведенный ниже синтаксис T-SQL для удаления триггера DDL, созданного на уровне базы данных.

ТРИГГЕР КАПЛИ TR_DATABASEEVENTS НА БАЗЕ ДАННЫХ

Триггеры LOGON в SQL Server

Эти триггеры в SQL Server срабатывают в ответ на событие LOGON. Триггеры LOGON срабатывают после успешной аутентификации и до установления сеанса пользователя.

Триггеры LOGON создаются на уровне сервера и полезны в следующих случаях.

Для аудита активности входа
Для контроля активности входа в систему

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

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

СОЗДАТЬ ТАБЛИЦУ LoginActivity (LOGONEvent XML, Logintime datetime)

СОЗДАТЬ ТРИГГЕР [track_logins] НА ВСЕХ СЕРВЕРАХ

ДЛЯ ВХОДА КАК

НАЧАТЬ

ВСТАВИТЬ

ВСТАВИТЬ ВСТАВИТЬ

ВСТАВИТЬ ВХОД 9000 (

ВСТАВИТЬ 9000) )

КОНЕЦ

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

Включение и отключение триггеров LOGON

Используйте приведенный ниже синтаксис T-SQL, чтобы отключить или включить триггер LOGON.

ВКЛЮЧИТЬ TRIGGER track_logins НА ВСЕХ СЕРВЕРЕ

ОТКЛЮЧИТЬ TRIGGER track_logins НА ВСЕХ СЕРВЕРАХ

Используйте приведенный ниже синтаксис T-SQL, чтобы отбросить триггер LOGON.

DROP TRIGGER track_logins НА ВСЕХ СЕРВЕРЕ

Прямая рекурсия

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

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

140002 140003

УСТАНОВИТЬ ANSI_NULLS НА

УСТАНОВИТЬ QUOTED_IDENTIFIER НА

СОЗДАТЬ ТАБЛИЦУ [dbo].[Locations] (

[LocationID] [int] NULL,

[LocName] [varchar] (100) NULL,

DateUpdated datetime

) ON [PRIMARY]

INSERT INTO Locations VALUES 1, ‘Richmond Road’, NULL)

CREATE TRIGGER TR_UPD_Locations ON Locations

FOR UPDATE

BEGIN

Update Locations set DateUpdated = GETDATE

00025 ()

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

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

Чтобы изменить параметр RECURSIVE_TRIGGERS с помощью SSMS, перейдите к базе данных, щелкните базу данных правой кнопкой мыши и выберите «Свойства ». Щелкните Параметры и измените настройку на нужный вариант.

Чтобы установить RECURSIVE_TRIGGERS OFF с помощью T-SQL, используйте инструкцию ниже и замените имя базы данных именем своей базы данных.

ALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS OFF WITH NO_WAIT

Чтобы установить RECURSIVE_TRIGGERS ON с помощью T-SQL, используйте инструкцию ниже и замените имя базы данных именем своей базы данных.

ALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS ON WITH NO_WAIT

Косвенная рекурсия

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

Ниже приведен пример триггера для косвенной рекурсии.

140002 140003

14000

СОЗДАТЬ ТАБЛИЦУ Temp1 (id int)

INSERT INTO Значения Temp1 (1), (2)

CREATE TABLE Temp2 (id int)

Значения Temp2 (1), (2)

CREATE TRIGGER TR_Temp1 on Temp1

FOR UPDATE

BEGIN

UPDATE TEMP2 set ID = ‘5’ from where id in (select id in (select id)

END

CREATE TRIGGER TR_Temp2 on Temp2

FOR UPDATE

BEGIN

UPDATE Temp1 установлен ID = ‘5’, где id в

вставлен из

Теперь, когда мы обновляем значение в таблице Temp1, запускается триггер TR_Temp1, который обновляет таблицу Temp2.TR_Temp2 запускается и обновляет таблицу Temp1, что вызывает повторное срабатывание TR_Temp1.

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

EXEC sp_configure ‘вложенные триггеры’, 0;

ГО

Порядок запуска SQL Server

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

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

Ниже приведен пример синтаксиса для установки первого порядка срабатывания триггера для оператора INSERT.

140002 140003

СОЗДАТЬ ТАБЛИЦУ TriggerOrderTest (id int)

CREATE TRIGGER TR_1 ON TriggerOrderTest

FOR INSERT

BEGIN

END ‘First Trigger

END’ First Trigger PRINT ‘

TR_2 ON TriggerOrderTest

FOR INSERT

BEGIN

PRINT ‘Second Trigger’

END

CREATE TRIGGER TR_3 ON Trigger3000 INSERT

000

000 PRINT

000

Trigger ‘

END

sp_settriggerorder @triggername =’ TR_3 ‘

, @order =’ FIRST ‘

, @stmttype =’ INSERT ‘

Теперь, когда данные вставляются в таблицу «TriggerOrderTest», происходит событие INSERT, и сначала срабатывает триггер TR_3.

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

Ниже приведен пример синтаксиса для установки порядка запуска DDL.

sp_settriggerorder @triggername = ‘DDL_3’

, @order = ‘FIRST’

, @stmttype = ‘ALTER_TABLE’

, @namespace = ‘DATABASE’

НЕ ДЛЯ РЕПЛИКАЦИИ

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

Например, если вы реплицируете как Locations, так и LocationHist. Теперь, когда вы обновляете запись в Location, срабатывает триггер, вставляет запись в таблицу истории. Когда эти изменения синхронизируются с другим концом (подписчиками), нет необходимости повторно запускать триггер. Таким образом, если мы помечаем триггер как «НЕ ДЛЯ ПОВТОРЕНИЯ», триггер не срабатывает, когда агент репликации синхронизирует изменения, а срабатывает только для изменений данных, выполненных пользователем.

Ниже приведен пример синтаксиса для создания триггеров в SQL Server без репликации.

CREATE TRIGGER TR_UPD_Locations ON Locations

FOR UPDATE

NOT FOR REPLICATION

BEGIN

INSERT INTO LocationHist

000 SELECT Location

9000 FROM

Если вы хотите, чтобы триггеры в SQL Server срабатывали, когда данные синхронизации агента репликации меняются на другой конец, просто создайте триггер, не указывая «НЕ ДЛЯ ПОВТОРКИ».

SQL Server DBA, разработчик с большим опытом в администрировании, разработке, настройке производительности, мониторинге, технологиях высокой доступности и аварийного восстановления SQL Server

Последние сообщения Ранги Бабу (посмотреть все) ,

Обзор триггеров базы данных SQL Server

Содержание

Обзор

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

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

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

Триггер базы данных — это хранимая процедура, которая вызывается автоматически при наступлении заранее определенного события. Триггеры базы данных позволяют администраторам баз данных (Data Base Administrators) создавать дополнительные связи между отдельными базами данных. Другими способами можно определить триггер для выполнения до или после операции INSERT , UPDATE или DELETE либо один раз для каждой измененной строки, либо один раз для каждого оператора SQL. Если происходит событие триггера, функция триггера вызывается в подходящее время для обработки события.

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

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

Зачем нужны триггеры?

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

Мы можем отправить клиенту товар бесплатно вместе с заказом, если его общая сумма превышает 1000 долларов США. Будет создан триггер для проверки общей суммы заказа по завершении заказа, чтобы увидеть, нужно ли вставлять дополнительную строку заказа.
В банковском сценарии, когда делается запрос на снятие наличных в пункте выдачи наличных, хранимая процедура создает запись в таблице выписок клиента для снятия, и триггер автоматически уменьшает баланс по мере необходимости. Триггером также может быть точка, в которой выполняется проверка баланса клиента, чтобы убедиться, что на балансе достаточно средств для вывода средств. Имея триггер в таблице выписок, мы уверены, что любая сделанная запись выписки, будь то снятие или депозит, будет проверяться и обрабатываться в одном центральном месте.

Примечание : W e здесь обсуждается только целостность данных, а не ссылочная целостность.

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

Diagram

Рисунок 1

Хранимая процедура A обновляет TableA .
Запускает триггер из TableA .
Определенный триггер в TableA обновляет TableB .
TableB имеет спусковой крючок, который срабатывает.
Этот триггер из TableB обновляет TableA .

Создание и использование триггера

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

CREATE TRIGGER Заявление

Оператор CREATE TRIGGER определяет триггер в базе данных.

Призыв

Этот оператор может быть встроен в прикладную программу или выдан с помощью динамических операторов SQL. Это исполняемый оператор, который может быть динамически подготовлен только в том случае, если для пакета действует режим выполнения DYNAMICRULES (SQLSTATE 42509).

Авторизация

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

ALTER привилегия для таблицы, для которой определен триггер BEFORE или AFTER
CONTROL привилегия для представления, в котором определен INSTEAD OF TRIGGER
Определитель представления, для которого Триггер INSTEAD OF определен
ALTERIN привилегией в схеме таблицы или представления, для которого определен триггер
SYSADM или DBADM , и одно из:
- IMPLICIT_SCHEMA полномочия для базы данных, если неявное или явное имя схемы триггера не существует
- CREATEIN привилегия для схемы, если имя схемы триггера ссылается на существующую схему

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

Синтаксис триггера

 CREATE TRIGGER name ON table
   [С ШИФРОВАНИЕМ]
   [ДЛЯ / ПОСЛЕ / ВМЕСТО]
   [ВСТАВИТЬ, ОБНОВИТЬ, УДАЛИТЬ]
   [НЕ ДЛЯ ПОВТОРЕНИЯ]
ТАК КАК
НАЧАТЬ

...
КОНЕЦ

Пример образца

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





СОЗДАТЬ ТРИГГЕР [TRIGGER_ALTER_COUNT] НА [dbo].[TblTriggerExample]
ДЛЯ ВСТАВКИ, ОБНОВЛЕНИЯ
ТАК КАК
НАЧАТЬ
ОБЪЯВИТЬ @TransID VARCHAR (36)
 ВЫБЕРИТЕ @TransID = TransactionID ИЗ ВСТАВЛЕННОГО
 ОБНОВЛЕНИЕ [dbo]. [TblTriggerExample] УСТАНОВИТЬ AlterCount = AlterCount + 1
          , LastUpdate = GETDATE ()
    ГДЕ TransactionID = @TransID
КОНЕЦ

Рисунок 2 (таблица SQL)

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

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

Передовая практика

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

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

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

Заключение

Надеюсь, эта статья будет вам полезна. Наслаждайтесь!

История

7 ^-е Август 2009 г .: Начальная должность

Пример триггера SQL в SQL Server 2008

Что такое триггер SQL Server

Триггер MS SQL может содержать коды sql, которые автоматически выполняются механизмом MS SQL Server при возникновении определенного события.

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

Событиями, запускающими триггеры SQL Server, которые фактически хранятся в кодах t-sql, являются инструкции sql INSERT, UPDATE и DELETE, выполняемые в таблице базы данных sql.

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

Разработчики SQL также могут создавать триггеры sql, которые будут работать вместо команд Insert, Update, Delete, используя подсказку INSTEAD OF во время создания триггера SQL Server.
Вместо триггеров содержатся коды t-sql, которые будут запускаться вместо исходного триггерного события.

Пример триггера SQL Server

Давайте продолжим наше руководство по триггерам SQL с помощью примера триггера SQL. Мы будем управлять этим случаем с помощью триггера в таблице базы данных SQL Server.
Вот сценарий или бизнес-кейс.
В нашем примере компании имеется таблица Customers в экземпляре базы данных SQL Server.
Операции продаж компании клиентам хранятся в таблице базы данных sql под названием Sales .
Общая сумма каждой продажи хранится в поле [Чистая сумма] в таблице sql Sales.

Бизнес-требования следующие:
Если общая сумма продаж клиента меньше 10 000 $, отметьте поле приоритета клиента цифрой 3.
Если общая сумма составляет от 10 000 до 50 000, тогда приоритет клиента должен быть установлен на 2.
Если для этого клиента достигнута большая сумма продаж, в поле базы данных приоритета клиентов должно быть указано 1.
Обратите внимание: если транзакция продажи еще не была создана, этот покупатель не будет иметь никакого приоритета (поле sql приоритета будет NULL).

Вот дизайн базы данных sql или сценарии создания таблиц sql, которые будут использоваться в примере триггера mssql 2008.
Обратите внимание, что поле приоритета клиента, которое будет обновлять триггер sql, находится в таблице «Клиенты».

 СОЗДАТЬ ТАБЛИЦЫ клиентов (
 CustomerId smallint identity (1,1), 
 Имя nvarchar (255), 
 Приоритет tinyint 
 ) СОЗДАТЬ ТАБЛИЦУ Продажи (
 Идентификатор транзакции smallint (1,1), 
 CustomerId smallint, 
 [Чистая сумма] int, 
 Завершено долото 
 )

Сначала мы создадим SQL Server ПОСЛЕ триггера.И этот триггер AFTER будет срабатывать после каждого оператора sql INSERT, UPDATE и DELETE.

Итак, вот определение триггера SQL Server в нашем примере.
Вот как мы создаем триггер в sql-кодах.

СОЗДАТЬ ТРИГГЕР dbo.Update_Customer_Priority ON dbo.Sales ПОСЛЕ ВСТАВКИ, ОБНОВЛЕНИЯ, УДАЛЕНИЯ AS ... - здесь скоро закончим

Теперь нам следует подумать об обновлении всех клиентов, затронутых событием триггера sql (выполнение инструкций Insert, Update или Delete в таблице sql Sales).
Таким образом, код t-sql внутри кода триггера SQL Server будет содержать блок кода sql, аналогичный показанному ниже:

С КТР КАК ( выберите CustomerId из вставленного союз выберите CustomerId из удаленного ) ОБНОВЛЕНИЕ клиентов НАБОР Priority = ... - скоро здесь закончим ОТ Заказчиков c INNER JOIN CTE НА CTE.CustomerId = c.CustomerId

Оператор sql CTE select возвращает список клиентов, на которых воздействуют команды Insert, Delete и Update sql.

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

выберите CustomerId, SUM ([Net Amount]) Итого из продаж внутреннее соединение CTE на CTE.CustomerId = Sales.CustomerId где Завершено = 1 группа по продажам.CustomerId

Код TSQL триггера SQL ПОСЛЕ вставки, обновления, удаления

И теперь мы готовы объединить эти блоки кода t-sql для создания общего решения.
Решение фактически состоит из одного триггера sql в SQL Server 2005 или SQL Server 2008 и т. Д.
Результирующий триггер SQL, работающий с моим экземпляром базы данных MS SQL Server 2008 R2, выглядит следующим образом:

 СОЗДАТЬ TRIGGER dbo.Update_Customer_Priority 
 ПО dbo.Sales 
 ПОСЛЕ ВСТАВКИ, ОБНОВЛЕНИЯ, УДАЛЕНИЯ 
 КАК С КТР КАК (
 выберите CustomerId из вставленного 
 союз 
 выберите CustomerId из удаленного 
 ) 
 ОБНОВЛЕНИЕ клиентов 
 НАБОР 
 Приоритет = 
 чехол 
 когда t.Total <10000, то 3 
 когда t Всего от 10000 до 50000, то 2 
 когда t Итого> 50000, то 1 
 когда т.Итого: NULL, затем NULL 
 конец 
 ОТ Заказчиков c 
 INNER JOIN CTE НА CTE.CustomerId = c.CustomerId 
 LEFT JOIN (
)
выберите 
 Sales.CustomerId, 
 SUM ([Net Amount]) Итого 
 из продаж 
 внутреннее соединение CTE на CTE.CustomerId = Sales.CustomerId 
 где 
 Завершено = 1 
 группа по Sales.CustomerId 
 ) t ON t.CustomerId = c.CustomerId
 GO

Пример триггера SQL Server ПОСЛЕ вставки, обновления, удаления

Как работает триггер SQL Server?

Чтобы завершить это учебное пособие по триггеру sql, заполним таблицу SQL Customers образцами данных с помощью оператора Insert.

вставить в «Клиенты» выберите N'MS SQL Server Team ', NULL вставить в «Клиенты» выберите N'MS Windows Team ', NULL вставить в «Клиенты» выберите N'MS Internet Explorer Team ', NULL

После того, как мы вставим транзакцию продажи для клиента 1 на сумму 5000 $, используя оператор INSERT:

вставить в Продажи выберите 1, 5000, 1

Соответствующее поле приоритета клиента обновляется как 3 триггером SQL Server.

Теперь вы можете выполнить приведенный ниже оператор T-SQL Insert, который запустит оператор обновления sql для таблицы Customers в поле приоритета клиента 2.

вставить в Продажи выберите 2, 45000, 1

Еще одна команда tsql, которую мы выполним вместе, выглядит следующим образом:

вставить в Продажи выберите CustomerId, 7500, 1 из Customers

Этот оператор t-sql INSERT добавит более 1 строки в таблицу транзакций sql Sales.
Фактически, приведенный выше оператор создает по одной строке для каждого клиента в одном операторе одновременно.
Результат выполнения оператора sql SELECT для обеих таблиц базы данных sql показан на снимке экрана ниже.

Теперь вы можете выполнить sql UPDATE все транзакции продаж и установить для параметра Завершено значение false.
В этом случае мы предполагаем, что транзакция еще не завершена.
Таким образом, значения полей приоритета всех клиентов должны быть равны NULL.

обновить набор продаж Завершено = 0

И результат триггера SQL такой же, как мы и предполагали.

Надеюсь, разработчикам t-sql понравится этот короткий учебник по триггеру MSSQL с примерами.
Чтобы узнать о другом триггере SQL Server в примере SQL Server 2008, обратитесь к руководству по t-sql. Регистрация изменений цен с помощью триггера SQL Server в SQL Server 2008.

Триггеры в SQL Server

Триггер SQL — это объект базы данных, который срабатывает при возникновении события в базе данных. Мы можем выполнить SQL-запрос, который «что-то сделает» в базе данных, когда в таблице базы данных произойдет изменение, например, вставка, обновление или удаление записи. Например, триггер может быть установлен при вставке записи в таблицу базы данных. Например, если вы хотите увеличить количество блогов в таблице отчетов, когда новая запись вставляется в таблицу блогов, мы можем создать триггер в таблице блогов на INSERT и обновить таблицу отчетов, увеличив количество блогов до 1.

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

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

Триггер DDL
Триггер DML

Триггеры DDL

Триггеры DDL срабатывают в ответ на DDL (Data). командные события, которые начинаются с Create, Alter и Drop, такие как Create_table, Create_view, drop_table, Drop_view и Alter_table.

Код DDL-триггера

создать триггер безопасности
в базе данных
для
create_table, alter_table, drop_table
as
print’вы не можете создавать, удалять и изменять таблицу в этой базе данных ‘
откат;

Когда мы создаем, изменяем или удаляем любую таблицу в базе данных, появляется следующее сообщение:

Триггеры DML

Триггеры DML запускаются в ответ на командные события DML (язык манипулирования данными), которые запускаются с помощью Вставить, Обновить и Удалить.Как insert_table, Update_view и Delete_table.

создать триггер глубокий
на emp
для
вставить, обновить, удалить
как
print’вы не можете вставлять, обновлять и удалять эту таблицу i ‘
откат;

Когда мы вставляем, обновляем или удаляем таблицу в базе данных, появляется следующее сообщение:

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

AFTER Triggers

Триггеры AFTER выполняются после действия оператора INSERT, UPDATE или DELETE.

создать вставку триггера
в emp
после вставки
как
начало
вставить в значения empstatus («активные»)
конец

ВМЕСТО триггеров

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

CREATE TRIGGER instoftr
ON v11
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO emp
SELECT I.id, I.names
FROM INSERTED I
INSERT INTO emp1values I.id1, I.name1
FROM INSERTED I
END

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

вставить в значения v11 (1, ‘d’ , ‘dd’)

Вы можете увидеть обе таблицы с помощью следующего запроса:

select * from emp
select * from emp1values

Summary

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

Transact-SQL | Триггеры

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

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

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

Триггеры в SQL — CodeTown.ru

Общие сведения

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

Пример работы в SQL

Триггер на взаимодействие таблиц

Ключевые слова OLD и NEW

Заключение

Поделиться ссылкой:

Похожее

3.4. Триггеры — Transact-SQL В подлиннике : Персональный сайт Михаила Флёнова

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

3.4.2. Откат изменений в триггере

3.4.3. Изменение триггера

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

3.4.5. Как работают триггеры?

Триггер INSERT

Триггер DELETE

Триггер UPDATE

3.4.6. INSTEAD OF

3.4.7. Дополнительно о триггерах

Триггеры в MySQL / Хабр

Синтаксис создания триггера:

Применение

Лог

Расширенный лог:

Пример использования триггеров SQL для поддержания ссылочной целостности

Триггеры в MS SQL Server Обзор триггеров

Инструкция CREATE/ALTER TRIGGER советы и хитрости в SQL Server

триггеров в SQL Server

Триггеры DML в SQL Server

Создание триггеров для события DML

Вместо триггеров в SQL Server

Включение и отключение триггеров DML для таблицы

Падение триггера на стол.

Триггеры DDL

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

Включение и отключение триггеров DDL

Триггеры LOGON в SQL Server

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

Включение и отключение триггеров LOGON

Прямая рекурсия

Косвенная рекурсия

Порядок запуска SQL Server

НЕ ДЛЯ РЕПЛИКАЦИИ

Обзор триггеров базы данных SQL Server

Содержание

Обзор

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

Зачем нужны триггеры?

Создание и использование триггера

CREATE TRIGGER Заявление

Призыв

Авторизация

Синтаксис триггера

Пример образца

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

Передовая практика

Заключение

История

Пример триггера SQL в SQL Server 2008

Что такое триггер SQL Server

Пример триггера SQL Server

Код TSQL триггера SQL ПОСЛЕ вставки, обновления, удаления

Как работает триггер SQL Server?

Триггеры в SQL Server

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

Триггеры DDL

Триггеры DML

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

Summary

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

Рубрики

Рубрики