Выполнение триггеров в определенном порядке. Триггеры: создание и применение Sql trigger примеры
База данных Oracle позволяет определять триггеры, срабатывающие при выполнении команды DDL - проще говоря, любых команд SQL, создающих или модифицирующих объекты базы данных (таблицы, индексы и т. д.). Несколько примеров команд DDL: CREATE TABLE , ALTER INDEX , DROP TRIGGER - каждая из них создает, изменяет или удаляет объект базы данных. Синтаксис создания триггеров команд DDL почти не отличается от синтаксиса триггеров PL/SQL для DML . Они различаются лишь перечнем инициирующих событий и тем, что триггеры DDL не связываются с конкретными таблицами.
Весьма специфический триггер INSTEAD OF CREATE TABLE , описанный в конце раздела, позволяет манипулировать со стандартным поведением события CREATE TABLE . Не все аспекты синтаксиса и использования триггеров, описанные далее, применимы к этому типу триггеров.
Создание триггера DDL
Команда создания (или замены) триггера DDL имеет следующий синтаксис :
1 CREATE TRIGGER имя_триггера 2 {BEFORE | AFTER } { событие_DDL} ON {DATABASE | SCHEMA} 3 4 DECLARE 5 Объявления переменных 6 BEGIN 7 ...код триггера... 8 END;
Элементы триггера описаны в следующей таблице.
| Строки | Описание |
| 1 | Создание триггера с заданным именем. Если триггер существует, а секция REPLACE отсутствует, попытка создания триггера приведет к ошибке ORA-4081 |
| 2 | Строка определяет, должен ли триггер запускаться до или после наступления заданного события DDL , а также должен ли он запускаться для всех операций в базе данных или только в текущей схеме. Секция INSTEAD OF поддерживается только в Oracle9i Release 1 и последующих версиях |
| 3 | Необязательное условие WHEN , позволяющее задать логику для предотвращения ненужного выполнения триггера |
| 4-7 | Образец тела триггера |
Приведем пример простого триггера, оповещающего о создании любых объектов:
SQL> CREATE OR REPLACE TRIGGER town_crier 2 AFTER CREATE ON SCHEMA 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE("I believe you have created something!"); 5 END; 6 / Trigger created. SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE a_table 2 (col1 NUMBER); Table created. SQL> CREATE INDEX an_index ON a_table(col1); Index created. SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS 2 BEGIN 3 RETURN(TRUE); 4 END; 5 / Function created. SQL> /* Очистка буфера DBMS_OUTPUT */ SQL> BEGIN NULL; END; 2 / I believe you have created something! I believe you have created something! I believe you have created something! PL/SQL procedure successfully completed.
Текст, возвращаемый встроенным пакетом DBMS_OUTPUT , не будет выводиться триггером DDL до успешного выполнения блока PL/SQL, даже если этот блок не выполняет никаких действий.
Однако сообщая о создании объекта, этот триггер не уточняет, что же именно создается. Но на самом деле триггеры DDL могут предоставлять и более полную информацию, ведь триггер можно переписать и таким образом:
SQL> CREATE OR REPLACE TRIGGER town_crier 2 AFTER CREATE ON SCHEMA 3 BEGIN 4 -- Использование атрибутов события для получения более полной информации 5 DBMS_OUTPUT.PUT_LINE("I believe you have created a " || 6 ORA_DICT_OBJ_TYPE || " called " || 7 ORA_DICT_OBJ_NAME); 8 END; 9 / Trigger created. SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE a_table 2 col1 NUMBER); Table created. SQL> CREATE INDEX an_index ON a_table(col1); Index created. SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS 2 BEGIN 3 RETURN(TRUE); 4 END; 5 / Function created. SQL> /*-- Очистка буфера DBMS_OUTPUT */ SQL> BEGIN NULL; END; 2 / I believe you have created a TABLE called A_TABLE I believe you have created a INDEX called AN_INDEX I believe you have created a FUNCTION called A_FUNCTION PL/SQL procedure successfully completed.
В этих примерах представлены два важнейших аспекта триггеров DDL: события, с которыми они связываются, и атрибуты событий, которые в них доступны.
События триггеров
Список событий, которые можно связать с триггерами DDL, приведен в табл. 1. Любой триггер DDL может вызываться как до (BEFORE), так и после (after) наступления указанного события.
Таблица 1. События DLL
| Событие DDL | описание |
| ALTER | Создание объекта базы данных командой SQL ALTER |
| ANALYZE | Анализ состояния объекта базы данных командой SQL |
| ASSOCIATE STATISTICS | Связывание статистики с объектом базы данных |
| AUDIT | Включение аудита базы данных командой SQL AUDIT |
| COMMENT | Создание комментария для объекта базы данных |
| CREATE | Создание объекта базы данных командой SQL CREATE |
| DDL | Любое из перечисленных событий |
| DISASSOCIATE STATISTICS | Удаление статистики,связанной с объектом баз данных |
| DROP | Удаление объекта базы данных командой SQL DROP |
| GRANT | Назначение прав командой SQL GRANT |
| NOAUDIT | Отключение аудита базы данных командой SQL NOAUDIT |
| RENAME | Переименование объекта базы данных командой SQL RENAME |
| REVOKE | Отмена прав командой SQL REVOKE |
| TRUNCATE | Очистка таблицы командой SQL TRUNCATE |
Как и триггеры DML, триггеры DDL запускаются, когда в заданной базе данных или схеме происходят связанные с ними события. Количество типов триггеров, которые могут быть определены в базе данных или схеме, не ограничено.
Атрибутные функции
Oracle предоставляет набор функций (определенных в пакете DBMS_STANDARD) для получения информации о причине запуска триггера DDL и других связанных с ним параметрах (например, имя удаляемой таблицы). Перечень этих атрибутных функций приведен в табл. 2, а примеры их использования - в следующих разделах.
Таблица 2. События триггеров DDL и атрибутные функции
| Функция | Что возвращает |
| ORA_CLIENT_IP_ADDRESS | IP-адрес клиента |
| ORA_DATABASE_NAME | Имя базы данных |
| ORA_DES_ENCRYPTED_PASSWORD | Пароль текущего пользователя, зашифрованный с использованием алгоритма DES |
| ORA_DICT_OBJ_NAME | Имя объекта базы данных, связанного с командой DDL, которая вызвала запуск триггера |
| ORA_DICT_OBJ_NAME_LIST | Количество обработанных командой объектов. В параметре NAME_LIST возвращается полный список этих объектов в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T |
| ORA_DICT_OBJ_OWNER | Имя владельца объекта базы данных, связанного с командой DDL, которая вызвала запуск триггера |
| ORA_DICT_OBJ_OWNER_LIST | Количество обработанных командой объектов. В параметре NAME_LIST возвращается полный список имен этих объектов в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T |
| ORA_DICT_OBJ_TYPE | Тип объекта базы данных, связанного с командой DDL, вызвавшей запуск триггера (например, TABLE или INDEX) |
| ORA_GRANTEE | Количество пользователей, получивших привилегии. В аргументе USER_LIST содержится полный список этих пользователей в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T |
| ORA_INSTANCE_NUM | Номер экземпляра базы данных |
| ORA_IS_ALTER_COLUMN | TRUE , если изменяется столбец, заданный параметром COLUMN_NAME ; FALSE в противном случае |
| ORA_IS_CREATING_NESTED_TABLE | TRUE , если создается вложенная таблица; FALSE в противном случае |
| ORA_IS_DROP_COLUMN | TRUE , если удаляется столбец, заданный параметром COLUMN _ NAME; FALSE в противном случае |
| ORA_LOGIN_USER | Имя пользователя Oracle , для которого запущен триггер |
| ORA_PARTITION_POS | Позиция команды SQL для корректной вставки секции PARTITION |
| ORA_PRIVILEGE_LIST | Количество предоставленных или отмененных привилегий. В аргументе PRIVILEGE_LIST содержится полный список привилегий в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T |
| ORA_REVOKEE | Количество пользователей, лишенных привилегий. В аргументе USER_LIST содержится полный список этих пользователей в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T |
| ORA_SQL_TXT | Количество строк в команде SQL, которая вызвала запуск триггера. Аргумент SQL_TXT возвращает каждую строку команды в виде аргумента типа DBMS_STANDARD.ORA_NAME_LIST_T |
| ORA_SYSEVENT | Тип события, вызвавшего запуск триггера DDL (например, CREATE , DROP или ALTER) |
| ORA_WITH_GRANT_OPTION | TRUE , если привилегии предоставлены конструкцией GRANT ; FALSE в противном случае |
Об атрибутных функциях необходимо дополнительно сказать следующее:
- Тип данных 0RA_NAME_LIST_T определен в пакете DBMS_STANDARD так:
Иными словами, это вложенная таблица строк, каждая из которых может содержать до 64 символов.
- События триггеров DDL и атрибутные функции также определены в пакете DBMS_ STANDARD . Для каждой из функций этого пакета Oracle создает независимую функцию, добавляя к ее имени префикс 0RA_, для чего при создании базы данных выполняется сценарий $ORACLE_HOME/rdbms/dbmstrig.sql. В некоторых версиях Oracle этот сценарий содержит ошибки, из-за которых независимые функции не видны или не выполняются. Если вы сомневаетесь в правильности определения этих элементов, попросите администратора базы данных проверить сценарий и внести необходимые исправления.
- Представление словаря данных USER_S0URCE не обновляется до срабатывания обоих триггеров DDL, BEFORE и AFTER . Иначе говоря, вы не сможете использовать эти функции для реализации системы контроля версий «до и после», построенной исключительно в границах базы данных и основанной на триггерах.
Применение событий и атрибутов
Возможности триггеров DDL лучше всего продемонстрировать на примерах. Для начала рассмотрим триггер, который блокирует создание любых объектов базы данных:
TRIGGER no_create AFTER CREATE ON SCHEMA BEGIN RAISE_APPLICATI0N_ERR0R (-20000, "ERROR: Objects cannot be created in the production database."); END;
После его создания в базе данных не удастся создать ни один объект:
SQL> CREATE TABLE demo (coll NUMBER); * ERROR at line 1: ORA-20000: Objects cannot be created in the production database.
Сообщение об ошибке получилось кратким и не слишком содержательным. Произошел сбой, но какой именно? Хорошо бы включить в сообщение дополнительную информацию, например указать тип объекта, который пытался создать пользователь:
TRIGGER no_create AFTER CREATE ON SCHEMA BEGIN RAISE_APPLICATION_ERROR (-20000, "Cannot create the " || ORA_DICT_OBJ_TYPE || " named" || ORA_DICT_OBJ_NAME || " as requested by" || ORA_DICT_OBJ_OWNER || " in production."); END;
С таким триггером попытка создать таблицу в базе данных приведет к выводу более подробного сообщения об ошибке:
SQL> CREATE TABLE demo (coll NUMBER); * ERROR at line 1: ORA-20000: Cannot create the TABLE named DEMO as requested by SCOTT in production
Можно было бы даже реализовать эту логику в виде триггера BEFORE и воспользоваться событием ora_sysevent:
TRIGGER no_create BEFORE DDL ON SCHEMA BEGIN IF ORA_SYSEVENT = "CREATE" THEN RAISE_APPLICATION_ERROR (-20000, "Cannot create the " || ORA_DICT_OBJ_TYPE || " named " ||ORA_DICT_OBJ_NAME || " as requested by " || ORA_DICT_OBJ_OWNER); ELSIF ORA_SYSEVENT = "DROP" THEN -- Логика операций DROP ... END IF; END;
Какой столбец был изменен?
Для получения информации о том, какой столбец был изменен командой ALTER TABLE , можно воспользоваться функцией ORA_IS_ALTER_COLUMN . Пример:
TRIGGER preserve_app_cols AFTER ALTER ON SCHEMA DECLARE -- Курсор для получения информации о столбцах таблицы CURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2) IS SELECT column_name FROM all_tab_columns WHERE owner = cp_owner AND table_name = cp_table; BEGIN -- Если была изменена таблица... IF ora_dict_obj_type = "TABLE" THEN -- Для каждого столбца в таблице... FOR v_column_rec IN curs_get_columns (ora_dict_obj_owner, ora_dict_obj_name) LOOP -- Является ли текущий столбец измененным? IF ORA_IS_ALTER_COLUMN (v_column_rec.column_name) THEN -- Отклонить изменения в "критическом" столбце IF mycheck.is_application_column (ora_dict_obj_owner, ora_dict_obj_name, v_column_rec.column_name) THEN CENTRAL_ERROR_HANDLER ("FAIL", "Cannot alter core application attributes"); END IF; -- критическая таблица/столбец END IF; -- текущий столбец был изменен END LOOP; -- для каждого столбца в таблице END IF; -- таблица была изменена END;
Попытки изменения критических атрибутов приложения становятся невозможными. Помните, что эта логика не будет работать, если триггер срабатывает при добавлении новых столбцов. При срабатывании триггера DDL информация о столбцах еще не видна в словаре данных.
Попытки удаления конкретных столбцов можно проверять следующим образом:
IF ORA_IS_DROP_COLUMN ("COL2") THEN что-то сделать ELSE сделать что-то другое! END IF;
Функции ORA_IS_DROP_COLUMN и ORA_IS_ALTER_COLUMN не обращают внимания на то, к какой таблице присоединен столбец; они работают исключительно по имени столбца.
Списки, возвращаемые атрибутными функциями
Некоторые атрибутные функции возвращают данные двух типов: список элементов и количество элементов. Например, функция ORA_GRANTEE возвращает список и количество пользователей, которым предоставлены определенные права, а функция 0RA_PRIVILEGE_LIST - список и количество предоставленных прав. Обычно обе эти функции применяются в триггерах AFTER GRANT . Пример использования этих функций представлен в файле privs.sql на сайте github. Фрагмент кода этого примера:
TRIGGER what_privs AFTER GRANT ON SCHEMA DECLARE v_grant_type VARCHAR2 (30); v_num_grantees BINARY_INTEGER; v_grantee_list ora_name_list_t; v_num_privs BINARY_INTEGER; v_priv_list ora_name_list_t; BEGIN -- Получение информации о типе с последующей выборкой списков. v_grant_type:= ORA_DICT_OBJ_TYPE; v_num_grantees:= ORA_GRANTEE (v_grantee_list); v_num_privs:= ORA_PRIVILEGE_LIST (v_priv_list); IF v_grant_type = "ROLE PRIVILEGE" THEN DBMS_OUTPUT.put_line ("The following roles/privileges were granted"); -- Вывод привилегии для каждого элемента списка. FOR counter IN 1 .. v_num_privs LOOP DBMS_OUTPUT.put_line ("Privilege " || v_priv_list (counter)); END LOOP;
Триггер прекрасно подходит для получения подробной информации о правах пользователей и объектах базы данных, указанных в командах GRANT . Эту информацию также можно сохранить в базе данных, добавив журнал с подробными сведениями об изменениях.
SQL> GRANT DBA TO book WITH ADMIN OPTION; Grant succeeded. SQL> EXEC DBMS_OUTPUT.PUT_LINE("Flush buffer"); The following roles/privileges were granted Privilege UNLIMITED TABLESPACE Privilege DBA Grant Recipient BOOK Flush buffer SQL> GRANT SELECT ON x TO system WITH GRANT OPTION; Grant succeeded. SQL> EXEC DBMS_OUTPUT.PUT_LINE("Flush buffer"); The following object privileges were granted Privilege SELECT On X with grant option Grant Recipient SYSTEM Flush buffer
Можно ли удалить неудаляемое?
Я показал, что триггеры DDL могут использоваться для предотвращения выполнения определенного типа операций DDL с конкретными объектами или типами объектов. А если я создам триггер, который предотвращает DDL -операции DROP , а затем попытаюсь удалить сам триггер? Не появится ли триггер, который по сути невозможно удалить? К счастью, в Oracle этот сценарий был предусмотрен:
SQL> CREATE OR REPLACE TRIGGER undroppable 2 BEFORE DROP ON SCHEMA 3 BEGIN 4 RAISE_APPLICATION_ERROR(-20000,"You cannot drop me! I am invincible!"); 5 END; SQL> DROP TABLE employee; * ERROR at line 1: ORA-20000: You cannot drop me! I am invincible! SQL> DROP TRIGGER undroppable; Trigger dropped.
При работе с подключаемыми базами данных (Oracle Database 12c и выше) можно вставить ключевое слово PLUGGABLE перед DATABASE в определении триггера. DATABASE (без PLUGGABLE) определяет триггер на корневом уровне. В мультиарендной (multitenant) контейнерной базе данных (CDB) только пользователь, подключившийся к корневому уровню, может создать триггер для всей базы данных. PLUGGABLE DATABASE определяет триггер для подключаемой базы данных, к которой вы подключены. Триггер срабатывает каждый раз, когда любой пользователь заданной базы данных или PDB инициирует активизирующее событие.
Триггер INSTEAD OF CREATE
Oracle предоставляет триггер INSTEAD OF CREATE для автоматической группировки данных таблиц. Для этого триггер должен перехватить выполняемую команду SQL , вставить в нее условие группировки, а затем выполнить при помощи функции ORA_SQL_TXT . Следующий пример показывает, как это делается.
TRIGGER io_create INSTEAD OF CREATE ON DATABASE WHEN (ORA_DICT_OBJ_TYPE = "TABLE") DECLARE v_sql VARCHAR2 (32767); -- Генерируемый код sql v_sql_t ora_name_list_t; -- таблица sql BEGIN -- Получение выполняемой команды SQL FOR counter IN 1 .. ora_sql_txt (v_sql_t) LOOP v_sql:= v_sql || v_sql_t (counter); END LOOP; -- Для определения условия PARTITION будет вызвана -- функция magic_partition. v_sql:= SUBSTR (v_sql, 1, ora_partition_pos) || magic_partition_function || SUBSTR (v_sql, ora_partition_pos + 1); /* Вставить перед именем таблицы имя пользователя. | Комбинации CRLF заменяются пробелами. | Операция требует наличия явной привилегии CREATE ANY TABLE, | если только вы не переключились на модель AUTHID CURRENT_USER. */ v_sql:= REPLACE (UPPER (REPLACE (v_sql, CHR (10), " ")) , "CREATE TABLE " , "CREATE TABLE " || ora_login_user || "."); -- Выполнение сгенерированной команды SQL EXECUTE IMMEDIATE v_sql; END;
Теперь группировка таблиц будет осуществляться автоматически в соответствии с логикой функции my_partition .
Oracle предоставляет несколько способов группировки (диапазоны, хеширование) и логических моделей группировки (например, по первичному ключу или по уникальному ключу). Вы должны решить, какие из них должны использоваться в вашей функции группировки.
Если не включить только что приведенную секцию WHEN , при попытке создания объектов, отличных от таблиц, происходит ошибка:
ORA-00604: error occurred at recursive SQL level 1 ORA-30511: invalid DDL operation in system triggers
Кроме того, при попытке создания триггера INSTEAD OF для любой другой операции DDL , кроме CREATE , компилятор выдает сообщение об ошибке (ORA-30513).
Триггеры INSTEAD OF для операций DML (вставка, обновление и удаление) будут рассматриваться мною далее в блоге. Эти триггеры используют некоторые элементы синтаксиса триггера INSTEAD OF CREATE для таблиц, но этим сходство между ними ограничивается.
Триггер (базы данных)
Три́ггер (англ. 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 ;
Wikimedia Foundation . 2010 .
- Домашнее хозяйство
- Спектроскопия
Смотреть что такое "Триггер (базы данных)" в других словарях:
Представление (базы данных) - У этого термина существуют и другие значения, см. Представление. Представление (англ. view, более созвучное не стандартное название «вид», в сленге программистов часто используется в качестве заимствования из английского «вьюха», «вьюшка»)… … Википедия
Иерархические базы данных - Иерархическая модель базы данных состоит из объектов с указателями от родительских объектов к потомкам, соединяя вместе связанную информацию. Иерархические базы данных могут быть представлены как дерево, состоящее из объектов различных уровней.… … Википедия
Реляционные базы данных - Реляционная база данных база данных, основанная на реляционной модели данных. Слово «реляционный» происходит от англ. relation (отношение). Для работы с реляционными БД применяют реляционные СУБД. Использование реляционных баз данных было… … Википедия
Индекс (базы данных) - У этого термина существуют и другие значения, см. Индекс. Индекс (англ. index) объект базы данных, создаваемый с целью повышения производительности поиска данных. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в … Википедия
Курсор (базы данных) - У этого термина существуют и другие значения, см. Курсор (значения). Курсор ссылка на контекстную область памяти[источник не указан 126 дней]. В некоторых реализациях информационно логического языка SQL (Oracle,… … Википедия
Триггер (значения) - Триггер (англ. trigger в значении существительного «собачка, защёлка, спусковой крючок в общем смысле, приводящий нечто в действие элемент»; в значении глагола «приводить в действие»): в русском языке первоначально термин из области… … Википедия
Рефакторинг баз данных - (англ. database refactoring) это простое изменение в схеме базы данных, которое способствует улучшению ее проекта при сохранении функциональной и информационной семантики. Иными словами, следствием рефакторинга базы данных не может быть… … Википедия
База данных - Запрос «БД» перенаправляется сюда; см. также другие значения. База данных представленная в объективной форме совокупность самостоятельных материалов (статей, расчётов, нормативных актов, судебных решений и иных подобных материалов),… … Википедия
Проектирование баз данных - процесс создания схемы базы данных и определения необходимых ограничений целостности. Содержание 1 Основные задачи проектирования баз данных … Википедия
Модель данных - В классической теории баз данных, модель данных есть формальная теория представления и обработки данных в системе управления базами данных (СУБД), которая включает, по меньшей мере, три аспекта: 1) аспект структуры: методы описания типов и… … Википедия
Очень может быть, что вы знаете, что такое триггер базы данных, хотя бы в общих терминах. Есть даже шанс, что вы знаете, что MySQL поддерживает триггеры и имеет практику работы с ними. Но скорее всего, что большинство из вас, даже вооруженные знаниями не представляют себе, какие преимущества скрывают триггеры MySQL. Этот инструмент должен быть у вас на вооружении, так как триггеры могут полностью изменить ваш способ работы с данными.
Введение: что такое триггер
“Не смотря на то, что приложения становятся все более и более сложными, мы можем абстрагировать уровень приложений для того, чтобы управлять ими и увеличивать удобство процесса разработки.”
Для тех, кто не знает, триггер - это правило, которое помещается вами в таблицу, и при выполнении DELETE, UPDATE или INSERT совершает дополнительные действия. Например, мы можем делать запись в журнале об изменении. Но вместо написания двух отдельных запросов (один - для изменения данных, другой для внесения записи в журнал), можно написать триггер, который будет содержать правило: “Когда бы ни изменялась строка, создать новую строку в другой таблице, чтобы сообщить, что были сделаны изменения”. Такой подход создает некоторую избыточность в основном запросе, но теперь нет проходов двух разных пакетов до сервера вашей базы данных, чтобы выполнить два разных действия, что в целом способствует улучшению производительности.
Триггеры были введены в MySQL начиная с версии 5.0.2. Синтаксис триггеров несколько чужероден. MySQL использует стандарт ANSI SQL:2003 для процедур и других функций. Если вы работаете с языками программирования, то понять его будет не сложно. Спецификация отсутствует в свободном доступе, поэтому мы постараемся использовать простые структуры и будем объяснять, что происходит в триггере. Будут использоваться такие же структуры, как и в любом языке программирования.
Как уже упоминалось выше, триггеры выполняются как процедуры при событиях UPDATE, DELETE и INSERT. Они могут быть выполнены либо до либо после определения события. Таким образом Вы можете определить триггер, которые будет выполняться перед DELETE или после DELETE, и так далее. Это значит, что можно иметь один триггер, который выполнится до INSERT и совершенно другой, который выполнится после INSERT, что является весьма мощным инструментом.
Начало: структура таблиц, инструменты и заметки
В статье мы будем работать с выдуманной системой для корзины покупок, каждый элемент которой будет иметь цену. Структура данных будет проста, насколько это возможно с целью продемонстрировать процедуры работы с триггерами. Наименования таблиц и столбцов придуманы с целью облегчения понимания, а не для реальной работы. Также используется TIMESTAMPS для облегчения учебного процесса. Таблицы имеют имена carts, cart_items, cart_log, items, items_cost.
Также будут использоваться очень простые запросы. Нет связи между переменными и не используется никакого ввода данных. Запросы подготавливались так, чтобы быть как можно более простыми и понятными для чтения.
Для определения времени выполнения использовался Particle Tree PHP Quick Profiler . Для иллюстрации эффектов на базе данных использовался Chive . Chive предназначен только для MySQL 5+ и очень похож на PHPMyAdmin. Он имеет более выразительный интерфейс, но содержит значительно больше ошибок на текущий момент. Использование Chive обусловлено желанием представить более выразительные скрин шоты запросов.
Вам также может понадобиться поменять разделитель MySQL при создании триггеров. Оригинальный разделитель MySQL - это; , но так как мы будем использовать разделитель для добавленных запросов, то может понадобиться явно указать разделитель, чтобы создавать запросы из командной линии. При использование Chive нет необходимости менять разделитель.
Чтобы изменить разделитель, нужно выполнить команду перед командой триггера:
DELIMITER $$
А после команды триггера надо ввести:
DELIMITER ;
Простой триггер : целостность данных
Если Вы захотите выполнить даже незначительную нормализацию структуры базы данных, может получиться так, что нужно будет удалять источник основных данных, который имеет фрагменты, участвующие в общем потоке данных. Например, у вас может быть cart_id, который ссылается на две или три таблицы без внешних ключей, особенно при использовании механизма MyISAM, который их не поддерживает.
Для такого случая раньше вы возможно выполняли следующие операции:
$sql = "DELETE FROM no_trigger_cart_items WHERE cart_id = 1";
$rs = $this->db->query($sql);
$sql = "DELETE FROM no_trigger_carts WHERE cart_id = 1";
$rs = $this->db->query($sql);
Теперь, в зависимости от того, насколько вы сами организованы, у вас может быть одна API или метод, который очищает ваши корзины. Если это ваш случай, то у вас будет изолированная функция, которая выполняет два запроса. Если самоорганизация - не ваш конек, то Вам придется всегда помнить, что нужно очищать элементы корзины, когда вы удаляете определенную корзину. Не сложно, но если Вы забудете, то потеряете целостность данных.
Вернемся к триггерам. Создадим простой триггер, который при удалении корзины будет удалять все элементы корзины, которые имеют такой же cart_id:
CREATE TRIGGER `tutorial`.`before_delete_carts`
BEFORE DELETE ON `trigger_carts` FOR EACH ROW
BEGIN
DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;
END
Очень простой синтаксис. Давайте разберем триггер подробно.
Первая строка“CREATE TRIGGER `tutorial`.`before_delete_carts`”. Это команда для MySQL создать триггер для базы данных “tutorial”, который будет иметь имя “before_delete_carts”. Будем использовать схему имен для триггеров “Когда_Что_Таблица”.
Вторая строка указывает для MySQL определение триггера “BEFORE DELETE ON `trigger_carts` FOR EACH ROW”. Мы говорим MySQL, что перед тем, как провести удаление из данной таблицы, для каждой строки нужно сделать что-то. Что нужно сделать, объясняется далее между BEGIN и END. “DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;” Для MySQL задается, что перед тем, как удалить из trigger_carts, нужно взять OLD.cart_id и также удалить из trigger_cart_items. Синтаксис OLD определяет переменную. Он будет обсуждаться в следующем разделе, где будут комбинироваться OLD и NEW.
Преимущество использования триггера - целостность ваших данных перемещается с уровня логики на уровень данных, где она и должна быть. Также есть и некоторый прирост производительности системы.
Два запроса:
Один запрос с триггером:
Как вы можете видеть, существует небольшой прирост производительности, которого следовало ожидать. База данных для примера использует тот же самый сервер, что и клиент. Но если сервер баз данных расположен в другом месте, то следует ожидать более значительной разницы, так как ко времени выполнения запросов добавится время передачи данных между серверами.Также нужно отметить, что первый раз триггер может выполняться значительно медленнее, чем в следующие разы.
Перемещение логики данных на уровень данных подобно тому, как задание стиля перемещается с уровня разметки на уровень презентации, что известно всему миру как CSS.
Чудесный простой триггер : журналирование и аудит
Следующий пример, который мы рассмотрим связан с журналированием событий. Например, мы хотим наблюдать за каждым товаром, который помещается в корзину. Возможно, мы хотим отслеживать рейтинг покупки товаров. Возможно, мы просто хотим иметь копию каждого товара, помещенного в корзину, не обязательно для продажи, а для анализа поведения покупателей. Какими бы ни были причины, давайте посмотрим на триггер INSERT, который открывает возможности для журналирования или аудита наших данных.
До использования триггера, вероятно мы делали что-то похожее:
Теперь мы можем создать очень простой триггер для процесса журналирования:
CREATE TRIGGER `after_insert_cart_items`
AFTER INSERT ON `trigger_cart_items` FOR EACH ROW
BEGIN
INSERT INTO trigger_cart_log (cart_id, item_id)
VALUES (NEW.cart_id, NEW.item_id);
END
Первая строка “CREATE TRIGGER `after_insert_cart_items`”. Для MySQL задается команда, создать триггер с именем “after_insert_cart_items”. Имя может быть “Foo”, или “BullWinkle” или какое-то другое, но лучше использовать ранее описанную схему имен триггера. Далее следует “AFTER INSERT ON `trigger_cart_items` FOR EACH ROW”. Снова мы говорим, что после того, как что-то будет вставлено в trigger_cart_items, для каждой строки нужно выполнить операции между BEGIN и END.
Строка “INSERT INTO trigger_cart_log (cart_id, item_id) VALUES (NEW.cart_id, NEW.item_id);” является стандартным запросом с использованием двух переменных. Здесь используются значения NEW, которые вставляются в таблицу cart_items.
Снова выполнение нашего запроса осуществляется быстрее:
Для проверки, что триггер работает, посмотрим значения в таблице:
Более сложный триггер : бизнес логика
Начиная с этого момента мы перестанем рассматривать старый способ использования множественных запросов и их сравнение с техникой использования триггеров. Давайте рассмотрим несколько более продвинутых примеров использования триггеров.
Бизнес логика - это место, где плодятся ошибки. Не смотря на осторожность и внимание к организации процесса, всегда что-то идет не так. Триггер для UPDATE позволяет несколько смягчить такое положение. У нас есть возможность в триггере вычислить значение OLD и установить значение NEW на основе оценки. Например, мы хотим всегда устанавливать цену на товар с 30% надбавкой к стоимости. Это приводит к тому, что когда мы изменяем (UPDATE) стоимость, мы должны изменить (UPDATE) цену. Давайте используем триггер.
CREATE TRIGGER `after_update_cost`
AFTER UPDATE ON `trigger_items_cost` FOR EACH ROW
BEGIN
UPDATE trigger_items
SET price = (NEW.cost * 1.3)
WHERE item_id = NEW.item_id;
END
Мы изменяем таблицу товаров с ценами, основанными на NEW.cost * 1.3. Если ввести стоимость $50, то цена должна быть $65.
Данный триггер работает отлично.
Давайте рассмотрим более сложный пример. У нас уже есть правило, которое изменяет цену товара на основе стоимости. Теперь мы хотим установить некоторую ярусность в ценах. Если цена меньше $50, то актуальное значение будет $50. Если цена больше $50, но меньше $100, то актуальное значение будет $100.
Для того, чтобы решить задачу, мы снова будем работать с UPDATE, но в этот раз триггер будет выполняться до выполнения запроса. Также будет использоваться выражение IF.
Вот текст триггера:
CREATE TRIGGER `before_update_cost`
BEFORE UPDATE ON `trigger_items_cost` FOR EACH ROW
BEGIN
IF NEW.cost < 50 THEN
SET NEW.cost = 50;
ELSEIF NEW.cost > 50 AND NEW.cost < 100 THEN
SET NEW.cost = 100;
END IF;
END
Это не запрос, а перекрытие значений. Если цена меньше $50, то устанавливаем ее $50. Если цена лежит между $50 и $100, то устанавливаем ее $100. Если она выше, то просто оставляем ее такой, какая она есть. Синтаксис не отличается от других серверных языков. Нужно закрыть выражение IF с помощью END IF.
Проверим работу нашего триггера. Если ввести значение стоимости $30, то цена должна быть $50:
Для значения стоимости $85:
Для проверки того, что триггер AFTER UPDATE все еще работает, цена должна быть $130:
Заключение
В данной статье мы только слегка задели айсберг триггеров в MySQL. Они позволяют переносить правила работы с данными с уровня логики приложения на уровень данных.
Может быть, использование триггеров на одностраничном сайте и является суетой, которая отнимает время и силы. Но сложные вэб приложения могут совершенно преобразиться при использовании триггеров.
Продолжаем изучать SQL в частности Transact-SQL , на примере MS Sql 2008 и сегодня речь пойдет о триггерах . Мы рассмотрим, для чего нужны триггеры, основной синтаксис написания триггера, и, конечно же, все это будем делать на основе простого и понятного примера.
И как обычно начнем с небольшой теории, узнаем, что такое триггер и вообще, зачем он нужен. Т.е. дадим определение триггеру и рассмотрим его назначение в базе данных.
Определение и назначение триггера
Триггер – в базе данных это обычная хранимая процедура, но вызывается она не пользователем, а событием, которое может произойти в базе данных. События могут быть следующие, и относятся они как к таблицам, так и к представлениям VIEW :
- INSERT – добавление записей в таблицу;
- DELETE – удаление записей из таблицы;
- UPDATE – обновление записей таблицы.
С определением разобрались, теперь давайте разберемся, для чего нужны эти триггеры. А нужны они всего лишь для одной цели, выполнение дополнительных действий до или после самой sql инструкции в которой присутствуют вышеперечисленные события. Имеется в виду, Вы сами можете указать, когда выполнять и на какое событие реагировать. Вы можете написать триггер сразу на все события (insert, delete, update) а можете только на одно или на два.
Какие дополнительные действия здесь подразумеваются, в принципе любые, например, на событие insert можно написать триггер и повесить на таблицу, и при добавлении записей, например, отправлять уведомление на почту, или еще пример, который мы разберем чуть ниже, суть которого заключается в сохранении старой и новой записи при обновлении (update) т.е. своего рода вести аудит всех изменений в той или иной таблицы.
Пример создания триггера в MS SQL 2008
Как я уже сказал, наш пример будет заключаться в следующем, мы будем вести аудит всех изменений в определенной таблице, т.е. триггер будет вызываться по событию update.
Мы с Вами уже реализовывали подобную задачу, которую рассматривали в материале Журналирование изменений данных в таблице на Transact-SQL . Но там мы не использовали триггеры, и использовали своего рода импровизированный метод хранения всех изменений, но сегодня как в материале Transact-sql – работа с xml мы будем использовать XML данные именно в триггере.
Примечание! Все действия мы будем выполнять в Management Studio SQL сервера.
И для начала, давайте создадим таблицу, на которую будем вешать триггер:
Код создания таблицы:
CREATE TABLE .( NULL, (255) NULL, (255) NULL) ON GOИ добавим пару строк, которые в дальнейшем мы будем обновлять, я добавил следующие данные, и проверил их наличие с помощью простого запроса select
Теперь давайте создадим таблицу, в которую мы будем записывать все изменения, я назвал ее audit.
Код создания таблицы audit:
CREATE TABLE .( IDENTITY(1,1) NOT NULL, (50) NOT NULL, (15) NOT NULL, NULL, NULL, (50) NOT NULL, NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON GO
- id – это идентификатор с автоинкрементном;
- table_name – название таблицы, в которой произошло изменение;
- oper – операция, в данном случае у нас туда будет записывать update;
- record_old – xml данные старой строки;
- record_new — xml данные новой строки;
- username – кто изменил;
- date_ch – когда изменили.
Теперь давайте приступим непосредственно к написанию самого триггера, и для начала, я приведу пример синтаксиса, немного конечно упрощенного, но так даже лучше для начинающих, так как так более понятно и этого достаточно, чтобы написать свой первый триггер. А весь синтаксис Вы можете посмотреть в Интернете, например в официальной справке Transact-SQL. Просто я считаю, что для начинающих он будет немного сложен и непонятен, поэтому вот простой синтаксис, если можно так сказать основа этого синтаксиса:
CREATE TRIGGER название триггера ON таблица на которую вешать
FOR на какие операции (update, insert, delete)
Сюда пишем сами sql инструкции которые необходимо выполнять при срабатывании триггера
Теперь пришло время привести пример кода самого триггера. SQL инструкции я прокомментировал.
CREATE TRIGGER . ON . for UPDATE AS BEGIN SET NOCOUNT on --переменные для хранения старых и новых данных DECLARE @record_new xml DECLARE @record_old xml --задаем значения этим переменным -- в таблице deleted хранятся старые данные, или удаленные SET @record_old=(SELECT * FROM deleted FOR XML RAW, TYPE); -- в таблице inserted хранятся измененные данные, или только что созданные SET @record_new=(SELECT * FROM inserted FOR XML RAW, TYPE); /*--проверяем, действительно ли update обновил хотя бы одну строку, так как может возникнуть ситуация Вы запустили update при этом он не обновил не одной строки, не подошло условие*/ IF @record_new is not null and @record_old is not null BEGIN INSERT INTO dbo.audit (table_name, oper, record_old, record_new, username, date_ch) VALUES ("test_table", "update", @record_old, @record_new, SUSER_NAME(), GETDATE()) END END GO
Теперь осталось проверить работу данного триггера, для начала запустим update одной строки.
И посмотрим, что у нас появилось в таблице audit.
Как видите, XML данные легко просматриваются, и мы видим, что в старой записи были одни значения, а в новой уже соответственно новые.
Кстати если нажать на эти xml данные можно попасть в отдельное окно, где еще наглядней будет видно, что за данные мы обновили, но для начала давайте попробуем, обновить сразу несколько строк, и посмотрим, как поведет себя наш триггер, запустим простой запрос на обновление:
UPDATE test_table SET pole1 = "обновили" , pole2 = "обновили" WHERE number in(1,2)
И проверяем audit
Как видите у нас добавилась всего одно строка, но мы же обновили две строки, все очень просто все наши данные записались в xml документ, и соответственно в record_old хранятся старые две строки, а в record_new измененные две строки, т.е. сколько бы мы не обновили строк они будут отображены в xml документе. И теперь давайте все-таки нажмем на xml данные и посмотрим что будет.
А здесь отображены все строки, которые мы затронули.
Вот такой простой пример написания триггера, надеюсь, теперь стал понятно, как писать эти самые триггера и для чего их можно использовать. Но учтите триггера не всегда полезны так как неправильное их применение может привести к серьезным последствиям. Поэтому прежде чем повесить триггер на ту или иную таблицу, все тщательно продумайте, возможно, можно будет обойтись и без триггера. Ну, на этом все! Удачи!
333 163
Предшествующий синтаксис относится только к триггерам DML. Триггеры DDL имеют несколько иную форму синтаксиса, которая будет показана позже.
Здесь в параметре schema_name указывается имя схемы, к которой принадлежит триггер, а в параметре trigger_name - имя триггера. В параметре table_name задается имя таблицы, для которой создается триггер. (Также поддерживаются триггеры для представлений, на что указывает наличие параметра view_name.)
Также можно задать тип триггера с помощью двух дополнительных параметров: AFTER и INSTEAD OF. (Параметр FOR является синонимом параметра AFTER.) Триггеры типа AFTER вызываются после выполнения действия, запускающего триггер, а триггеры типа INSTEAD OF выполняются вместо действия, запускающего триггер. Триггеры AFTER можно создавать только для таблиц, а триггеры INSTEAD OF - как для таблиц, так и для представлений.
Параметры INSERT, UPDATE и DELETE задают действие триггера. Под действием триггера имеется в виду инструкция Transact-SQL, которая запускает триггер. Допускается любая комбинация этих трех инструкций. Инструкция DELETE не разрешается, если используется параметр IF UPDATE.
Как можно видеть в синтаксисе инструкции CREATE TRIGGER, действие (или действия) триггера указывается в спецификации AS sql_statement.
Компонент Database Engine позволяет создавать несколько триггеров для каждой таблицы и для каждого действия (INSERT, UPDATE и DELETE). По умолчанию определенного порядка исполнения нескольких триггеров для данного модифицирующего действия не имеется.
Только владелец базы данных, администраторы DDL и владелец таблицы, для которой определяется триггер, имеют право создавать триггеры для текущей базы данных. (В отличие от разрешений для других типов инструкции CREATE это разрешение не может передаваться.)
Изменение структуры триггера
Язык Transact-SQL также поддерживает инструкцию ALTER TRIGGER , которая модифицирует структуру триггера. Эта инструкция обычно применяется для изменения тела триггера. Все предложения и параметры инструкции ALTER TRIGGER имеют такое же значение, как и одноименные предложения и параметры инструкции CREATE TRIGGER.
Для удаления триггеров в текущей базе данных применяется инструкция DROP TRIGGER .
Использование виртуальных таблиц deleted и inserted
При создании действия триггера обычно требуется указать, ссылается ли он на значение столбца до или после его изменения действием, запускающим триггер. По этой причине, для тестирования следствия инструкции, запускающей триггер, используются две специально именованные виртуальные таблицы:
deleted - содержит копии строк, удаленных из таблицы;
inserted - содержит копии строк, вставленных в таблицу.
Структура этих таблиц эквивалентна структуре таблицы, для которой определен триггер.
Таблица deleted используется в том случае, если в инструкции CREATE TRIGGER указывается предложение DELETE или UPDATE, а если в этой инструкции указывается предложение INSERT или UPDATE, то используется таблица inserted . Это означает, что для каждой инструкции DELETE, выполненной в действии триггера, создается таблица deleted. Подобным образом для каждой инструкции INSERT, выполненной в действии триггера, создается таблица inserted.
Инструкция UPDATE рассматривается, как инструкция DELETE, за которой следует инструкция INSERT. Поэтому для каждой инструкции UPDATE, выполненной в действии триггера, создается как таблица deleted, так и таблица inserted (в указанной последовательности).
Таблицы inserted и deleted реализуются, используя управление версиями строк, которое рассматривалось в предыдущей статье. Когда для таблицы с соответствующими триггерами выполняется инструкция DML (INSERT, UPDATE или DELETE), для всех изменений в этой таблице всегда создаются версии строк. Когда триггеру требуется информация из таблицы deleted, он обращается к данным в хранилище версий строк. В случае таблицы inserted, триггер обращается к самым последним версиям строк.
В качестве хранилища версий строк механизм управления версиями строк использует системную базу данных tempdb. По этой причине, если база данных содержит большое число часто используемых триггеров, следует ожидать значительного увеличения объема этой системной базы данных.
Области применения DML-триггеров
Такие триггеры применяются для решения разнообразных задач. В этом разделе мы рассмотрим несколько областей применения триггеров DML, в частности триггеров AFTER и INSTEAD OF.
Триггеры AFTER
Как вы уже знаете, триггеры AFTER вызываются после того, как выполняется действие, запускающее триггер. Триггер AFTER задается с помощью ключевого слова AFTER или FOR. Триггеры AFTER можно создавать только для базовых таблиц. Триггеры этого типа можно использовать для выполнения, среди прочих, следующих операций:
создания журнала логов действий в таблицах базы данных;
реализации бизнес-правил;
принудительного обеспечения ссылочной целостности.
Создание журнала логов
В SQL Server можно выполнять отслеживание изменения данных, используя систему перехвата изменения данных CDC (change data capture). Эту задачу можно также решить с помощью триггеров DML. В примере ниже показывается, как с помощью триггеров можно создать журнал логов действий в таблицах базы данных:
USE SampleDb; /* Таблица AuditBudget используется в качестве журнала логов действий в таблице Project */ GO CREATE TABLE AuditBudget (ProjectNumber CHAR(4) NULL, UserName CHAR(16) NULL, Date DATETIME NULL, BudgetOld FLOAT NULL, BudgetNew FLOAT NULL); GO CREATE TRIGGER trigger_ModifyBudget ON Project AFTER UPDATE AS IF UPDATE(budget) BEGIN DECLARE @budgetOld FLOAT DECLARE @budgetNew FLOAT DECLARE @projectNumber CHAR(4) SELECT @budgetOld = (SELECT Budget FROM deleted) SELECT @budgetNew = (SELECT Budget FROM inserted) SELECT @projectNumber = (SELECT Number FROM deleted) INSERT INTO AuditBudget VALUES (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew) END
В этом примере создается таблица AuditBudget, в которой сохраняются все изменения столбца Budget таблицы Project. Изменения этого столбца будут записываться в эту таблицу посредством триггера trigger_ModifyBudget.
Этот триггер активируется для каждого изменения столбца Budget с помощью инструкции UPDATE. При выполнении этого триггера значения строк таблиц deleted и inserted присваиваются соответствующим переменным @budgetOld, @budgetNew и @projectNumber. Эти присвоенные значения, совместно с именем пользователя и текущей датой, будут затем вставлены в таблицу AuditBudget.
В этом примере предполагается, что за один раз будет обновление только одной строки. Поэтому этот пример является упрощением общего случая, когда триггер обрабатывает многострочные обновления. Если выполнить следующие инструкции Transact-SQL:
то содержимое таблицы AuditBudget будет таким:
Реализация бизнес-правил
С помощью триггеров можно создавать бизнес-правила для приложений. Создание такого триггера показано в примере ниже:
USE SampleDb; -- Триггер trigger_TotalBudget является примером использования -- триггера для реализации бизнес-правила GO CREATE TRIGGER trigger_TotalBudget ON Project AFTER UPDATE AS IF UPDATE (Budget) BEGIN DECLARE @sum_old1 FLOAT DECLARE @sum_old2 FLOAT DECLARE @sum_new FLOAT SELECT @sum_new = (SELECT SUM(Budget) FROM inserted) SELECT @sum_old1 = (SELECT SUM(p.Budget) FROM project p WHERE p.Number NOT IN (SELECT d.Number FROM deleted d)) SELECT @sum_old2 = (SELECT SUM(Budget) FROM deleted) IF @sum_new > (@sum_old1 + @sum_old2) * 1.5 BEGIN PRINT "Бюджет не изменился" ROLLBACK TRANSACTION END ELSE PRINT "Изменение бюджета выполнено" END
Здесь создается правило для управления модификацией бюджетов проектов. Триггер trigger_TotalBudget проверяет каждое изменение бюджетов и выполняет только такие инструкции UPDATE, которые увеличивают сумму всех бюджетов не более чем на 50%. В противном случае для инструкции UPDATE выполняется откат посредством инструкции ROLLBACK TRANSACTION.
Принудительное обеспечение ограничений целостности
В системах управления базами данных применяются два типа ограничений для обеспечения целостности данных: декларативные ограничения, которые определяются с помощью инструкций языка CREATE TABLE и ALTER TABLE; процедурные ограничения целостности, которые реализуются посредством триггеров.
В обычных ситуациях следует использовать декларативные ограничения для обеспечения целостности, поскольку они поддерживаются системой и не требуют реализации пользователем. Применение триггеров рекомендуется только в тех случаях, для которых декларативные ограничения для обеспечения целостности отсутствуют.
В примере ниже показано принудительное обеспечение ссылочной целостности посредством триггеров для таблиц Employee и Works_on:
USE SampleDb; GO CREATE TRIGGER trigger_WorksonIntegrity ON Works_on AFTER INSERT, UPDATE AS IF UPDATE(EmpId) BEGIN IF (SELECT Employee.Id FROM Employee, inserted WHERE Employee.Id = inserted.EmpId) IS NULL BEGIN ROLLBACK TRANSACTION PRINT "Строка не была вставлена/модифицирована" END ELSE PRINT "Строка была вставлена/модифицирована" END
Триггер trigger_WorksonIntegrity в этом примере проверяет ссылочную целостность для таблиц Employee и Works_on. Это означает, что проверяется каждое изменение столбца Id в ссылочной таблице Works_on, и при любом нарушении этого ограничения выполнение этой операции не допускается. (То же самое относится и к вставке в столбец Id новых значений.) Инструкция ROLLBACK TRANSACTION во втором блоке BEGIN выполняет откат инструкции INSERT или UPDATE в случае нарушения ограничения для обеспечения ссылочной целостности.
В этом примере триггер выполняет проверку на проблемы ссылочной целостности первого и второго случая между таблицами Employee и Works_on. А в примере ниже показан триггер, который выполняет проверку на проблемы ссылочной целостности третьего и четвертого случая между этими же таблицами (эти случаи обсуждались в статье "Transact-SQL - создание таблиц"):
USE SampleDb; GO CREATE TRIGGER trigger_RefintWorkson2 ON Employee AFTER DELETE, UPDATE AS IF UPDATE (Id) BEGIN IF (SELECT COUNT(*) FROM Works_on, deleted WHERE Works_on.EmpId = deleted.Id) > 0 BEGIN ROLLBACK TRANSACTION PRINT "Строка не была вставлена/модифицирована" END ELSE PRINT "Строка была вставлена/модифицирована" END
Триггеры INSTEAD OF
Триггер с предложением INSTEAD OF заменяет соответствующее действие, которое запустило его. Этот триггер выполняется после создания соответствующих таблиц inserted и deleted, но перед выполнением проверки ограничений целостности или каких-либо других действий.
Триггеры INSTEAD OF можно создавать как для таблиц, так и для представлений. Когда инструкция Transact-SQL ссылается на представление, для которого определен триггер INSTEAD OF, система баз данных выполняет этот триггер вместо выполнения любых действий с любой таблицей. Данный тип триггера всегда использует информацию в таблицах inserted и deleted, созданных для представления, чтобы создать любые инструкции, требуемые для создания запрошенного события.
Значения столбцов, предоставляемые триггером INSTEAD OF, должны удовлетворять определенным требованиям:
значения не могут задаваться для вычисляемых столбцов;
значения не могут задаваться для столбцов с типом данных timestamp;
значения не могут задаваться для столбцов со свойством IDENTITY, если только параметру IDENTITY_INSERT не присвоено значение ON.
Эти требования действительны только для инструкций INSERT и UPDATE, которые ссылаются на базовые таблицы. Инструкция INSERT, которая ссылается на представления с триггером INSTEAD OF, должна предоставлять значения для всех столбцов этого представления, не допускающих пустые значения NULL. (То же самое относится и к инструкции UPDATE. Инструкция UPDATE, ссылающаяся на представление с триггером INSTEAD OF, должна предоставить значения для всех столбцов представления, которое не допускает пустых значений и на которое осуществляется ссылка в предложении SET.)
В примере ниже показана разница в поведении при вставке значений в вычисляемые столбцы, используя таблицу и ее соответствующее представление:
USE SampleDb; CREATE TABLE Orders (OrderId INT NOT NULL, Price MONEY NOT NULL, Quantity INT NOT NULL, OrderDate DATETIME NOT NULL, Total AS Price * Quantity, ShippedDate AS DATEADD (DAY, 7, orderdate)); GO CREATE VIEW view_AllOrders AS SELECT * FROM Orders; GO CREATE TRIGGER trigger_orders ON view_AllOrders INSTEAD OF INSERT AS BEGIN INSERT INTO Orders SELECT OrderId, Price, Quantity, OrderDate FROM inserted END
В этом примере используется таблица Orders, содержащая два вычисляемых столбца. Представление view_AllOrders содержит все строки этой таблицы. Это представление используется для задания значения в его столбце, которое соотносится с вычисляемым столбцом в базовой таблице, на которой создано представление. Это позволяет использовать триггер INSTEAD OF, который в случае инструкции INSERT заменяется пакетом, который вставляет значения в базовую таблицу посредством представления view_AllOrders. (Инструкция INSERT, обращающаяся непосредственно к базовой таблице, не может задавать значение вычисляемому столбцу.)
Триггеры first и last
Компонент Database Engine позволяет создавать несколько триггеров для каждой таблицы или представления и для каждой операции (INSERT, UPDATE и DELETE) с ними. Кроме этого, можно указать порядок выполнения для нескольких триггеров, определенных для конкретной операции. С помощью системной процедуры sp_settriggerorder можно указать, что один из определенных для таблицы триггеров AFTER будет выполняться первым или последним для каждого обрабатываемого действия. Эта системная процедура имеет параметр @order, которому можно присвоить одно из трех значений:
first - указывает, что триггер является первым триггером AFTER, выполняющимся для модифицирования действия;
last - указывает, что данный триггер является последним триггером AFTER, выполняющимся для инициирования действия;
none - указывает, что для триггера отсутствует какой-либо определенный порядок выполнения. (Это значение обычно используется для того, чтобы выполнить сброс ранее установленного порядка выполнения триггера как первого или последнего.)
Изменение структуры триггера посредством инструкции ALTER TRIGGER отменяет порядок выполнения триггера (первый или последний). Применение системной процедуры sp_settriggerorder показано в примере ниже:
USE SampleDb; EXEC sp_settriggerorder @triggername = "trigger_ModifyBudget", @order = "first", @stmttype="update"
Для таблицы разрешается определить только один первый и только один последний триггер AFTER. Остальные триггеры AFTER выполняются в неопределенном порядке. Узнать порядок выполнения триггера можно с помощью системной процедуры sp_helptrigger или функции OBJECTPROPERTY.
Возвращаемый системной процедурой sp_helptrigger результирующий набор содержит столбец order, в котором указывается порядок выполнения указанного триггера. При вызове функции objectproperty в ее втором параметре указывается значение ExeclsFirstTrigger или ExeclsLastTrigger, а в первом параметре всегда указывается идентификационный номер объекта базы данных. Если указанное во втором параметре свойство имеет значение true, функция возвращает значение 1.
Поскольку триггер INSTEAD OF исполняется перед тем, как выполняются изменения в его таблице, для триггеров этого типа нельзя указать порядок выполнения "первым" или "последним".
Триггеры DDL и области их применения
Ранее мы рассмотрели триггеры DML, которые задают действие, предпринимаемое сервером при изменении таблицы инструкциями INSERT, UPDATE или DELETE. Компонент Database Engine также позволяет определять триггеры для инструкций DDL, таких как CREATE DATABASE, DROP TABLE и ALTER TABLE. Триггеры для инструкций DDL имеют следующий синтаксис:
CREATE TRIGGER trigger_name ON {ALL SERVER | DATABASE } {FOR | AFTER } { event_group | event_type | LOGON} AS {batch | EXTERNAL NAME method_name} Соглашения по синтаксису
Как можно видеть по их синтаксису, триггеры DDL создаются таким же способом, как и триггеры DML. А для изменения и удаления этих триггеров используются те же инструкции ALTER TRIGGER и DROP TRIGGER, что и для триггеров DML. Поэтому в этом разделе рассматриваются только те параметры инструкции CREATE TRIGGER, которые новые для синтаксиса триггеров DDL.
Первым делом при определении триггера DDL нужно указать его область действия. Предложение DATABASE указывает в качестве области действия триггера DDL текущую базу данных, а предложение ALL SERVER - текущий сервер.
После указания области действия триггера DDL нужно в ответ на выполнение одной или нескольких инструкций DDL указать способ запуска триггера. В параметре event_type указывается инструкция DDL, выполнение которой запускает триггер, а в альтернативном параметре event_group указывается группа событий языка Transact-SQL. Триггер DDL запускается после выполнения любого события языка Transact-SQL, указанного в параметре event_group. Ключевое слово LOGON указывает триггер входа.
Кроме сходства триггеров DML и DDL, между ними также есть несколько различий. Основным различием между этими двумя видами триггеров является то, что для триггера DDL можно задать в качестве его области действия всю базу данных или даже весь сервер, а не всего лишь отдельный объект. Кроме этого, триггеры DDL не поддерживают триггеров INSTEAD OF. Как вы, возможно, уже догадались, для триггеров DDL не требуются таблицы inserted и deleted, поскольку эти триггеры не изменяют содержимого таблиц.
В следующих подразделах подробно рассматриваются две формы триггеров DDL: триггеры уровня базы данных и триггеры уровня сервера.
Триггеры DDL уровня базы данных
В примере ниже показано, как можно реализовать триггер DDL, чья область действия распространяется на текущую базу данных:
USE SampleDb; GO CREATE TRIGGER trigger_PreventDrop ON DATABASE FOR DROP_TRIGGER AS PRINT "Перед тем, как удалить триггер, вы должны отключить "trigger_PreventDrop"" ROLLBACK
Триггер в этом примере предотвращает удаление любого триггера для базы данных SampleDb любым пользователем. Предложение DATABASE указывает, что триггер trigger_PreventDrop является триггером уровня базы данных. Ключевое слово DROP_TRIGGER указывает предопределенный тип события, запрещающий удаление любого триггера.
Триггеры DDL уровня сервера
Триггеры уровня сервера реагируют на серверные события. Триггер уровня сервера создается посредством использования предложения ALL SERVER в инструкции CREATE TRIGGER. В зависимости от выполняемого триггером действия, существует два разных типа триггеров уровня сервера: обычные триггеры DDL и триггеры входа. Запуск обычных триггеров DDL основан на событиях инструкций DDL, а запуск триггеров входа - на событиях входа.
В примере ниже демонстрируется создание триггера уровня сервера, который является триггером входа:
USE master; GO CREATE LOGIN loginTest WITH PASSWORD = "12345!", CHECK_EXPIRATION = ON; GO GRANT VIEW SERVER STATE TO loginTest; GO CREATE TRIGGER trigger_ConnectionLimit ON ALL SERVER WITH EXECUTE AS "loginTest" FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= "loginTest" AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = "loginTest") > 1 ROLLBACK; END;
Здесь сначала создается имя входа SQL Server loginTest, которое потом используется в триггере уровня сервера. По этой причине, для этого имени входа требуется разрешение VIEW SERVER STATE, которое и предоставляется ему посредством инструкции GRANT. После этого создается триггер trigger_ConnectionLimit. Этот триггер является триггером входа, что указывается ключевым словом LOGON.
С помощью представления sys.dm_exec_sessions выполняется проверка, был ли уже установлен сеанс с использованием имени входа loginTest. Если сеанс уже был установлен, выполняется инструкция ROLLBACK. Таким образом имя входа loginTest может одновременно установить только один сеанс.
Триггеры и среда CLR
Подобно хранимым процедурам и определяемым пользователем функциям, триггеры можно реализовать, используя общеязыковую среду выполнения (CLR - Common Language Runtime). Триггеры в среде CLR создаются в три этапа:
Создается исходный код триггера на языке C# или Visual Basic, который затем компилируется, используя соответствующий компилятор в объектный код.
Объектный код обрабатывается инструкцией CREATE ASSEMBLY, создавая соответствующий выполняемый файл.
Посредством инструкции CREATE TRIGGER создается триггер.
Выполнение всех этих трех этапов создания триггера CLR демонстрируется в последующих примерах. Ниже приводится пример исходного кода программы на языке C# для триггера из первого примера в статье. Прежде чем создавать триггер CLR в последующих примерах, сначала нужно удалить триггер trigger_PreventDrop, а затем удалить триггер trigger_ModifyBudget, используя в обоих случаях инструкцию DROP TRIGGER.
Using System; using System.Data.SqlClient; using Microsoft.SqlServer.Server; public class Triggers { public static void ModifyBudget() { SqlTriggerContext context = SqlContext.TriggerContext; if (context.IsUpdatedColumn(2)) // Столбец Budget { float budget_old; float budget_new; string project_number; SqlConnection conn = new SqlConnection("context connection=true"); conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT Budget FROM DELETED"; budget_old = (float)Convert.ToDouble(cmd.ExecuteScalar()); cmd.CommandText = "SELECT Budget FROM INSERTED"; budget_new = (float)Convert.ToDouble(cmd.ExecuteScalar()); cmd.CommandText = "SELECT Number FROM DELETED"; project_number = Convert.ToString(cmd.ExecuteScalar()); cmd.CommandText = @"INSERT INTO AuditBudget (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew)"; cmd.Parameters.AddWithValue("@projectNumber", project_number); cmd.Parameters.AddWithValue("@budgetOld", budget_old); cmd.Parameters.AddWithValue("@budgetNew", budget_new); cmd.ExecuteNonQuery(); } } }
Пространство имен Microsoft.SQLServer.Server содержит все классы клиентов, которые могут потребоваться программе C#. Классы SqlTriggerContext и SqlFunction являются членами этого пространства имен. Кроме этого, пространство имен System.Data.SqlClient содержит классы SqlConnection и SqlCommand, которые используются для установления соединения и взаимодействия между клиентом и сервером базы данных. Соединение устанавливается, используя строку соединения "context connection = true".
Затем определяется класс Triggers, который применяется для реализации триггеров. Метод ModifyBudget() реализует одноименный триггер. Экземпляр context класса SqlTriggerContext позволяет программе получить доступ к виртуальной таблице, создаваемой при выполнении триггера. В этой таблице сохраняются данные, вызвавшие срабатывание триггера. Метод IsUpdatedColumn() класса SqlTriggerContext позволяет узнать, был ли модифицирован указанный столбец таблицы.
Данная программа содержит два других важных класса: SqlConnection и SqlCommand. Экземпляр класса SqlConnection обычно применяется для установления соединения с базой данных, а экземпляр класса SqlCommand позволяет исполнять SQL-инструкции.
Программу из этого примера можно скомпилировать с помощью компилятора csc, который встроен в Visual Studio. Следующий шаг состоит в добавлении ссылки на скомпилированную сборку в базе данных:
USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" WITH PERMISSION_SET = SAFE
Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, на основе которого создается триггер CLR. Предложение WITH PERMISSION_SET в примере указывает, что разрешениям доступа присвоено значение SAFE.
Наконец, в примере ниже посредством инструкции CREATE TRIGGER создается триггер trigger_modify_budget:
USE SampleDb; GO CREATE TRIGGER trigger_modify_budget ON Project AFTER UPDATE AS EXTERNAL NAME CLRStoredProcedures.Triggers.ModifyBudget
Инструкция CREATE TRIGGER в примере отличается от такой же инструкции в примерах ранее тем, что она содержит параметр EXTERNAL NAME . Этот параметр указывает, что код создается средой CLR. Имя в этом параметре состоит из трех частей. В первой части указывается имя соответствующей сборки (CLRStoredProcedures), во второй - имя открытого класса, определенного в примере выше (Triggers), а в третьей указывается имя метода, определенного в этом классе (ModifyBudget).