Как вызвать триггер в sql

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

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); Query OK, 0 rows affected (0.03 sec) mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account -> FOR EACH ROW SET @sum = @sum + NEW.amount; Query OK, 0 rows affected (0.06 sec)

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

6.1. Синтаксис CREATE TRIGGER

CREATE [DEFINER = user | CURRENT_USER>] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

INSERT : всякий раз, когда новая строка вставлена в таблицу. Например, через команды INSERT , LOAD DATA или REPLACE .
UPDATE : всякий раз, когда строка изменяется. Например, через инструкцию UPDATE .
DELETE : всякий раз, когда строка удалена из таблицы. Например, через инструкции DELETE и REPLACE . Однако, инструкции DROP TABLE и TRUNCATE относительно таблицы НЕ активизируют триггер, потому что они не используют DELETE !

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

Не может быть двух триггеров для данной таблицы, которые имеют те же самые время действия и событие. Например, Вы не можете иметь два триггера BEFORE UPDATE для таблицы. Но Вы можете иметь BEFORE UPDATE и BEFORE INSERT или BEFORE UPDATE и AFTER UPDATE .

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

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

Обратите внимание : до MySQL 5.0.10 триггер не может содержать прямые ссылки к именам таблиц. С MySQL 5.0.10, Вы можете записывать имена, как показано в этом примере:

CREATE TABLE test1(a1 INT); CREATE TABLE test2(a2 INT); CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE test4(a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0); DELIMITER | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; | DELIMITER ; INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL); INSERT INTO test4 (a4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

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

mysql> INSERT INTO test1 VALUES  -> (1), (3), (1), (7), (1), (8), (4), (4); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0

В результате данные в четырех таблицах будут следующие:

mysql> SELECT * FROM test1; +------+ | a1 | +------+ | 1 | | 3 | | 1 | | 7 | | 1 | | 8 | | 4 | | 4 | +------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM test2; +------+ | a2 | +------+ | 1 | | 3 | | 1 | | 7 | | 1 | | 8 | | 4 | | 4 | +------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM test3; +----+ | a3 | +----+ | 2 | | 5 | | 6 | | 9 | | 10 | +----+ 5 rows in set (0.00 sec) mysql> SELECT * FROM test4; +----+------+ | a4 | b4 | +----+------+ | 1 | 3 | | 2 | 0 | | 3 | 1 | | 4 | 2 | | 5 | 0 | | 6 | 0 | | 7 | 1 | | 8 | 1 | | 9 | 0 | | 10 | 0 | +----+------+ 10 rows in set (0.00 sec)

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

Предложение DEFINER определяет логин MySQL, который нужно использовать при проверке привилегий доступа в вызове триггера. Это было добавлено в MySQL 5.0.17. Если дано значение user , это должно быть логином MySQL в формате ‘ user_name ‘@’ host_name ‘ (как в команде GRANT ). Требуются переменные user_name и host_name . CURRENT_USER также может быть дан как CURRENT_USER() . Заданное по умолчанию значение DEFINER : пользователь, который выполняет инструкцию CREATE TRIGGER . Это также, как DEFINER = CURRENT_USER .

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

Если Вы не имеете привилегии SUPER , единственное допустимое значение user : Ваш собственный логин, определенный буквально или используя CURRENT_USER . Вы не можете устанавливать DEFINER к некоторому другому логину.
Если Вы имеете привилегию SUPER , Вы можете определять любое синтаксически допустимое имя пользователя. Если такого логина фактически не существует, будет сгенерировано предупреждение. Хотя возможно создать триггер с несуществующим значением DEFINER , делать этого не следует, поскольку триггер не будет активизирован, пока DEFINER фактически не существует. Иначе, поведение относительно проверки привилегии неопределенно.

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

Начиная с MySQL 5.0.17, MySQL проверяет привилегии триггера подобно этому:

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

При срабатывании триггера привилегии проверены на соответствие DEFINER . Пользователь должен иметь эти привилегии:

SUPER .
SELECT для подчиненной таблицы, если ссылки к столбцам таблицы происходят через OLD. col_name или or NEW. col_name в определении триггера.
UPDATE для подчиненной таблицы, если столбцы таблицы являются адресатами SET NEW. col_name = value , назначенными в определении триггера.
Любые другие привилегии обычно требуются для инструкций, выполненных триггером.

До MySQL 5.0.17, MySQL проверяет привилегии подобно этому:

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

Обратите внимание, что введение предложения DEFINER меняет значение CURRENT_USER() в определении триггера: функция CURRENT_USER() работает со значением DEFINER в MySQL 5.0.17 (и выше) или с тем пользователем, чьи действия заставили выполниться триггер (до MySQL 5.0.17).

6.2. Синтаксис DROP TRIGGER

DROP TRIGGER [schema_name.]trigger_name

Это уничтожает триггер. Имя базы данных опционально. Если оно не задано, триггер удаляется из заданной по умолчанию базы данных, Вызов DROP TRIGGER был добавлен в MySQL 5.0.2. Использование требует привилегии SUPER .

Обратите внимание: До MySQL 5.0.10, имя таблицы требовалось вместо имени схемы ( table_name . trigger_name ). При обновлении с MySQL 5.0 до MySQL 5.0.10 или выше, Вы должны удалить все триггеры перед обновлением и вновь создать их впоследствии, иначе вызов DROP TRIGGER не работает после обновления.

Кроме того, триггеры, созданные в MySQL 5.0.16 или выше, не могут быть удалены в MySQL 5.0.15 или ниже. Если Вы желаете выполнить такой возврат, Вы также должны в этом случае удалить все триггеры и заново их пересоздать после смены версий.

6.3. Использование триггеров

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

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

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

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

Следующие инструкции создают таблицу и триггер для нее:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account -> FOR EACH ROW SET @sum = @sum + NEW.amount;

Команда CREATE TRIGGER создает триггер ins_sum , который связан с таблицей account . Это также включает предложения, которые определяют время активации, событие вызова, и что делать с активированным триггером дальше:

Ключевое слово BEFORE указывает время срабатывания. В этом случае триггер должен активизировать перед каждой строкой, вставленной в таблицу. Другое допустимое ключевое слово здесь: AFTER .
Ключевое слово INSERT указывает событие, которое активизирует триггер. В этом примере триггер срабатывает от инструкции INSERT . Вы можете также создавать триггеры для инструкций DELETE и UPDATE .
Инструкция FOR EACH ROW определяет, что триггер должен сработать один раз для каждой строки, на которую воздействует инструкция в примере. Собственно триггер представляет собой в данном случае простой SET , который накапливает значения, вставленные в столбец amount . Инструкция обращается к столбцу как NEW.amount , что означает «значение столбца amount , которое будет вставлено в новую строку».

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

mysql> SET @sum = 0; mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); mysql> SELECT @sum AS 'Total amount inserted'; +-----------------------+ | Total amount inserted | +-----------------------+ | 1852.48 | +-----------------------+

В этом случае значение @sum после выполнения команды INSERT равно 14.98 + 1937.50 — 100 или 1852.48 .

Для уничтожения триггера выполните DROP TRIGGER . Вы должны определить имя схемы, если триггер не в заданной по умолчанию схеме:

mysql> DROP TRIGGER test.ins_sum;

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

В дополнение к требованию, чтобы имя триггера было уникальным для схемы, имеются другие ограничения на типы триггеров, которые можно создавать. Вы не можете иметь два триггера для таблицы, которые имеют то же самое событие и время активации. Например, Вы не можете определять два триггера типа BEFORE INSERT или AFTER UPDATE для таблицы. Это редко должно быть значительным ограничением, поскольку запросто можно определить триггер, выполняющий много инструкций с помощью конструкции BEGIN . END после FOR EACH ROW .

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

Столбец, именованный OLD только для чтения. Вы можете обратиться к этому столбцу (если Вы имеете привилегию SELECT , но не изменяете его. Столбец, именованный NEW может упоминаться, если Вы имеете привилегию SELECT для него. В триггере BEFORE Вы можете также изменять значение с SET NEW. col_name = value , если Вы имеете привилегию UPDATE для этого. Это означает, что Вы можете использовать триггер, чтобы изменить значения, которые будут вставлены в новую строку, или использовать это, чтобы модифицировать строку.

В триггере BEFORE значение NEW для столбца AUTO_INCREMENT : 0, а не автоматически сгенерированный порядковый номер, который будет сгенерирован, когда новая запись фактически вставлена.

OLD и NEW представляют собой MySQL-расширения триггеров.

Используя конструкцию BEGIN . END , Вы можете определять триггер, который выполняет много инструкций. Внутри блока BEGIN Вы также можете использовать другой синтаксис, который позволяется внутри сохраненных подпрограмм, типа условных выражений и циклов. Однако, точно как для сохраненных подпрограмм, если Вы используете программу mysql , чтобы определить триггер, необходимо переопределить операторный разделитель mysql так, чтобы Вы могли использовать ; как операторный разделитель внутри описания триггера. Следующий пример иллюстрирует эти моменты. Это определяет триггер UPDATE , который проверяет новое значение, которое нужно использовать для модифицирования каждой строки, и изменяет значение, чтобы оставаться внутри диапазона от 0 до 100. Это должно быть триггером типа BEFORE , потому что значение должно быть проверено прежде, чем оно используется, чтобы модифицировать строку:

mysql> delimiter // mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account -> FOR EACH ROW -> BEGIN -> IF NEW.amount < 0 THEN SET NEW.amount = 0; ->ELSEIF NEW.amount > 100 THEN SET NEW.amount = 100; -> END IF; -> END;// mysql> delimiter ;

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

Триггер не может использовать инструкцию CALL , чтобы вызвать сохраненные процедуры, которые возвращают данные пользователю или применяют динамический SQL. Процедурам позволяется возвратить данные триггеру через параметры OUT или INOUT .
Триггер не может использовать инструкции, которые явно или неявно начинают или заканчивают транзакцию, типа START TRANSACTION , COMMIT или ROLLBACK .
До MySQL 5.0.10 триггер не может содержать прямые ссылки к именам таблиц.

MySQL обрабатывает ошибки в выполнении триггеров следующим образом:

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

6.4. MySQL 5 FAQ по триггерам

6.4.1: Имеется ли форум для обсуждения триггеров в MySQL?

6.4.2: MySQL 5.1 имеет триггеры операторного уровня или уровня строки?

В MySQL 5.1 все триггеры FOR EACH ROW , то есть триггер активизирован для каждой строки, которая вставлена, модифицируется или удалена. MySQL 5.1 не поддерживает использование триггеров FOR EACH STATEMENT .

6.4.3: Имеется ли любое значение по умолчанию для триггеров?

Неявно. MySQL имеет специфическое специальное поведение для некоторых столбцов TIMESTAMP , а также для столбцов, которые определены, используя AUTO_INCREMENT .

6.4.4: Как управлять триггерами в MySQL?

В MySQL 5.1 триггер может быть создан, используя инструкцию CREATE TRIGGER , а удален инструкцией DROP TRIGGER .

Информация относительно триггеров может быть получена, запрашивая таблицу INFORMATION_SCHEMA.TRIGGERS .

6.4.5: Имеется ли способ просмотреть все триггеры в конкретной базе данных?

Да. Вы можете получать распечатку всех триггеров, определенных в базе данных dbname , запросом к таблице INFORMATION_SCHEMA.TRIGGERS примерно так:

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='dbname';

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

6.4.6: Где хранятся триггеры?

Триггеры в настоящее время сохранены в .TRG -файлах, один такой файл на таблицу. Другими словами, триггер принадлежит таблице.

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

6.4.7: Может триггер вызывать сохраненную процедуру?

6.4.8: Может триггер обращаться к таблицам?

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

6.4.9: Может триггер вызывать внешнюю прикладную программу через UDF?

Нет, не в настоящее время.

6.4.10: Может триггер модифицировать таблицы на удаленном сервере?

Да. Таблица на удаленном сервере могла бы модифицироваться, используя тип памяти FEDERATED .

Использование триггеров в СУБД MySQL

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

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

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

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

Общий вид синтаксиса для создания триггера:

CREATE [DEFINER = < user | CURRENT_USER >] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body

Где
trigger_name — название триггера;
trigger_time — время срабатывания триггера. BEFORE — перед событием. AFTER — после события;
trigger_event — событие:

insert — событие возбуждается операторами insert, data load, replace;
update — событие возбуждается оператором update;
delete — событие возбуждается операторами delete, replace. Операторы DROPTABLE и TRUNCATE не активируют выполнение триггера;
tbl_name — название таблицы;
trigger_body — выражение, которое выполняется при активации триггера.

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

Пример: создадим две таблицы test и log, напишем триггер, который после добавления каждой записи в 1-ю таблицу будет вести лог этого события:

-- таблица, за которой мы будем следить CREATE TABLE `test` ( `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `content` TEXT NOT NULL ); -- лог 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 ); -- триггер DELIMITER | CREATE TRIGGER `update_test` AFTER INSERT ON `test` FOR EACH ROW BEGIN INSERT INTO log Set msg = 'insert', row_id = NEW.id; END;

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

На столбцы таблицы, к которой привязан триггер можно ссылаться с помощью псевдонимов OLD и NEW. OLD.col_name указывает на столбец с именем col_name до изменения или удаления данных. NEW.col_name относится к колонке новой строке после вставки или существующей — сразу после её обновления.

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

DROP TRIGGER `update_test`;

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

SHOW TRIGGERS;

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

триггеры в MySQL 5 могут создаваться только пользователем с привилегией SUPER;
при использовании запроса, затрагивающего N — записей, триггер будет запускаться N — раз;
после удаления таблицы, СУБД MySQL автоматически удаляет привязанные к ней триггеры.

Список использованный источников

Официальная документация MySQL (http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html)
Habrahabr.ru — Триггеры в MySQL (http://habrahabr.ru/blogs/mysql/37693/)
Википедия (http://ru.wikipedia.org/wiki/Триггер_(базы_данных)

При полном или частичном использовании любых материалов с сайта вы обязаны явным образом указывать ссылку на handyhost.ru в качестве источника.

МИР Visa MasterCard СБП QIWI Wallet Безналичный платеж

Все способы

Услуги
Хостинг сайтов
Домены
Конструктор сайтов
Linux VPS / Windows VPS
Выделенные серверы
SSL сертификаты

Клиентам
Контакты
О компании
Акции
Оборудование
Партнерская программа

Поддержка
Способы оплаты
Регламент
Документы
Справка

Как вызвать триггер в sql

CREATE TRIGGER — создать триггер

Синтаксис

CREATE [ CONSTRAINT ] TRIGGER имя < BEFORE | AFTER | INSTEAD OF >< событие [ OR . ] > ON имя_таблицы [ FROM ссылающаяся_таблица ] [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] [ FOR [ EACH ] < ROW | STATEMENT >] [ WHEN ( условие ) ] EXECUTE PROCEDURE имя_функции ( аргументы ) Здесь допускается событие: INSERT UPDATE [ OF имя_столбца [, . ] ] DELETE TRUNCATE

Описание

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

Триггер можно настроить так, чтобы он срабатывал до операции со строкой (до проверки ограничений и попытки выполнить INSERT , UPDATE или DELETE ) или после её завершения (после проверки ограничений и выполнения INSERT , UPDATE или DELETE ), либо вместо операции (при добавлении, изменении и удалении строк в представлении). Если триггер срабатывает до или вместо события, он может пропустить операцию с текущей строкой, либо изменить добавляемую строку (только для операций INSERT и UPDATE ). Если триггер срабатывает после события, он « видит » все изменения, включая результат действия других триггеров.

Триггер с пометкой FOR EACH ROW вызывается один раз для каждой строки, изменяемой в процессе операции. Например, операция DELETE , удаляющая 10 строк, приведёт к срабатыванию всех триггеров ON DELETE в целевом отношении 10 раз подряд, по одному разу для каждой удаляемой строки. Триггер с пометкой FOR EACH STATEMENT , напротив, вызывается только один раз для конкретной операции, вне зависимости от того, как много строк она изменила (в частности, при выполнении операции, изменяющей ноль строк, всё равно будут вызваны все триггеры FOR EACH STATEMENT ). Заметьте, что при выполнении INSERT с предложением ON CONFLICT DO UPDATE сработают оба триггера уровня операторов, для INSERT и для UPDATE .

Триггеры, срабатывающие в режиме INSTEAD OF , должны быть помечены FOR EACH ROW и могут быть определены только для представлений. Триггеры BEFORE и AFTER для представлений должны быть помечены FOR EACH STATEMENT .

Кроме того, триггеры можно определить и для команды TRUNCATE , но только типа FOR EACH STATEMENT .

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

Когда	Событие	На уровне строк	На уровне оператора
BEFORE	INSERT / UPDATE / DELETE	Таблицы и сторонние таблицы	Таблицы, представления и сторонние таблицы
BEFORE	TRUNCATE	—	Таблицы
AFTER	INSERT / UPDATE / DELETE	Таблицы и сторонние таблицы	Таблицы, представления и сторонние таблицы
AFTER	TRUNCATE	—	Таблицы
INSTEAD OF	INSERT / UPDATE / DELETE	Представления	—
INSTEAD OF	TRUNCATE	—	—

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

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

Когда указывается параметр CONSTRAINT , эта команда создаёт триггер ограничения. Он подобен обычным триггерам, но отличается тем, что время его срабатывания можно изменить командой SET CONSTRAINTS . Триггеры ограничений должны работать в режиме AFTER ROW . Они могут срабатывать либо в конце оператора, вызвавшего целевое событие, либо в конце содержащей его транзакции; в последнем случае они называются отложенными. Срабатывание ожидающего отложенного триггера можно вызвать немедленно, воспользовавшись командой SET CONSTRAINTS . Предполагается, что триггеры ограничений будут генерировать исключения при нарушении ограничений.

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

За дополнительными сведениями о триггерах обратитесь к Главе 37.

Параметры

Имя, назначаемое новому триггеру. Это имя должно отличаться от имени любого другого триггера в этой же таблице. Имя не может быть дополнено схемой — триггер наследует схему от своей таблицы. Для триггеров ограничений это имя также используется, когда требуется скорректировать поведение триггера с помощью команды SET CONSTRAINTS . BEFORE
AFTER
INSTEAD OF

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

Принимает одно из значений: INSERT , UPDATE , DELETE или TRUNCATE ; этот параметр определяет событие, при котором будет срабатывать триггер. Несколько событий можно указать, добавив между ними слово OR .

Для событий UPDATE можно указать список столбцов, используя такую запись:

UPDATE OF имя_столбца1 [, имя_столбца2 . ]

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

Для событий INSTEAD OF UPDATE список столбцов задать нельзя. имя_таблицы

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

Имя (возможно, дополненное схемой) другой таблицы, на которую ссылается ограничение. Оно используется для ограничений внешнего ключа и не рекомендуется для обычного применения. Это указание допускается только для триггеров ограничений. DEFERRABLE
NOT DEFERRABLE
INITIALLY IMMEDIATE
INITIALLY DEFERRED

Время срабатывания триггера по умолчанию. Подробнее возможные варианты описаны в документации CREATE TABLE . Это указание допускается только для триггеров ограничений. FOR EACH ROW
FOR EACH STATEMENT

Определяет, будет ли процедура триггера срабатывать один раз для каждой строки, либо для SQL-оператора. Если не указано ничего, подразумевается FOR EACH STATEMENT (для оператора). Для триггеров ограничений можно указать только FOR EACH ROW . условие

Логическое выражение, определяющее, будет ли выполняться функция триггера. Если для триггера задано указание WHEN , функция будет вызываться, только когда условие возвращает true . В триггерах FOR EACH ROW условие WHEN может ссылаться на значения столбца в старой и/или новой строке, в виде OLD. имя_столбца и NEW. имя_столбца , соответственно. Разумеется, триггеры INSERT не могут ссылаться на OLD , а триггеры DELETE не могут ссылаться на NEW .

Триггеры INSTEAD OF не поддерживают условия WHEN .

В настоящее время выражения WHEN не могут содержать подзапросы.

Учтите, что для триггеров ограничений вычисление условия WHEN не откладывается, а выполняется немедленно после операции, изменяющей строки. Если результат условия — ложь, сам триггер не откладывается для последующего выполнения. имя_функции

Заданная пользователем функция, объявленная как функция без аргументов и возвращающая тип trigger , которая будет вызываться при срабатывании триггера. аргументы

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

Замечания

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

Для удаления триггера применяется команда DROP TRIGGER .

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

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

В триггере BEFORE условие WHEN вычисляется непосредственно перед возможным вызовом функции, поэтому проверка WHEN существенно не отличается от проверки того же условия в начале функции триггера. В частности, учтите, что строка NEW , которую видит ограничение, содержит текущие значения, возможно изменённые предыдущими триггерами. Кроме того, в триггере BEFORE условие WHEN не может проверять системные столбцы в строке NEW (например, oid ), так как они ещё не установлены.

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

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

В PostgreSQL до версии 7.3 обязательно требовалось объявлять триггерные функции, как возвращающие фиктивный тип opaque , а не trigger . Для поддержки загрузки старых файлов экспорта БД, команда CREATE TRIGGER принимает функции с объявленным типом результата opaque , но при этом выдаётся предупреждение и тип результата меняется на trigger .

Примеры

Выполнение функции check_account_update перед любым изменением строк в таблице accounts :

CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW EXECUTE PROCEDURE check_account_update();

То же самое, но функция триггера будет выполняться, только если столбец balance присутствует в списке целевых столбцов команды UPDATE :

CREATE TRIGGER check_update BEFORE UPDATE OF balance ON accounts FOR EACH ROW EXECUTE PROCEDURE check_account_update();

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

CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) EXECUTE PROCEDURE check_account_update();

Вызов функции, ведущей журнал изменений в accounts , но только если что-то изменилось:

CREATE TRIGGER log_update AFTER UPDATE ON accounts FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE log_account_update();

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

CREATE TRIGGER view_insert INSTEAD OF INSERT ON my_view FOR EACH ROW EXECUTE PROCEDURE view_insert_row();

В Разделе 37.4 приведён полный пример функции триггера, написанной на C.

Совместимость

Оператор CREATE TRIGGER в PostgreSQL реализует подмножество возможностей, описанных в стандарте SQL . В настоящее время в нём отсутствует следующая функциональность:

SQL позволяет определить синонимы для строк « old » и « new » или таблиц, которые затем можно будет использовать в определении действия триггера (например, CREATE TRIGGER . ON имя_таблицы REFERENCING OLD ROW AS некоторое_имя NEW ROW AS другое_имя. ). PostgreSQL позволяет писать процедуры триггеров на различных языках, так что механизм доступа к данным зависит от конкретного языка.

PostgreSQL не позволяет обращаться к старой и новой таблице в триггерах на уровне оператора, т. е. к таблицам, содержащим все старые и/или новые строки, обозначаемые как OLD TABLE и NEW TABLE в стандарте SQL .

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

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

Возможность задать несколько действий для одного триггера с помощью ключевого слова OR — реализованное в PostgreSQL расширение стандарта SQL.

Возможность вызывать триггеры для TRUNCATE — реализованное в PostgreSQL расширение стандарта SQL, как и возможность определять триггеры на уровне оператора для представлений.

CREATE CONSTRAINT TRIGGER — реализованное в PostgreSQL расширение стандарта SQL .

См. также

Пред.	Наверх	След.
CREATE TRANSFORM	Начало	CREATE TYPE

Триггеры SQL — Введение

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

Волшебные таблицы

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

Таблица содержит все вставленные строки

Таблица не содержит строк

Таблица содержит все удаленные строки

Таблица содержит строки после обновления

Таблица содержит все строки до обновления

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

Мы можем выполнить хранимую процедуру всякий раз, когда захотим, с помощью команды exec, но триггер можно выполнить только всякий раз, когда событие (вставка, удаление и обновление) запускается в таблице, для которой определен триггер.
Мы можем вызвать хранимую процедуру из другой хранимой процедуры, но мы не можем напрямую вызвать другой триггер внутри триггера. Мы можем достичь только вложенности триггеров, при которой действие (вставка, удаление и обновление), определенное внутри триггера, может инициировать выполнение другого триггера, определенного в той же или другой таблице.
Хранимые процедуры могут быть запланированы через задание для выполнения в заранее определенное время, но мы не можем запланировать триггер.
Хранимая процедура может принимать входные параметры, но мы не можем передать параметры в качестве входных данных для триггера.
Хранимые процедуры могут возвращать значения, но триггер не может возвращать значение.
Мы можем использовать команды Print внутри хранимой процедуры для отладки, но мы не можем использовать команду print внутри триггера.
Мы можем использовать операторы транзакции, такие как начало транзакции, фиксация транзакции и откат внутри хранимой процедуры, но мы не можем использовать операторы транзакции внутри триггера.
Мы можем вызвать хранимую процедуру из внешнего интерфейса (.asp-файлы, .aspx-файлы, .ascx-файлы и т. Д.), Но мы не можем вызвать триггер из этих файлов.

Триггеры DML

Типы триггера

В SQL Server есть два типа триггеров, которые приведены ниже:

Триггеры AFTER
Триггеры INSTEAD OF

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

Create table customer (customerid int identity (1, 1) primary key,Custnumber nvarchar(100), custFname nvarchar(100), CustEnamn nvarchar(100), email nvarchar(100), Amount int, regdate datetime)
Create table customerTransaction(Transactionid int identity(1,1)primary key,custid int,Transactionamt int, mode nvarchar, trandate datetime)
Create table Custmail (Custmailid int identity (1, 1) primary key, custid int, Amt int, Mailreason nvarchar(1000))

Триггеры AFTER

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

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

Create Trigger trigger_name
On Table name
For Insert/Delete/update
As
Begin
//SQL Statements
End

Пример триггера AFTER для вставки

Предположим, у нас есть требование, что всякий раз, когда добавляется новый клиент, автоматически его соответствующее значение должно быть вставлено в таблицу Custmail, чтобы можно было отправить электронное письмо клиенту и уполномоченному лицу в Банке. Чтобы решить эту проблему, мы можем создать триггер After Insert для таблицы customer, синтаксис которой приведен ниже:

Create Trigger trig_custadd on Customer
For Insert
As
Begin
Declare @Custnumber as nvarchar( 100 )
Declare @amount as int
Declare @custid as int
Select @Custnumber=Custnumber, @amount=Amount From inserted
Select @custid=customerid From customer Where Custnumber =@Custnumber
Insert Into Custmail (custid,Amt,Mailreason)
Values (@custid,@amount, ‘New Customer’ )
End

Этот триггер сработает всякий раз, когда новый клиент добавляется в банк и соответствующая запись вставляется в таблицу Custmail. Функциональность почты будет использовать записи из таблицы custmail для отправки почты клиенту.

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

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

Create trigger trig_custdelete on customer
For Delete
As begin
Declare @Custnumber as nvarchar( 100 )
Declare @custid as int
Select @Custnumber=Custnumber from deleted
Select @custid=customerid from customer where Custnumber =@Custnumber
Delete from customerTransaction where custid=@custid
Insert into Custmail
Values(@custid, 0 , ‘Customer delete’ )
end

Пример триггера AFTER для обновления

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

create trigger trig_Custupdate
on customer
for update
as
begin
declare @Custnumber as nvarchar( 100 )
declare @amount as int
Declare @custid as int
if update(amount)
begin
select @Custnumber=Custnumber, @amount=Amount from inserted
select @custid=customerid from customer where Custnumber =@Custnumber
insert into Custmail
values(@custid,@amount, ‘Customer Amount Update’ )
end
if update(custFname)or update(CustEnamn)
begin
insert into Custmail
values(@custid, 0 , ‘Customer Name Update’ )
end
end

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

Триггеры INSTEAD OF

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

могут быть определены в случае вставки, удаления и обновления. Например, предположим, что у нас есть условие, что в одной транзакции пользователь не сможет дебетовать более 15000 долларов. Мы можем использовать триггер вместо, чтобы реализовать это ограничение. Если пользователь пытается снять со своего счета более 15000 долларов за один раз, появляется сообщение об ошибке « Cannot Withdraw more than 15000 at a time ». В этом примере мы используем волшебную таблицу Inserted.

Create trigger trigg_insteadofdelete
on customerTransaction
instead of insert
as
begin
declare @Custnumber as nvarchar( 100 )
declare @amount as int
Declare @custid as int
Declare @mode as nvarchar( 10 )
select @custid =custid , @amount=Transactionamt,@mode=mode from
inserted
if @mode = ‘c’
begin
update customer set amount=amount+ @amount where
customerid= @custid
insert into Custmail
values( @custid ,@amount, ‘Customer Amount Update’ )
end
if @mode = ‘d’
begin
if @amount < = 15000
begin
update customer set amount=amount- @amount where
customerid= @custid
insert into Custmail
values( @custid ,@amount, ‘Customer Amount Update’ )
end
else
begin
Raiserror (‘Cannot Withdraw more than 15000 at a time’,16,1)
rollback;
end
end
end

Триггеры DDL

У триггеров DDL такое же поведение, как и у триггеров DML, за исключением того, что они запускаются в ответ на событие типа DDL, такое как команда Alter, команда Drop и команды Create. Другими словами, он будет срабатывать в ответ на события, которые пытаются изменить схему базы данных. Поэтому эти триггеры не создаются для конкретной таблицы, но они применимы ко всем таблицам в базе данных. Также триггеры DDL могут быть запущены только после выполнения команд, которые их запускают. Они могут быть использованы для следующих целей:

1) Чтобы предотвратить любые изменения в схеме базы данных

2) Если мы хотим хранить записи всех событий, которые меняют схему базы данных.

Например, предположим, что мы хотим создать таблицу command_log, в которой будут храниться все пользовательские команды для создания таблиц (Create table) и команды, которые изменяют таблицы. Также мы не хотим, чтобы какая-либо таблица был удалена. Поэтому, если какая-либо команда удаления таблицы запущена, триггер DDL откатит команду с сообщением «Вы не можете удалить таблицу».

Скрипт для таблицы command_log будет приведен ниже:

CREATE TABLE Command_log(id INT identity( 1 , 1 ), Commandtext NVARCHAR( 1000 ), Commandpurpose nvarchar( 50 ))

DDL Trigger для создания таблицы

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

CREATE TRIGGER DDL_Createtable
ON database
FOR CREATE_Table
AS
Begin
PRINT ‘Table has been successfully created.’
insert into command_log ()
Select EVENTDATA(). value ( ‘(/EVENT_INSTANCE/TSQLCommand/ CommandText ) [1] ‘ , ‘nvarchar(1000)’ )

End

Этот триггер срабатывает всякий раз, когда запускается любая команда для создания таблицы, и вставляет команду в таблицу command_log, а также выводит сообщение «Таблица была успешно создана».

Примечание. Eventdata () — это функция, которая возвращает информацию о событиях сервера или базы данных. Возвращает значение типа XML.

DDL Trigger для изменения таблицы

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

Create Trigger DDL_Altertable
On Database
for Alter_table
as
begin
declare @coomand as nvarchar(max)
print ‘Table has been altered successfully’
insert into command_log(commandtext)
Select EVENTDATA(). value ( ‘(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]’ , ‘nvarchar(1000)’ )

end

Этот триггер срабатывает всякий раз, когда в базе данных запускается любая команда alter table, и выводит сообщение «Таблица успешно изменена».

DDL Trigger для удаления таблицы

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

Create TRIGGER DDL_DropTable
ON database
FOR Drop_table
AS
Begin
PRINT ‘Table cannot be dropped.’
INSERT into command_log(commandtext)
Select EVENTDATA(). value ( ‘(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]’ , ‘nvarchar(1000)’ )
Rollback;
end

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

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

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

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

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

Мы также можем остановить выполнение вложенных триггеров с помощью следующей команды SQL:

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

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

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

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

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

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

Опцию рекурсивного запуска можно установить с помощью следующей команды SQL:

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Как найти триггеры в базе данных

1. Нахождение всех триггеров, определенных для всей базы данных

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

select o1.name, o2.name from sys.objects o1 inner join sys.objects o2 on o1.parent_object_id=o2.object_id and o1.type_desc= ‘sql_trigger’

2. Нахождение всех триггеров, определенных в конкретной таблице

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

sp_helptrigger Tablename
example:-
sp_helptrigger ‘Customer’

3. Нахождение определения триггера

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

sp_helptext triggername
For example:-
sp_helptext ‘trig_custadd’

Результат:

Как отключить триггер

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

DISABLE TRIGGER ‘trig_custadd’ ON Customer;

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

DISABLE TRIGGER ‘DDL_Createtable’ ON DATABASE;

Отключение всех триггеров, которые были определены с одинаковой областью действия

DISABLE Trigger ALL ON ALL SERVER;

Как включить триггер

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

ENABLE Trigger ‘trig_custadd’ ON Customer;

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

ENABLE TRIGGER ‘DDL_Createtable’ ON DATABASE;

Включение всех триггеров, которые были определены с одинаковой областью действия

ENABLE Trigger ALL ON ALL SERVER;

Как сбросить триггер

Сбрасывание триггера DML:

DROP TRIGGER trig_custadd ;

Сбрасывание триггера DDL:

DROP TRIGGER DDL_Createtable ON DATABASE

Пример из реальной жизни

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

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

Проблемы в этой задаче включают в себя:

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

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

Возможные решения:

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

Два подхода для вставки строк:

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

Мы использовали второй подход по следующим 4 причинам:

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

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

3) Если нам нужно что-то изменить в требовании, оно должно быть изменено во всех этих файлах и хранимых процедурах.

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

Преимущества триггеров SQL

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

2) Иногда они помогают сохранить короткие и простые коды SQL, как показано на примере из реальной жизни.

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

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

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

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

Недостатки триггеров

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

2) Их трудно отлаживать, так как их трудно просматривать по сравнению с хранимыми процедурами, представлениями, функциями и т. д.

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

4) Если в триггерах написан сложный код, это замедлит работу приложений.

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

Резюме

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

Как вызвать триггер в sql

Как вызвать триггер в sql

6.1. Синтаксис CREATE TRIGGER

6.2. Синтаксис DROP TRIGGER

6.3. Использование триггеров

6.4. MySQL 5 FAQ по триггерам

Использование триггеров в СУБД MySQL

Список использованный источников

Как вызвать триггер в sql

Синтаксис

Описание

Параметры

Замечания

Примеры

Совместимость

См. также

Триггеры SQL — Введение

Волшебные таблицы

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

Триггеры DML

Типы триггера

Триггеры AFTER

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

Пример триггера AFTER для вставки

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

Пример триггера AFTER для обновления

Триггеры INSTEAD OF

Триггеры DDL

DDL Trigger для создания таблицы

DDL Trigger для изменения таблицы

DDL Trigger для удаления таблицы

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

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

Как найти триггеры в базе данных

1. Нахождение всех триггеров, определенных для всей базы данных

2. Нахождение всех триггеров, определенных в конкретной таблице

3. Нахождение определения триггера

Результат:

Как отключить триггер

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

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

Отключение всех триггеров, которые были определены с одинаковой областью действия

Как включить триггер

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

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

Включение всех триггеров, которые были определены с одинаковой областью действия

Как сбросить триггер

Сбрасывание триггера DML:

Сбрасывание триггера DDL:

Пример из реальной жизни

Проблемы в этой задаче включают в себя:

Возможные решения:

Два подхода для вставки строк:

Преимущества триггеров SQL

Недостатки триггеров

Резюме

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