Что такое триггер в sql: Триггеры в SQL — CodeTown.ru

Триггеры в 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 были продемонстрированы. Если у вас остались вопросы, то оставляйте их в комментариях.

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

Похожее

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. Поэтому для каждой инструкции UPDATE, вы

Триггеры в 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 требуются полномочия суперпользователя.

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 запроса: добавление должности работника и самого работника. Первый запрос достаточно прост, потому что достаточ

Триггер (базы данных) — это… Что такое Триггер (базы данных)?

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

Момент запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанного с ним события; например, до добавления записи) или AFTER (после события). В случае, если триггер вызывается до события, он может внести изменения в модифицируемую событием запись (конечно, при условии, что событие — не удаление записи). Некоторые СУБД накладывают ограничения на операторы, которые могут быть использованы в триггере (например, может быть запрещено вносить изменения в таблицу, на которой «висит» триггер, и т. п.)

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

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

Пример (Oracle):

 /* Триггер на уровне таблицы */
 CREATE OR REPLACE TRIGGER DistrictUpdatedTrigger
 AFTER UPDATE ON district
 BEGIN 
  INSERT INTO info VALUES ('table "district" has changed');
 END;

В этом случае для отличия табличных триггеров от строчных вводится дополнительные ключевые слова при описании строчных триггеров. В Oracle это словосочетание FOR EACH ROW.

Пример:

 /* Триггер на уровне строки */
 CREATE OR REPLACE TRIGGER DistrictUpdatedTrigger
 AFTER UPDATE ON district FOR EACH ROW
 BEGIN 
  INSERT INTO info VALUES ('one string in table "district" has changed');
 END;

Различия SQL между хранимой процедурой и триггерами

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

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

мы можем создать триггер такой:

CREATE TRIGGER TriggerName
ON [dbo].[TableName]
FOR DELETE, INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON
END

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

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

мы можем создать хранимую процедуру, как это:

CREATE PROCEDURE dbo.Sample_Procedure 
    @param1 int = 0,
    @param2 int  
AS
    SELECT @param1,@param2 
    RETURN 0;

различия в обеих тогда

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

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

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

7

автор: Avadhesh Kumar Verma

Выполнение триггеров в определенном порядке

Проблема, с которой я столкнулся, довольно известна. Я имею два триггера, которые должны отработать в предопределенном порядке, т.е. триггер a должен выполниться сначала, а после него должен отработать триггер b. Вы можете поинтересоваться, а почему бы не иметь один триггер, который объединит триггеры a и b в один триггер ab? Хороший вопрос. К сожалению, триггер a используется для репликации (for replication), в то время как более поздний триггер — не для репликации, что определяет наличие именно двух триггеров.

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

create table [trigger priority] (
[id] [int] identity (1, 1) not null ,
[first] [int] null ,
[second] [int] null ,
[last] [int] null ,
[status] [char] (1) null
) on [primary]
go

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

create trigger trg_updatefirst on dbo.[trigger priority]
for insert
as
declare @id int, @val as float
select @id = id from inserted
select @val = floor(rand() * 10)
update [trigger priority] set [first] = @val where id = @id
insert into triggerlog (triggername) values ('trg_updatefirst')

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

create trigger trg_updatesecond on dbo.[trigger priority]
for insert
as
declare @id int
select @id = id from inserted
update [trigger priority] set [second] = [first] where id = @id
insert into triggerlog (triggername) values ('trg_updatesecond')

Последний триггер используется для обновления столбца last суммой значений столбцов first и second.

create trigger trg_updatelast on dbo.[trigger priority]
for insert
as
declare @id int
select @id = id from inserted
update [trigger priority] set [last] = [first] + [second] where id = @id
insert into triggerlog (triggername) values ('trg_updatelast')

Теперь, чтобы получить ожидаемые результаты, триггеры trg_updatefisrt, trg_updatesecond и trg_updatelast должны выполняться в вышеперечисленном порядке. Что вы об этом думаете? Каков будет порядок? Случайным образом или в некотором особом порядке?

Прежде чем ответить на этот вопрос, давайте посмотрим, что произойдет. После вставки одной записи в таблицу [trigger priority] первый столбец содержит пятерку, что нормально, и второй тоже — 5, что также правильно. Однако в последнем столбце находится null! Почему? Разве не должно быть 10?

Теперь давайте проверим таблицу triggerlog. Порядок столбцов — trg_updatelast, trg_updatefirst и trg_updatesecond. После небольшого исследования выясняется, что триггеры выполняются в том порядке, в котором они были созданы. Таким образом, в идеале триггеры следует создавать в таком порядке: trg_updatefirst, trg_updatesecond и trg_updatelast. Это ни в коем случае не является простой задачей в силу динамического характера процесса разработки, который по большей части не контролируется разработчиками.

Другой вопрос. Как на более поздней стадии Вы собираетесь узнать о порядке срабатывания триггеров?

select * from sysobjects where xtype ='tr' order by id

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

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

sp_settriggerorder [@triggername =] 'triggername'
, [@order =] 'значение'
, [@stmttype =] 'statement_type'

Первый параметр — это имя триггера, а второй параметр — порядок. Этот порядок может принимать одно из трех значений: » first (первый)», «none (ни первый, ни последний)», и » last (последний)». Последний параметр представляет собой тип триггера, т.е. insert, update или delete. Это означает, что Вы не можете позволить себе иметь четыре или пять триггеров одного и того же типа, которые бы выполнялись в определенном порядке. Однако это вряд ли встречается в практике. По крайней мере, я не встречал еще так много триггеров одного типа на одной таблице.

Этот порядок не может быть установлен опциями alter trigger или create trigger. Если оператор alter trigger изменяет первый или последний триггер, то первоначально установленные на триггере атрибуты first или last удаляются, и значение заменяется на none. Значение порядка должно быть переустановлено с помощью хранимой процедуры sp_settriggerorder.

Владелец триггера и таблицы, на которой определен триггер, имеет разрешение на выполнение sp_settriggerorder. Члены ролей db_owner и db_ddladmin в текущей базе данных, так же как серверная роль sysadmin, могут выполнять эту хранимую процедуру.

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

select objectproperty(object_id(' trg_updatefirst '), 'execisfirstinserttrigger') execisfirstinserttrigger')

укажет, является ли trg_updatefirst первым (first) триггером на вставку?

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

В sql server 2005 имеется дополнительный параметр в sp_settriggerorder, который должен сообщить, является ли данный триггер триггером базы данных или триггером сервера. Это обусловлено тем, что в sql server 2005 Вы можете написать также ddl триггеры.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Триггеры DDL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

.

триггеров в SQL Server с примерами

Вернуться к: Учебное пособие по SQL Server для начинающих и профессионалов

Триггеры в SQL Server с примерами

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

1. Что такое триггеры в SQL Server?
2. Типы триггеров в SQL Server
3. Что такое триггеры DML в SQL Server?
4. Примеры для понимания триггера DML
5. Где триггеры создаются в SQL Server?
6. Зачем нам нужны триггеры DML в SQL Server?
7. Несколько примеров для понимания вышеуказанных концепций

Что такое триггеры в SQL Server?

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

Есть два типа триггеров. Они следующие:

1. Вместо триггеров: Триггер «Вместо» срабатывает в SQL Server вместо триггерного действия. То есть вместо таких операторов DML, как Insert, Update и Delete, будет срабатывать этот триггер.
2. После триггеров: Триггеры после срабатывания в SQL Server выполняются после действия триггера. То есть, как только оператор DML (например, Insert, Update и Delete) завершит свое выполнение, этот триггер будет запущен.

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

В SQL Server доступно четыре типа триггеров. Они следующие:

1. Триггеры DML — язык обработки данных.
2. Триггеры DDL — язык определения данных
3. Триггеры CLR — среда CLR
4. Триггеры входа в систему

В этой статье мы собираемся обсудить триггеры DML , а остальные обсудим в наших следующих статьях.

Что такое триггеры DML в SQL Server?

Как мы знаем, DML означает язык обработки данных и предоставляет операторы Insert, Update и Delete для выполнения соответствующих операций с таблицами или представлением базы данных, которые изменяют данные.

Триггеры, которые автоматически выполняются в ответ на события DML (такие как операторы Insert, Update и Delete), называются триггерами DML.

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

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

Давайте разберемся с синтаксисом подробнее.

ON TableName или ViewName: Это относится к имени таблицы или представления, для которого мы определяем триггер.

For / After / AgainOf: Параметр For / After указывает, что триггер срабатывает только после выполнения операторов SQL, тогда как параметр AgainOf указывает, что триггер выполняется от имени запускающего оператора SQL.

Примечание: Вы не можете создать After Trigger для представлений.

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

Примечание: Операторы Insert, Update и Delete также известны как запускающие операторы SQL, поскольку эти операторы отвечают за срабатывание триггера.

Примеры для понимания триггера DML:

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

Используйте приведенный ниже сценарий SQL для создания и заполнения таблицы сотрудников.

 - Создать таблицу сотрудников
СОЗДАТЬ ТАБЛИЦУ сотрудника
(
  Id int Первичный ключ,
  Назовите nvarchar (30),
  Зарплата int,
  Пол nvarchar (10),
  DepartmentId int
)
ИДТИ

- Вставить данные в таблицу сотрудников
ВСТАВИТЬ В ЗНАЧЕНИЯ сотрудника (1, «Праная», 5000, «Мужской», 3)
ВСТАВИТЬ В ЗНАЧЕНИЯ сотрудников (2, «Приянка», 5400, «Женщина», 2)
ВСТАВИТЬ В ЗНАЧЕНИЯ сотрудника (3, 'Anurag', 6500, 'male', 1)
ВСТАВИТЬ В ЗНАЧЕНИЯ сотрудника (4, 'sambit', 4700, 'Male', 2)
ВСТАВИТЬ В ЗНАЧЕНИЯ сотрудника (5, "Hina", 6600, "Female", 3)
 

Пример 1: для триггера Insert DML

Создайте триггер, который ограничит операцию INSERT для таблицы Employee.

 CREATE TRIGGER trInsertEmployee
ON Сотрудник
ДЛЯ ВСТАВКИ
КАК
НАЧАТЬ
  ПЕЧАТЬ "ВЫ НЕ МОЖЕТЕ ВЫПОЛНИТЬ ОПЕРАЦИЮ"
  ОТКАТНАЯ СДЕЛКА
КОНЕЦ
 

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

ВСТАВИТЬ В ЗНАЧЕНИЯ сотрудника (6, «Saroj», 7600, «Male», 1)

Когда вы пытаетесь выполнить указанную выше инструкцию Insert, выдает следующую ошибку

Пример 2: для обновления триггера DML

Создайте триггер, который ограничит операцию UPDATE для таблицы Employee.

 CREATE TRIGGER trUpdateEmployee
ON Сотрудник
ДЛЯ ОБНОВЛЕНИЯ
КАК
Начать
  ПЕЧАТИ: «ВЫ НЕ МОЖЕТЕ ВЫПОЛНИТЬ ОПЕРАЦИЮ ОБНОВЛЕНИЯ»
  ОТКАТНАЯ СДЕЛКА
КОНЕЦ
 

Попробуем обновить одну запись в таблице Employee

ОБНОВЛЕНИЕ НАСТРОЙКА сотрудника Зарплата =

ГДЕ Id = 1

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

Пример 3: для удаления триггеров DML

Создайте триггер, который ограничит операцию DELETE для таблицы Employee.

 CREATE TRIGGER trDeleteEmployee
ON Сотрудник
ДЛЯ УДАЛЕНИЯ
КАК
НАЧАТЬ
  ПЕЧАТЬ "ВЫ НЕ МОЖЕТЕ ВЫПОЛНИТЬ УДАЛЕНИЕ ОПЕРАЦИИ"
  ОТКАТНАЯ СДЕЛКА
КОНЕЦ
 

Попробуем удалить одну запись из таблицы Employee

УДАЛИТЬ ОТ сотрудника, ГДЕ Id = 1

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

Где триггеры создаются в SQL Server?

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

Пример 4: для триггера вставки / обновления / удаления DML

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

Имя триггера DROP

Пример :

 DROP TRIGGER trDeleteEmployee
DROP TRIGGER trInsertEmployee
DROP TRIGGER trUpdate Сотрудник 

Теперь давайте создадим триггер, который ограничит все операции DML в таблице Employee.

 CREATE TRIGGER trAllDMLOperationsOnEmployee
ON Сотрудник
ДЛЯ ВСТАВКИ, ОБНОВЛЕНИЯ, УДАЛЕНИЯ
КАК
НАЧАТЬ
  ПЕЧАТЬ "ВЫ НЕ МОЖЕТЕ ВЫПОЛНИТЬ ОПЕРАЦИЮ DML"
  ОТКАТНАЯ СДЕЛКА
КОНЕЦ
 

Теперь вы не можете выполнять какие-либо операции DML с таблицей Employee, потому что эти операции ограничены триггером trAllDMLOperationsOnEmployee.

Пример 5:

Создайте триггер, который ограничит все операции DML в таблице Employee только в ПОНЕДЕЛЬНИК.

1. ДЕНЬ СОЛНЦА = 1
2. ПОНЕДЕЛЬНИК = 2
3. ДЕНЬ ВТО = 3
4. СРЕДА ДЕНЬ = 4
5. ЧЕТВЕРГ = 5
6. ПЯТНИЦА = 6
7. СБ ДЕНЬ = 7

 ALTER TRIGGER trAllDMLOperationsOnEmployee
ON Сотрудник
ДЛЯ ВСТАВКИ, ОБНОВЛЕНИЯ, УДАЛЕНИЯ
КАК
НАЧАТЬ
  ЕСЛИ DATEPART (DW; GETDATE ()) = 2
  НАЧАТЬ
    ПЕЧАТЬ "ОПЕРАЦИИ DML ЗАПРЕЩЕНЫ В ПОНЕДЕЛЬНИК"
    ОТКАТНАЯ СДЕЛКА
  КОНЕЦ
КОНЕЦ
 

Пример6:

Создайте триггер, который ограничит все операции DML в таблице Employee до 13:00.

 ALTER TRIGGER trAllDMLOperationsOnEmployee
ON Сотрудник
ДЛЯ ВСТАВКИ, ОБНОВЛЕНИЯ, УДАЛЕНИЯ
КАК
НАЧАТЬ
  ЕСЛИ DATEPART (HH, GETDATE ()) <13
  НАЧАТЬ
    ПЕЧАТЬ 'НЕДЕЙСТВИТЕЛЬНОЕ ВРЕМЯ'
    ОТКАТНАЯ СДЕЛКА
  КОНЕЦ
КОНЕЦ 

Зачем нам нужны триггеры DML в SQL Server?

Триггеры DML используются для обеспечения соблюдения бизнес-правил и целостности данных. Эти триггеры очень похожи на ограничения по способу обеспечения целостности.

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

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

.

Триггеры в SQL Server

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

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

Есть два типа триггеров:

1. Триггер DDL
2. Триггер DML

Триггеры DDL

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

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

1. создать триггер безопасности
2. в базе данных
3. для
4. create_table, alter_table, drop_table
5. as
6. print'вы не можете создавать, удалять и изменять таблицу в этой базе данных '
7. откат;

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

Триггеры DML

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

1. создать триггер глубокий
2. на emp
3. для
4. вставить, обновить, удалить
5. как
6. print'вы не можете вставлять, обновлять и удалять эту таблицу i '
7. откат;

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

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

AFTER Triggers

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

1. создать вставку триггера
2. на emp
3. после вставки
4. как
5. начало
6. вставить в значения empstatus («активные»)
7. конец

INSTEAD Of Triggers

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

1. CREATE TRIGGER instoftr
2. ON v11
3. INSTEAD OF INSERT
4. AS
5. BEGIN
6. INSERT INTO emp
7. SELECT I.id, I.names
8. FROM INSERTED I
10. INSERT14 900 SELECT I.id1, ​​I.name1
11. FROM INSERTED I
12. END

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

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

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

1. select * from emp
2. select * from emp1values ​​

Summary

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

.

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

Вернуться к: Руководство по SQL Server для начинающих и профессионалов

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

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

1. Что вместо триггеров в SQL Server?
2. Пример для понимания Вместо триггеров в SQL Server
3. Триггер INSTEAD OF INSERT в SQL Server
4. вместо триггера обновления в SQL Server
5. вместо удаления триггера в SQL Server

Что такое триггеры в SQL Server?

Триггеры INSTEAD OF - это триггеры, которые запускаются вместо инициирующего события, такого как события INSERT, UPDATE или DELETE.Итак, когда вы запускаете любые операторы DML, такие как Insert, Update и Delete, то от имени оператора DML вместо триггера будет выполняться.

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

Пример для понимания Вместо триггеров в SQL Server:

Мы собираемся использовать следующую таблицу «Отдел и сотрудник», чтобы понять сложные представления в SQL Server.

Используйте приведенный ниже сценарий SQL, чтобы создать и заполнить таблицу «Отдел» и «Сотрудник» некоторыми тестовыми данными.

 - Создать таблицу отделов
СОЗДАТЬ ТАБЛИЦУ
(
  ID INT ПЕРВИЧНЫЙ КЛЮЧ,
  Имя VARCHAR (50)
)
ИДТИ

- Заполните таблицу отделов тестовыми данными
ВСТАВИТЬ ЗНАЧЕНИЯ отдела (1, 'IT')
ВСТАВИТЬ В ЗНАЧЕНИЯ отдела (2, 'HR')
ВСТАВИТЬ ЗНАЧЕНИЯ отдела (3, «Продажи»)

- Создать таблицу сотрудников
СОЗДАТЬ ТАБЛИЦУ сотрудника
(
  ID INT ПЕРВИЧНЫЙ КЛЮЧ,
  Имя VARCHAR (50),
  Пол VARCHAR (50),
  Дата рождения, время,
  Заработная плата DECIMAL (18,2),
  DeptID INT
)
ИДТИ

- Заполните таблицу сотрудников тестовыми данными
ВСТАВИТЬ В ЦЕННОСТИ сотрудников (1, 'Pranaya', 'Male', '1996-02-29 10:53:27.060 ', 25000, 1)
ВСТАВИТЬ В ЦЕННОСТИ сотрудников (2, 'Priyanka', 'Female', '1995-05-25 10: 53: 27.060', 30000, 2)
ВСТАВИТЬ В ЗНАЧЕНИЯ сотрудника (3, 'Anurag', 'Male', '1995-04-19 10: 53: 27.060', 40000, 2)
ВСТАВИТЬ В ЗНАЧЕНИЯ сотрудников (4, 'Preety', 'Female', '1996-03-17 10: 53: 27.060', 35000, 3)
ВСТАВИТЬ В ЗНАЧЕНИЯ сотрудника (5, 'Sambit', 'Male', '1997-01-15 10: 53: 27.060', 27000, 1)
ВСТАВИТЬ В ЗНАЧЕНИЯ сотрудников (6, 'Hina', 'Female', '1995-07-12 10: 53: 27.060', 33000, 2)
GO 

Мы хотим получить следующие данные из таблицы «Сотрудник и отдел».

Итак, давайте создадим представление, которое вернет указанные выше результаты.

 СОЗДАТЬ ПРОСМОТР vwEmployeeDetails
КАК
ВЫБЕРИТЕ emp.ID, emp.Name, Gender, Salary, dept.Name AS Department
ОТ Сотрудника emp
Департамент INNER JOIN
ВКЛ emp.DeptID = dept.ID
 

Теперь давайте попробуем вставить запись в представление vwEmployeeDetails, выполнив следующий запрос.

ВСТАВИТЬ В vwEmployeeDetails ЗНАЧЕНИЯ (7, «Saroj», «Male», 50000, «IT»)

Когда мы выполняем вышеуказанный запрос, он дает нам ошибку, так как «Просмотр или функция vwEmployeeDetails не обновляются, поскольку изменение затрагивает несколько базовых таблиц. ‘

Как побороть указанную проблему?

Используя вместо триггера вставки.

триггер INSTEAD OF INSERT в SQL Server:

Здесь вы можете видеть, что вставка записи в представление, основанное на нескольких таблицах, дает нам ошибку. Теперь давайте разберемся, как ВМЕСТО ТРИГГЕРОВ может помочь нам в подобных ситуациях. Поскольку мы получаем сообщение об ошибке, когда пытаемся вставить запись в представление, давайте создадим триггер INSTEAD OF INSERT для представления vwEmployeeDetails с по , чтобы правильно вставить записи в соответствующую таблицу .

 СОЗДАТЬ ТРИГГЕР tr_vwEmployeeDetails_InsteadOfInsert
НА vwEmployeeDetails
ВМЕСТО ВСТАВКИ
КАК
НАЧАТЬ
  ОБЪЯВИТЬ @DepartmenttId int
 
  - Сначала проверьте, есть ли действительный идентификатор отдела в таблице отделов
  - для данного названия отдела
  ВЫБЕРИТЕ @DepartmenttId = ID отдела
  ОТ ОТДЕЛЕНИЯ
  INNER JOIN INSERTED inst
  на inst.Department = dept.Name
 
  - Если DepartmentId равен нулю, выдает ошибку
  ЕСЛИ (@DepartmenttId имеет значение null)
  НАЧАТЬ
    RAISERROR ('Недействительное название отдела.Заявление прекращено ', 16, 1)
    ВОЗВРАЩЕНИЕ
  КОНЕЦ
 
  - Наконец вставьте данные в таблицу сотрудников
  ВСТАВИТЬ В сотрудника (идентификатор, имя, пол, зарплата, DeptID)
  ВЫБЕРИТЕ ID, имя, пол, зарплату, @DepartmenttId
    ОТ ВСТАВЛЕННОГО
Конец 

Теперь давайте выполним приведенный ниже оператор Insert.

ВСТАВИТЬ В vwEmployeeDetails ЗНАЧЕНИЯ (7, «Saroj», «Male», 50000, «IT»)

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

SELECT * FROM vwEmployeeDetails даст нам следующий результат.

Как видно из изображения выше, запись вставляется в таблицу «Сотрудник», как и ожидалось.

вместо триггера обновления в SQL Server:

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

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

Мы хотим получить следующие данные из таблицы «Сотрудник и отдел».

Итак, давайте создадим представление, которое вернет указанные выше результаты.

 СОЗДАТЬ ПРОСМОТР vwEmployeeDetails
КАК
ВЫБЕРИТЕ emp.ID, emp.Name, Gender, Salary, dept.Name AS Department
ОТ Сотрудника emp
Департамент INNER JOIN
ВКЛ emp.DeptID = dept.ID
 

Теперь давайте попробуем обновить представление vwEmployeeDetails таким образом, чтобы оно влияло на обе базовые таблицы, такие как таблица Employee и Department, и посмотрим, получим ли мы какие-либо ошибки.

Следующий оператор UPDATE изменяет столбцы Name и Salary из таблицы Employee и столбцы Department Name из таблицы Department.

 ОБНОВЛЕНИЕ vwEmployeeDetails
SET Name = 'Кумар',
  Заработная плата = 45000,
  Отдел = 'HR'
ГДЕ Id = 1
 

Когда мы выполняем вышеуказанный запрос на обновление, мы получаем ошибку, так как « View or function‘ vwEmployeeDetails ’не обновляется, потому что изменение затрагивает несколько базовых таблиц. ”

Примечание: Если представление основано на нескольких таблицах и если оператор обновления влияет на более чем одну таблицу, то обновление не удалось.

А теперь попробуем изменить только отдел Пранаи с ИТ на HR. Следующий запрос UPDATE влияет только на одну базовую таблицу, которая является таблицей отдела. Итак, запрос на обновление должен быть успешным. Но перед выполнением запроса обратите внимание, что сотрудник Самбит также находится в ИТ-отделе.

 ОБНОВЛЕНИЕ vwEmployeeDetails
УСТАНОВИТЬ Отдел = 'HR'
ГДЕ Id = 1
 

Выполнив вышеуказанный запрос, выберите данные из представления и обратите внимание, что Sambit's Department также изменился на HR .

ВЫБРАТЬ * ОТ vwEmployeeDetails

Мы намеревались просто изменить название отдела Пранаи, но это также изменило название отдела Самбита. Таким образом, ОБНОВЛЕНИЕ сработало не так, как ожидалось. Это связано с тем, что запрос UPDATE обновил название отдела с IT на HR в таблице отдела.

ВЫБРАТЬ * ИЗ отдела

Как вы можете видеть, в записи с Id = 1 название отдела изменилось с IT на HR. Итак, вывод таков: если представление основано на нескольких таблицах и если мы хотим обновить представление, ОБНОВЛЕНИЕ может не всегда работать должным образом.Для правильного обновления базовых базовых таблиц через представление можно использовать триггер вместо обновления в SQL Server.

Перед созданием триггера давайте изменим название отдела на ИТ для записи с Id = 1.

ОБНОВЛЕНИЕ НАБОР отделов Имя = «IT» ГДЕ ID = 1

Используйте приведенный ниже сценарий SQL для создания триггера вместо обновления в SQL Server

 СОЗДАТЬ ТРИГГЕР tr_vwEmployeeDetails_InsteadOfUpdate
НА vwEmployeeDetails
ВМЕСТО ОБНОВЛЕНИЯ
КАК
НАЧАТЬ
  - если EmployeeId обновлен
  ЕСЛИ (ОБНОВЛЕНИЕ (ID))
  НАЧАТЬ
    RAISERROR ('Id не может быть изменен', 16, 1)
    ВОЗВРАЩЕНИЕ
  КОНЕЦ
 
  - Если название отдела обновлено
  ЕСЛИ (ОБНОВЛЕНИЕ (Отдел))
  НАЧАТЬ
    ОБЪЯВИТЬ @DepartmentID INT

    ВЫБЕРИТЕ @DepartmentID = dept.Я БЫ
    ОТ ОТДЕЛЕНИЯ
    INNER JOIN INSERTED inst
    ON dept.Name = inst.Department
  
    ЕСЛИ (@DepartmentID равен NULL)
    НАЧАТЬ
      RAISERROR ('Недопустимое название отдела', 16, 1)
      ВОЗВРАЩЕНИЕ
    КОНЕЦ
  
    ОБНОВЛЕНИЕ Сотрудник установил DeptID = @DepartmentID
    ОТ ВСТАВЛЕННОГО
    INNER JOIN Сотрудник
    на Employee.ID = Insert.ID
  Конец
 
  - Если пол обновлен
  ЕСЛИ (ОБНОВЛЕНИЕ (пол))
  НАЧАТЬ
    ОБНОВЛЕНИЕ НАБОР сотрудника Пол = вставлено.Пол
    ОТ ВСТАВЛЕННОГО
    INNER JOIN Сотрудник
    НА Employee.ID = INSERTED.ID
  КОНЕЦ
 
  - Если зарплата обновлена
  ЕСЛИ (ОБНОВЛЕНИЕ (зарплата))
  НАЧАТЬ
    ОБНОВЛЕНИЕ Сотрудник SET Salary = вставлено.
    ОТ ВСТАВЛЕННОГО
    INNER JOIN Сотрудник
    НА Employee.ID = INSERTED.ID
  КОНЕЦ

  - Если имя обновлено
  ЕСЛИ (ОБНОВЛЕНИЕ (имя))
  НАЧАТЬ
    ОБНОВЛЕНИЕ НАБОР сотрудников Имя = вставлено.Имя
    ОТ ВСТАВЛЕННОГО
    INNER JOIN Сотрудник
    ON Сотрудник.ID = INSERTED.ID
  КОНЕЦ
КОНЕЦ 

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

А теперь давайте попробуем обновить отдел Пранаи до отдела кадров.

ОБНОВЛЕНИЕ vwEmployeeDetails SET Department = ‘HR’ WHERE Id = 1

Запрос UPDATE работает должным образом.Триггер INSTEAD OF UPDATE правильно обновляет DeptId Pranaya до 2 в таблице Employee.

Теперь попробуем обновить имя, пол, зарплату и название отдела. Запрос UPDATE работает, как ожидалось, без возникновения ошибки - « View или функция vwEmployeeDetails не обновляются, поскольку изменение затрагивает несколько базовых таблиц. ‘

 ОБНОВЛЕНИЕ vwEmployeeDetails
НАБОР Name = 'Preety',
  Пол = 'Женский',
  Заработная плата = 44000,
  Отдел = 'ИТ'
ГДЕ Id = 1
 

вместо удаления триггера в SQL Server:

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

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

Мы хотим получить следующие данные из таблицы «Сотрудник и отдел».

Итак, давайте создадим представление, которое вернет указанные выше результаты.

 СОЗДАТЬ ПРОСМОТР vwEmployeeDetails
КАК
ВЫБЕРИТЕ emp.ID, emp.Name, Gender, Salary, dept.Name AS Department
ОТ Сотрудника emp
Департамент INNER JOIN
ВКЛ emp.DeptID = dept.ID
 

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

УДАЛИТЬ ИЗ vwEmployeeDetails ГДЕ ID = 1

Когда мы выполняем вышеуказанный запрос, он дает нам ошибку, так как «Просмотр или функция vwEmployeeDetails не обновляются, поскольку изменение затрагивает несколько базовых таблиц. ‘

Здесь мы видим, что удаление записи из представления, основанного на нескольких таблицах, дает нам ошибку. Теперь давайте разберемся, как ВМЕСТО ТРИГГЕРОВ может помочь нам в такой ситуации. Поскольку при попытке удалить запись из представления мы получаем сообщение об ошибке, давайте создадим триггер INSTEAD OF DELETE для представления vwEmployeeDetails с по , чтобы правильно удалить данные .

 СОЗДАТЬ ТРИГГЕР tr_vwEmployeeDetails_InsteadOfDelete
НА vwEmployeeDetails
ВМЕСТО УДАЛЕНИЯ
КАК
НАЧАТЬ
  - Использование внутреннего соединения
  УДАЛИТЬ ОТ сотрудника
  ОТ Сотрудника emp
  INNER JOIN DELETED del
  ON emp.ID = del.ID

  - Использование подзапроса
  - УДАЛИТЬ ОТ сотрудника
  - ГДЕ ID IN (ВЫБРАТЬ ID ИЗ УДАЛЕННЫХ)
КОНЕЦ
 

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

УДАЛИТЬ ИЗ vwEmployeeDetails ГДЕ ID = 1

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

SELECT * FROM vwEmployeeDetails даст нам следующий результат.

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

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

.

No related posts.