ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2008)База данных SQL AzureХранилище данных SQL AzureParallel Data Warehouse

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

Триггеры DDL срабатывают в ответ на ряд событий языка описания данных (DDL). Эти события прежде всего соответствуют инструкциям Transact-SQL CREATE, ALTER, DROP и некоторым системным хранимым процедурам, которые выполняют схожие с DDL операции. Триггеры входа могут срабатывать в ответ на событие LOGON, возникающее при установке пользовательских сеансов. Триггеры могут быть созданы непосредственно из Transact-SQL инструкций или методов сборок, созданных в Microsoft .NET Framework общеязыковая среда выполнения (CLR) и передачи в экземпляр SQL Server. SQL Server позволяет создавать несколько триггеров для любой инструкции.

SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name ON { table | view } [ WITH [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= assembly_name.class_name.method_name

SQL Server Syntax -- Trigger on an INSERT, UPDATE , or DELETE statement to a table (DML Trigger on memory -optimized tables) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON { table } [ WITH [ ,...n ] ] { FOR | AFTER } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement [ ; ] [ ,...n ] } ::= [ NATIVE_COMPILATION ] [ SCHEMABINDING ] [ EXECUTE AS Clause ]

Trigger on a CREATE , ALTER , DROP , GRANT , DENY , REVOKE or UPDATE statement (DDL Trigger ) CREATE [ OR ALTER ] TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

Trigger on a LOGON event (Logon Trigger ) CREATE [ OR ALTER ] TRIGGER trigger_name ON ALL SERVER [ WITH [ ,...n ] ] { FOR | AFTER } LOGON AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]

Windows Azure SQL Database Syntax -- Trigger on an INSERT, UPDATE , or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement [ ; ] [ ,...n ] [ ; ] > } ::= [ EXECUTE AS Clause ]

Windows Azure SQL Database Syntax -- Trigger on a CREATE, ALTER, DROP , GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger) CREATE [ OR ALTER ] TRIGGER trigger_name ON { DATABASE } [ WITH [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ,...n ] [ ; ] } ::= [ EXECUTE AS Clause ]

Условно изменяет триггер только в том случае, если он уже существует.

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

имя_триггера
Имя триггера. Объект имя_триггера должно соответствовать правилам для , за исключением того, что имя_триггера не может начинаться с символов # или ##.

table | view
Таблица или представление, в которых выполняется триггер DML, иногда указывается как таблица триггера или представление триггера. Указание уточненного имени таблицы или представления не является обязательным. На представление может ссылаться только триггер INSTEAD OF. Триггеры DML не могут быть описаны в локальной или глобальной временных таблицах.

DATABASE
Применяет область действия триггера DDL к текущей базе данных. Если указано, триггер срабатывает всякий раз, когда event_type или event_group происходит в текущей базе данных.

Применяет область действия триггера DDL или триггера входа к текущему серверу. Если указано, триггер срабатывает всякий раз, когда event_type или event_group возникновении в любом месте на текущем сервере.

Затемняет текст инструкции CREATE TRIGGER. Использование параметра WITH ENCRYPTION не позволяет публиковать триггер как часть репликации SQL Server. Параметр WITH ENCRYPTION не может быть указан для триггеров CLR.

EXECUTE AS
Указывает контекст безопасности, в котором выполняется триггер. Позволяет управлять учетной записью пользователя, используемой экземпляром SQL Server для проверки разрешений на любые объекты базы данных, ссылаемые триггером.

Дополнительные сведения см. в разделе .

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

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

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

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

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

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

Триггеры AFTER не могут быть определены на представлениях.

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

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

Использование триггеров INSTEAD OF не допускается в поддерживающих обновление представлениях, которые используют параметр WITH CHECK OPTION. SQL Server вызывает ошибку, если триггер INSTEAD OF добавляется к поддерживающему обновление представлению с параметром WITH CHECK OPTION. Пользователь должен удалить этот параметр при помощи инструкции ALTER VIEW перед определением триггера INSTEAD OF.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
Определяет инструкции изменения данных, по которым срабатывает триггер DML, если он применяется к таблице или представлению. Необходимо указать как минимум одну инструкцию. В определении триггера разрешены любые их сочетания в любом порядке.

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

Указывает, что требуется добавить триггер существующего типа. Аргумент WITH APPEND не может быть использован для триггеров INSTEAD OF или при явном указании триггера AFTER. Аргумент WITH APPEND может использоваться только при указании параметра FOR без INSTEAD OF или AFTER из соображений поддержки обратной совместимости. Аргумент WITH APPEND не может быть указан, если указан параметр EXTERNAL NAME (в случае триггера CLR).

event_type
Имя языкового события Transact-SQL, которое после выполнения вызывает срабатывание триггера DDL. Допустимые события для триггеров DDL, перечислены в .

event_group
Имя стандартной группы событий языка Transact-SQL. Триггер DDL срабатывает после выполнения какого-либо Transact-SQL событий языка, к которому принадлежит event_group . Список групп событий для триггеров DDL, перечислены в .

После завершения работы, инструкции CREATE TRIGGER event_group также функционирует в качестве макроса, добавляя события соответствующих типов в представление каталога sys.trigger_events.

NOT FOR REPLICATION

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

sql_statement
Условия и действия триггера. Условия триггера указывают дополнительные критерии, определяющие, какие события - DML, DDL или событие входа - вызывают срабатывание триггера.

Действия триггера, указанные в инструкциях языка Transact-SQL, вступают в силу после попытки использования операции.

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

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

SELECT * FROM deleted;

Дополнительные сведения см. в разделе .

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

SQL Serverпозволяет обновить текст , ntext , или изображение триггер столбцов с помощью INSTEAD OF для таблиц или представлений.

Для триггеров в таблицах, оптимизированных для памяти единственным sql_statement разрешено на верхнем уровне-АТОМАРНОГО блока. Допускается внутри блока ATOMIC T-SQL, ограничивается допускается внутри собственного процедуры T-SQL.

< method_specifier >

Указывает метод сборки для связывания с CLR-триггером. Этот метод не должен принимать аргументы и возвращать значения void. class_name должен быть допустимым SQL Server идентификатором и существовать как класс в сборке с видимостью сборки. Если класс имеет имя, содержащее точки (.) для разделения частей пространства имен, имя класса должно быть заключено в квадратные скобки () или двойные кавычки (" "). Класс не может быть вложенным.

Триггеры DML часто используются для применения бизнес-правил и обеспечения целостности данных. В SQL Server декларативное ограничение ссылочной целостности обеспечивается инструкциями ALTER TABLE и CREATE TABLE. Однако декларативное ограничение ссылочной целостности не обеспечивает ссылочную целостность между базами данных. Ограничение ссылочной целостности подразумевает выполнение правил связи между первичными и внешними ключами таблиц. Для обеспечения ограничений ссылочной целостности используйте в инструкциях ALTER TABLE и CREATE TABLE ограничения PRIMARY KEY и FOREIGN KEY. Если ограничения распространяются на таблицу триггера, они проверяются после срабатывания триггера INSTEAD OF и до выполнения триггера AFTER. В случае нарушения ограничения выполняется откат действий триггера INSTEAD OF, и триггер AFTER не срабатывает.

Первые и последние триггеры AFTER, которые будут выполнены в таблице, могут быть определены с использованием процедуры sp_settriggerorder. Для таблицы можно определить только один первый и один последний триггер для каждой из операций INSERT, UPDATE и DELETE. Если в таблице есть другие триггеры AFTER, они будут выполняться случайным образом.

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

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

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

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

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

Проверка действий инструкций UPDATE или INSERT на указанные столбцы

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

Ограничения триггеров

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

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

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

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

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

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

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

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

Инструкция WRITETEXT (с ведением журнала и без него) не запускает триггеры.

Следующие инструкции языка Transact-SQL не разрешены в триггерах DML:

Триггеры DDL, как и стандартные триггеры, выполняют хранимые процедуры в ответ на какое-либо событие. В отличие от стандартных триггеров, они не срабатывают в ответ на выполнение инструкций UPDATE, INSERT или DELETE по отношению к таблице или представлению. Вместо этого триггеры срабатывают в первую очередь в ответ на инструкции языка определения данных (DDL). Это инструкции CREATE, ALTER, DROP, GRANT, DENY, REVOKE и UPDATE STATISTICS. Системные хранимые процедуры, выполняющие операции, подобные операциям DDL, также могут запускать триггеры DDL.

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

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

В отличие от триггеров DML, триггеры DDL не ограничены областью схемы. Поэтому для запроса метаданных о триггерах DDL нельзя воспользоваться такими функциями как OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY и OBJECTPROPERTYEX. Используйте вместо них представления каталога. Дополнительные сведения см. в разделе .

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

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

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

Отключение триггера входа

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

Возвращаемые результаты

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

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

Несколько триггеров

SQL Server позволяет создавать несколько триггеров для каждого события DML, DDL и LOGON. Например, если инструкция CREATE TRIGGER FOR UPDATE выполняется в таблице, уже имеющей триггер UPDATE, дополнительно создается триггер обновления. В более ранних версиях SQL Server был разрешен только один триггер в каждой таблице для каждого события изменения данных INSERT, UPDATE или DELETE.

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

SQL Server разрешает рекурсивный вызов триггеров, если с помощью инструкции ALTER DATABASE включена настройка RECURSIVE_TRIGGERS.

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

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

При косвенной рекурсии приложение обновляет таблицу T1. Это событие вызывает срабатывание триггера TR1, обновляющего таблицу T2. Это вызывает срабатывание триггера T2 и обновление таблицы T1.

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

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

В следующем примере используются оба типа рекурсий: прямая и косвенная. Допустим, для таблицы T1 определены два триггера: TR1 и TR2. Триггер TR1 рекурсивно обновляет таблицу T1. Инструкция UPDATE выполняет каждый из триггеров TR1 и TR2 один раз. В дополнение к этому срабатывание триггера TR1 вызывает выполнение триггеров TR1 (рекурсивно) и TR2. В таблицах inserted и deleted триггера содержатся строки, которые относятся только к инструкции UPDATE, вызвавшей срабатывание триггера.

Отключение настройки RECURSIVE_TRIGGERS предотвращает выполнение только прямых рекурсий. Чтобы отключить косвенную рекурсию, с помощью хранимой процедуры sp_configure присвойте параметру сервера nested triggers значение 0.

Если один из триггеров выполняет инструкцию ROLLBACK TRANSACTION, никакие другие триггеры, вне зависимости от уровня вложенности, не срабатывают.

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

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

Чтобы отменить вложенные триггеры, присвойте значение 0 параметру nested triggers хранимой процедуры sp_configure. В конфигурации по умолчанию вложенные триггеры разрешены. Если вложенные триггеры отключены, рекурсивные триггеры тоже будут отключены, вне зависимости от настройки RECURSIVE_TRIGGERS, установленной с помощью инструкции ALTER DATABASE.

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

Отложенная интерпретация имен

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

Для создания триггера DML требуется разрешение ALTER на таблицу или представление, в которых создается триггер.

Для создания триггера DDL с областью действия в пределах сервера (ON ALL SERVER) или триггера входа требуется разрешение CONTROL SERVER на сервер. Для создания триггера DDL с областью видимости в пределах базы данных (ON DATABASE) требуется разрешение ALTER ANY DATABASE DDL TRIGGER на текущую базу данных.

A. Использование триггера DML с предупреждающим сообщением

Следующий триггер DML отправляет клиенту сообщение, когда кто-то пытается добавить или изменить данные в таблице Customer в базе данных AdventureWorks2012.

CREATE TRIGGER reminder1 ON Sales.Customer AFTER INSERT , UPDATE AS RAISERROR ("Notify Customer Relations" , 16 , 10 ); GO

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

В следующем примере указанному пользователю (MaryM) по электронной почте отправляется сообщение при изменении таблицы Customer .

CREATE TRIGGER reminder2 ON Sales.Customer AFTER INSERT , UPDATE , DELETE AS EXEC msdb.dbo.sp_send_dbmail @profile_name = "AdventureWorks2012 Administrator" , @recipients = "[email protected]" , @body = "Don""t forget to print a report for the sales force." , @subject = "Reminder" ; GO

В. Использование триггера DML AFTER для принудительного применения бизнес-правил между таблицами PurchaseOrderHeader и Vendor

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

В следующем примере создается триггер DML в базе данных AdventureWorks&2012;. Этот триггер выполняет проверку кредитоспособности поставщика является хорошим (не 5) при попытке вставить новый заказ на покупку в PurchaseOrderHeader таблицы. Для получения сведений о кредитоспособности поставщика требуется ссылка на таблицу Vendor . В случае слишком низкой кредитоспособности выводится соответствующее сообщение и вставка не выполняется.

This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table -- when the credit rating of the specified vendor is set to 5 (below average). CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader AFTER INSERT AS IF EXISTS (SELECT * FROM Purchasing.PurchaseOrderHeader AS p JOIN inserted AS i ON p .PurchaseOrderID = i .PurchaseOrderID JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = p .VendorID WHERE v.CreditRating = 5 ) BEGIN RAISERROR ("A vendor""s credit rating is too low to accept new purchase orders." , 16 , 1 ); ROLLBACK TRANSACTION ; RETURN END ; GO -- This statement attempts to insert a row into the PurchaseOrderHeader table -- for a vendor that has a below average credit rating. -- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back. INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status , EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight) VALUES (2 ,3 ,261 ,1652 ,4 ,GETDATE () ,GETDATE () ,44594.55 ,3567.564 ,1114.8638 ); GO

Г. Использование триггера DDL уровня базы данных

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

CREATE TRIGGER safety ON DATABASE FOR DROP_SYNONYM AS RAISERROR ("You must disable Trigger "safety" to drop synonyms!" ,10 , 1 ) ROLLBACK GO DROP TRIGGER safety ON DATABASE ; GO

Д. Использование триггера DDL уровня сервера

В следующем примере триггер DDL используется для вывода сообщения при возникновении на данном экземпляре сервера любого из событий CREATE DATABASE, а функция EVENTDATA используется для получения текста соответствующей инструкции на языке Transact-SQL. Дополнительные примеры использования функции EVENTDATA в триггерах DDL см. .

CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT "Database Created." SELECT EVENTDATA ().value ("(/EVENT_INSTANCE/TSQLCommand/CommandText)" ,"nvarchar(max)" ) GO DROP TRIGGER ddl_trig_database ON ALL SERVER ; GO

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

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

USE master ; GO CREATE LOGIN login_test WITH PASSWORD = "3KHJ6dhx(0xVYsdf" MUST_CHANGE, CHECK_EXPIRATION = ON ; GO GRANT VIEW SERVER STATE TO login_test; GO CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS "login_test" FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= "login_test" AND (SELECT COUNT (*) FROM sys .dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = "login_test" ) >

Конспект лекций по дисциплине «Базы данных»

Тема: Процедуры и триггеры

(на примере MS SQL Server)

составитель: Л. В. Щёголева

ПетрГУ, кафедра прикладной математики и кибернетики

Введение.........................................................................................................
	Описание структуры базы данных............................................................
	Понятие процедуры....................................................................................
	Команды работы с процедурами...............................................................
	Понятие триггера........................................................................................
	Команды работы с триггерами..................................................................
	Примеры реализации триггеров..............................................................
	Пример 1...............................................................................................
	Пример 2...............................................................................................
	Пример 3...............................................................................................
	Пример 4...............................................................................................
	Пример 5...............................................................................................

ПетрГУ, кафедра прикладной математики и кибернетики

Введение

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

с описанием их работы.

Все команды написаны в синтаксисе MS SQL Server.

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

разделе 1.

ПетрГУ, кафедра прикладной математики и кибернетики

1 Описание структуры базы данных

Таблица tblFaculty содержит информацию о факультетах университета.

Наименование			Описание
атрибута
			Идентификатор факультета
			Название факультета
			ФИО декана
			Номер кабинета деканата
			Телефон деканата
			Количество студентов факультета

Таблица tblStudent содержит информацию о студентах университета в одном учебном году.

Наименование атрибута			Описание
			Идентификатор студента
			ФИО студента
			Стипендия

Таблица tblGroup содержит информацию о студенческих группах университета в одном учебном году.

Наименование атрибута			Описание
			Идентификатор группы
			Староста
			Номер группы
			Факультет

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

	Наименование атрибута			Описание
				Идентификатор предмета
				Название предмета
				Количество часов лекций
				Количество часов практик
				Факультет
	Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

ПетрГУ, кафедра прикладной математики и кибернетики

Таблица tblRoom содержит информацию об аудиториях университета.

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

Наименование атрибута			Описание
			Идентификатор
			Аудитория
			День недели
			ФИО преподавателя

ПетрГУ, кафедра прикладной математики и кибернетики

2 Понятие процедуры

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

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

– централизованный контроль доступа к данным;

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

– снижает трафик в сети в системах «клиент-сервер» за счет передачи только имени процедуры и ее параметров вместо обмена данными, а процедура выполняется на сервере;

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

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

процедуру, но не управлять данными, которые вызываются этой процедурой; Недостаток: отсутствие стандартов в реализации процедур.

ПетрГУ, кафедра прикладной математики и кибернетики

3 Команды работы с процедурами в MS SQL Server

Создание процедуры

CREATE PROCEDURE <имя процедуры>

[@<имя параметра> <тип данных> , ...]

BEGIN

<операторы>

Имена всех переменных в MS SQL Server должны начинаться с символа

Вызов процедуры

EXECUTE <имя процедуры> [{@<имя переменной> | <значение параметра>}, ...]

Удаление процедуры

DROP PROCEDURE <имя процедуры>

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

Create Procedure prStudentsOfFaculty @id int, @total_sum int output AS

Set @total_sum = 0

Set @total_sum = (Select count(*) From tblStudent, tblGroup Where (tblStudent.GroupId = tblGroup.GroupId) and (tblGroup.FacultyId = @id)) End

ПетрГУ, кафедра прикладной математики и кибернетики

4 Понятие триггера

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

Особенности реализации триггеров в MS SQL Server

В MS SQL Server:

– триггер может быть вызван либо после выполнения операции, либо вместо выполнения операции;

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

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

o таблица Inserted – содержит измененные или добавленные записи таблицы;

o таблица Deleted – содержит записи до выполнения изменений или удаленные записи таблицы;

– в теле триггера, определенного для операции Insert, доступна только таблица

– в теле триггера, определенного для операции Delete, доступна только таблица

– в теле триггера, определенного для операции Update, доступны обе таблицы

Inserted и Deleted;

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

ПетрГУ, кафедра прикладной математики и кибернетики

5 Команды работы с триггерами

Создание

CREATE TRIGGER <имя триггера> ON <имя таблицы>

{ FOR | AFTER | INSTEAD OF }

[ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] AS

DECLARE @<имя переменной> <тип данных>, ...

BEGIN <операторы>

Удаление

DROP TRIGGER <имя триггера>

ПетрГУ, кафедра прикладной математики и кибернетики

6 Примеры реализации триггеров

Ограничение предметной области: стипендия студента не может быть увеличена более чем на 5% от предыдущей стипендии.

CREATE TRIGGER tgrStudentGrantUpdate

ON tblStudent AFTER UPDATE

DECLARE @Grant_old float, @Grant_new float, @Id int;

Select @Grant_old = Grant from Deleted

Select @Grant_new = Grant, @Id = StudentId from Inserted

IF (@Grant_new - @Grant_old > 0.05 * @Grant_old)

UPDATE tblStudent SET Grant = 1.05 * @Grant_old

WHERE StudentId = @Id

Триггер tgrStudentGrantUpdate создан для таблицы tblStudent. Триггер будет срабатывать после выполнения операции изменения данных.

В триггере определены три локальные переменные: @Grant_old (вещественного типа) для хранения старой стипендии студента, @Grant_new (вещественного типа) для хранения новой стипендии студента, @Id (целого типа) для хранения идентификатора студента.

При вызове триггера СУБД создает две таблицы: Deleted, содержащую измененные записи до их изменения, и Inserted, содержащую измененные записи после их изменения.

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

Далее, в теле триггера проверяется условие о величине изменения стипендии. Если стипендия изменилась более чем на 5%, то триггер вносит поправку в данные – увеличивает стипендию только на 5% по сравнению со предыдущим значением стипендии студента. Это действие выполняется посредством вызова операции Update в таблице tblStudent для соответствующего студента.

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

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

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

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

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

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

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

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

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

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

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

<Определение_триггера>::= CREATE TRIGGER имя_триггера BEFORE | AFTER <триггерное_событие> ON <имя_таблицы> <тело_триггера>

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

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

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

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

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

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

Реализация триггеров в среде MS SQL Server

В реализации СУБД MS SQL Server используется следующий оператор создания или изменения триггера :

<Определение_триггера>::= {CREATE | ALTER} TRIGGER имя_триггера ON {имя_таблицы | имя_просмотра } { { { FOR | AFTER | INSTEAD OF } { [ DELETE] [,] [ INSERT] [,] [ UPDATE] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS sql_оператор[...n] } | { {FOR | AFTER | INSTEAD OF } { [,] } [ WITH APPEND] [ NOT FOR REPLICATION] AS { IF UPDATE(имя_столбца) [ {AND | OR} UPDATE(имя_столбца)] [...n] | IF (COLUMNS_UPDATES(){оператор_бит_обработки} бит_маска_изменения) {оператор_бит_сравнения }бит_маска [...n]} sql_оператор [...n] } }

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

Рассмотрим назначение аргументов из команды CREATE | ALTER TRIGGER .

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

При указании аргумента WITH ENCRYPTION сервер выполняет шифрование кода триггера , чтобы никто, включая администратора, не мог получить к нему доступ и прочитать его. Шифрование часто используется для скрытия авторских алгоритмов обработки данных, являющихся интеллектуальной собственностью программиста или коммерческой тайной.

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

В SQL Server существует два параметра, определяющих поведение триггеров :

• AFTER . Триггер выполняется после успешного выполнения вызвавших его команд. Если же команды по какой-либо причине не могут быть успешно завершены, триггер не выполняется. Следует отметить, что изменения данных в результате выполнения запроса пользователя и выполнение триггера осуществляется в теле одной транзакции: если произойдет откат триггера , то будут отклонены и пользовательские изменения. Можно определить несколько AFTER -триггеров для каждой операции (INSERT , UPDATE , DELETE ). Если для таблицы предусмотрено выполнение нескольких AFTER -триггеров, то с помощью системной хранимой процедуры sp_settriggerorder можно указать, какой из них будет выполняться первым, а какой последним. По умолчанию в SQL Server все триггеры являются AFTER -триггерами.
• INSTEAD OF . Триггер вызывается вместо выполнения команд. В отличие от AFTER -триггера INSTEAD OF -триггер может быть определен как для таблицы, так и для просмотра. Для каждой операции INSERT , UPDATE , DELETE можно определить только один INSTEAD OF -триггер.

Триггеры различают по типу команд, на которые они реагируют.

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

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

Конструкции [ DELETE] [,] [ INSERT] [,] [ UPDATE] и FOR | AFTER | INSTEAD OF } { [,] определяют, на какую команду будет реагировать триггер . При его создании должна быть указана хотя бы одна команда. Допускается создание триггера , реагирующего на две или на все три команды.

Аргумент WITH APPEND позволяет создавать несколько триггеров каждого типа.

При создании триггера с аргументом NOT FOR REPLICATION запрещается его запуск во время выполнения модификации таблиц механизмами репликации.

Конструкция AS sql_оператор[...n] определяет набор SQL- операторов и команд, которые будут выполнены при запуске триггера .

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

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

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

Программирование триггера

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

• команда INSERT – в таблице inserted содержатся все строки, которые пользователь пытается вставить в таблицу; в таблице deleted не будет ни одной строки; после завершения триггера все строки из таблицы inserted переместятся в исходную таблицу;
• команда DELETE – в таблице deleted будут содержаться все строки, которые пользователь попытается удалить; триггер может проверить каждую строку и определить, разрешено ли ее удаление; в таблице inserted не окажется ни одной строки;
• команда UPDATE – при ее выполнении в таблице deleted находятся старые значения строк, которые будут удалены при успешном завершении триггера . Новые значения строк содержатся в таблице inserted . Эти строки добавятся в исходную таблицу после успешного выполнения триггера .

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

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

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

Для получения списка столбцов, измененных при выполнении команд INSERT или UPDATE , вызвавших выполнение триггера , можно использовать функцию COLUMNS_UPDATED() . Она возвращает двоичное число, каждый бит которого, начиная с младшего, соответствует одному столбцу таблицы (в порядке следования столбцов при создании таблицы). Если бит установлен в значение "1", то соответствующий столбец был изменен. Кроме того, факт изменения столбца определяет и функция UPDATE (имя_столбца).

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

DROP TRIGGER {имя_триггера} [,...n]

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

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

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

INSERT INTO Сделка VALUES (3,1,-299,"01/08/2002")

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

CREATE TRIGGER Триггер_ins ON Сделка FOR INSERT AS IF @@ROWCOUNT=1 BEGIN IF NOT EXISTS(SELECT * FROM inserted WHERE -inserted.количество<=ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "Отмена поставки: товара на складе нет" END END Пример 14.1. Использование триггера для реализации ограничений на значение.

Пример 14.2. Использования триггера для сбора статистических данных.

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

INSERT INTO Сделка VALUES (3,1,200,"01/08/2002")

поставляется товар с кодом 3 от клиента с кодом 1 в количестве 200 единиц.

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

ALTER TRIGGER Триггер_ins ON Сделка FOR INSERT AS DECLARE @x INT, @y INT IF @@ROWCOUNT=1 --в таблицу Сделка добавляется запись --о поставке товара BEGIN --количество проданного товара должно быть не --меньше, чем его остаток из таблицы Склад IF NOT EXISTS(SELECT * FROM inserted WHERE -inserted.количество< =ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "откат товара нет " END --если записи о поставленном товаре еще нет, --добавляется соответствующая запись --в таблицу Склад IF NOT EXISTS (SELECT * FROM Склад С, inserted i WHERE С.КодТовара=i.КодТовара) INSERT INTO Склад (КодТовара,Остаток) ELSE --если запись о товаре уже была в таблице --Склад, то определяется код и количество --товара издобавленной в таблицу Сделка записи BEGIN SELECT @y=i.КодТовара, @x=i.Количество FROM Сделка С, inserted i WHERE С.КодТовара=i.КодТовара --и производится изменения количества товара в --таблице Склад UPDATE Склад SET Остаток=остаток+@x WHERE КодТовара=@y END END Пример 14.2. Использования триггера для сбора статистических данных.

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

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

CREATE TRIGGER Триггер_del ON Сделка FOR DELETE AS IF @@ROWCOUNT=1 -- удалена одна запись BEGIN DECLARE @y INT,@x INT --определяется код и количество товара из --удаленной из таблицы Склад записи SELECT @y=КодТовара, @x=Количество FROM deleted --в таблице Склад корректируется количество --товара UPDATE Склад SET Остаток=Остаток-@x WHERE КодТовара=@y END Пример 14.3. Триггер для обработки операции удаления записи из таблицы

Пример 14.4. Создать триггер для обработки операции изменения записи в таблице Сделка , например, такой командой:

во всех сделках с товаром, имеющим код, равный 3, уменьшить количество товара на 10 единиц.

Указанная команда может привести к изменению сразу нескольких записей в таблице Сделка . Поэтому покажем, как создать триггер , обрабатывающий не одну запись. Для каждой измененной записи необходимо для старого (до изменения) кода товара уменьшить остаток товара на складе на величину старого (до изменения) количества товара и для нового (после изменения) кода товара увеличить его остаток на складе на величину нового (после изменения) значения. Чтобы обработать все измененные записи, введем курсоры, в которых сохраним все старые (из таблицы deleted ) и все новые значения (из таблицы inserted ).

CREATE TRIGGER Триггер_upd ON Сделка FOR UPDATE AS DECLARE @x INT, @x_old INT, @y INT, @y_old INT -- курсор с новыми значениями DECLARE CUR1 CURSOR FOR SELECT КодТовара,Количество FROM inserted -- курсор со старыми значениями DECLARE CUR2 CURSOR FOR SELECT КодТовара,Количество FROM deleted OPEN CUR1 OPEN CUR2 -- перемещаемся параллельно по обоим курсорам FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN --для старого кода товара уменьшается его --количество на складе UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old --для нового кода товара, если такого товара --еще нет на складе, вводится новая запись IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара,Остаток) VALUES (@x,@y) ELSE --иначе для нового кода товара увеличивается --его количество на складе UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Пример 14.4. триггер для обработки операции изменения записи в таблице

В рассмотренном триггере отсутствует сравнение количества товара при изменении записи о сделке с его остатком на складе.

Пример 14.5. Исправим этот недостаток. Для генерирования сообщения об ошибке используем в теле триггера команду MS SQL Server RAISERROR , аргументами которой являются текст сообщения, уровень серьезности и статус ошибки.

ALTER TRIGGER Триггер_upd ON Сделка FOR UPDATE AS DECLARE @x INT, @x_old INT, @y INT, @y_old INT ,@o INT DECLARE CUR1 CURSOR FOR SELECT КодТовара,Количество FROM inserted DECLARE CUR2 CURSOR FOR SELECT КодТовара,Количество FROM deleted OPEN CUR1 OPEN CUR2 FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN SELECT @o=остаток FROM Склад WHERE кодтовара=@x IF @o<-@y BEGIN RAISERROR("откат",16,10) CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR22 ROLLBACK TRAN RETURN END UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара,Остаток) VALUES (@x,@y) ELSE UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Пример 14.5. Исправленный вариант триггера для обработки операции изменения записи в таблице

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

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

ALTER TRIGGER Триггер_upd ON Сделка INSTEAD OF UPDATE AS DECLARE @k INT, @k_old INT DECLARE @x INT, @x_old INT, @y INT DECLARE @y_old INT ,@o INT DECLARE CUR1 CURSOR FOR SELECT КодСделки, КодТовара,Количество FROM inserted DECLARE CUR2 CURSOR FOR SELECT КодСделки, КодТовара,Количество FROM deleted OPEN CUR1 OPEN CUR2 FETCH NEXT FROM CUR1 INTO @k,@x, @y FETCH NEXT FROM CUR2 INTO @k_old,@x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN SELECT @o=остаток FROM Склад WHERE КодТовара=@x IF @o>=-@y BEGIN RAISERROR("изменение",16,10) UPDATE Сделка SET количество=@y, КодТовара=@x WHERE КодСделки=@k UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара, Остаток) VALUES (@x,@y) ELSE UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x END ELSE RAISERROR("запись не изменена",16,10) FETCH NEXT FROM CUR1 INTO @k,@x, @y FETCH NEXT FROM CUR2 INTO @k_old,@x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Пример 14.6. Триггер, позволяющий отменять изменение только некоторых записей и выполнять изменение остальных.

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

Назначение триггеров

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

Объявление триггеров

CREATE TRIGGER {BEFORE|AFTER } {DELETE|INSERT|UPDATE [OF ]} ON REFERENCING {OLD {[ROW ]|TABLE [AS ] } NEW {ROW|TABLE } [AS ] }] [FOR EACH {STATEMENT|ROW [WHEN ]}]
[BEGIN ATOMIC ]

[END ]

Ключевые слова

. BEFORE|AFTER – время запуска триггера – до | после операции обновления.
. DELETE|INSERT|UPDATE = событие срабатывания триггера.
. FOR EACH ROW – для каждой строки (строчный триггер, тогда и WHEN).
. FOR EACH STATEMENT – для всей команды (действует по умолчанию).
. REFERENCING – позволяет присваивать до 4-х псевдонимов старым и | или новым строкам и | или таблицам, к которым могут обращаться триггера.

Ограничения триггеров

Тело триггера не может содержать операторов:
. Определения, удаления и изменения объектов БД (таблиц, доменов и т.п.)
. Обработки транзакций (COMMIT, ROLLBACK)
. Подключения и отключения к БД (CONNECT, DISCONNECT)

Особенности применения
. Триггер выполняется после применения всех других (декларативны) проверок целостности и целесообразен тогда, когда критерий проверки достаточно сложен. Если декларативные проверки отклоняют операцию обновления, то до выполнения триггеров дело не доходит. Триггер работает в контексте транзакции, а ограничение FK нет.
. Если триггер вызывает дополнительную модификацию своей базовой таблицы, то чаще всего это не приводит к его рекурсивному выполнению, однако это следует уточнять. В СУБД SQL Server 2005 предусмотрена возможность указания рекурсии до 255 уровней с помощью ключевого слова OPTION (MAXRECURSIV 3).
. Триггеры обычно не выполняются при обработке больших двоичных столбцов (BLOB).
. Следует помнить, что всякий раз при обновлении данных СУБД автоматически создает так называемые триггерные виртуальные таблицы, которые в различных СУБД носят разные название. В InterBase и Oracle – Это New и Old. В SQL Server – Inserted и Deleted. Причем при изменении данных создаются обе. Эти таблицы имеют то же количество столбцов, с теми же именами и доменами, что и обновляемая таблица. В СУБД SQL Server 2005 предусмотрена возможность указания таблицы, включая временную, в которую следует вставить данные с помощью ключевого слова OUTPUT Inserted.ID,… INTO @ .
. В ряде СУБД допустимо объявлять триггеры для нескольких действий одновременно. Для реализации разных реакций на различные действия в Oracle предусмотрены предикаты Deleting, Inserting, Updating, возвращающие True для соответствующего вида обновления.
. В СУБД Oracle можно для триггеров Update указать список столбцов (After Update Of), что обеспечит вызов триггера только при изменении значений только этих столбцов.
. Для каждого триггерного события может быть объявлено несколько триггеров (в Oracle 12 триггеров на таблицу) и обычно порядок их запуска определяется порядком создания. В некоторых СУБД, например, InterBase, порядок запуска указывается с помощью дополнительного ключевого слова POSITION . В общем случае считается, что первоначально должны выполняться триггеры для каждой команды, а затем – для каждой строки.
. Триггеры можно встраивать друг в друга. Так SQL Server допускает 32 уровня вложения (с помощью глобальной переменной @@NextLevel можно определить уровень вложения).

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

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

Изменение и удаление триггеров

Для удаление триггера используется оператор DROP TRIGGER
. Для изменения триггера используется оператор ALTER TRIGGER …
. Отключение триггеров
В ряде случаев, например, при пакетной загрузке, триггеры требуется отключать. В ряде СУБД предусмотрены соответствующие возможности. В Oracle и SQL Server ключевые слова DISABLE|ENABLE, в InterBase INACTIVE|ACTIVE в операторе ALTER TRIGGER.

Особенности промышленных серверов

1) InterBase/Firebird

CREATE TRIGGER FOR {ACTIVE|INACTIVE } {BEFORE|AFTER } {INSERT|DELETE|UPDATE } [POSITION ]
AS [DECLARE VARIABLE [()]]
BEGIN

END

Пример:

CREATE TRIGGER BF_Del_Cust FOR Customer
ACTIVE BEFORE DELETE POSITION 1 AS
BEGIN
DELETE FROM Orders WHERE Orders.CNum=Customer.CNum;
END;

2) SQL Server

CREATE TRIGGER ON [WITH ENCRYPTION ] {FOR|AFTER|INSTEAD OF } {INSERT|UPDATE|DELETE }
AS

USE B1;
GO
CREATE TRIGGER InUpCust1 ON Customer AFTER INSERT, UPDATE
AS RAISEERROR(‘Изменена таблица Customer’);

Дополнительные виды триггеров

В СУБД Oracle и SQL Server есть возможность создания (замещающих) триггеров для не обновляемых представлений. Для этого предусмотрены ключевые слова INSTEAD OF:

CREATE TRIGGER ON INSTEAD OF INSERT AS …

Можно отслеживать попытки клиента обновлять данные с помощью представлений и выполнять какие-либо действия, обрабатывать не обновляемые представления и т.п.
. В СУБД SQL Server предусмотрен триггер отката, фактически прекращающий все действия с выдачей сообщения:

ROLLBACK TRIGGER

1. Изучить типы триггеров, которые могут создаваться на сервере MS SQL Server 2000.
2. Изучить операторы описания триггеров разных типов и ограничения, накладываемые на допустимые операторы внутри тела триггера.
3. Изучить порядок создания и методы отладки триггеров на сервере MS SQL Server 2000.
4. Разработать пять триггеров для учебной базы данных «Библиотека», предложенных вашим преподавателем из заданий, описанных в работе.
5. Подготовить отчет о проделанной работе в электронном виде.

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

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

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

В MS SQL SERVER 2000 появился новый вид триггера — INSTEAD OF -триггер. Его принципиальное отличие от обычных (AFTER ) триггеров состоит в том, что он выполняется не после выполнения операции вставки, изменения или удаления, а вместо нее.

Наиболее общее применение триггера — поддержка целостности в базах данных.

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

Для создания триггера необходимо быть владельцем таблицы, для которой триггер создается, либо входить в роль db_owner или db_ddladmin , либо же являться администратором SQL-сервера, то есть входить в фиксированную роль сервера sysadmins . При добавлении триггера к таблице изменяется тип доступа, отношение к ней других объектов и т. д.

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

CREATE TRIGGER имя_триггера
ON таблица
{
{FOR | AFTER | INSTEAD OF} { [,] [,] }

AS
{IF UPDATE (столбец_i)
[{AND | OR} UPDATE (столбец_j)]
[… n]
| IF (COLUMNS_UPDATED() {побитовый_оператор} битовая_маска)
{оператор_сравнения} битовая_маска_столбца [… n]
}
инструкции_SQL [… n]
}
}

• имя_триггера — должно соответствовать стандартным соглашениям об именах объектов SQL Server и быть уникальным в базе данных;
• таблица — название таблицы, для которой создается триггер;
• WITH ENCRYPTION — эта опция дает разработчикам возможность запретить пользователям читать текст триггера после его загрузки на сервер. Опять же, отметим, что для того чтобы сделать текст триггера действительно невосстановимым, следует после шифрования удалить соответствующие ему строки из таблицы syscomments ;
• FOR DELETE , INSERT , UPDATE — ключевые слова, определяющие операцию модификации таблицы, при выполнении которой будет активизирован триггер;
• WITH APPEND — эта опция необходима, только если установленный уровень совместимости не превышает 65 и используется для создания дополнительных триггеров;
• NOT FOR REPLICATION — показывает, что триггер не активизируется при модификации таблицы в процессе репликации;
• AS — ключевое слово, задающее начало определения триггера;
• инструкции_SQL — в T-SQL триггер может содержать любое количество инструкций SQL, если они заключены в операторные скобки BEGIN ... END ;
• IF UPDATE (столбец) — для операций добавления и обновления данных можно определить дополнительные условия на конкретный столбец таблицы; при указании нескольких столбцов они разделяются логическими операторами;
• IF (COLUMNS_UPDATED()…) — выше было показано, как можно с помощью конструкции IF UPDATE (столбец) определять, какие столбцы затрагиваются изменениями. Если необходимо проверять, изменяется ли какой-то один конкретный столбец, эта конструкция очень удобна. Однако при построении сложного условия, включающего много столбцов, данная конструкция получается слишком громоздкой. Для таких случаев предназначена конструкция IF (COLUMNS_UPDATED()…) . Результатом функции COLUMNS_UPDATED() является набор битов, каждый из которых отвечает за один столбец таблицы; младший бит соответствует первому стобцу, старший — последнему. Если в операции, вызвавшей срабатывание триггера, была попытка изменить некоторый столбец, то соответствующий бит будет установлен в 1;
• побитовый_оператор — побитовый оператор, определяющий операцию выделения нужных битов, полученных с помощью COLUMNS_UPDATED() . Обычно используется оператор & ;
• битовая_маска — в сочетании с побитовым оператором битовая маска позволяет выделить интересующие разработчика биты, то есть определить, изенялись ли в операции, вызвавшей срабатывание триггера, интересующие его столбцы;
• оператор_сравнения и битовая_маска_столбца — функция COLUMNS_UPDATED() дает набор битов, соответствующий изменяемым столбцам. С помощью битовой маски и побитового оператора над этим набором битов производится преобразование и получается некий промежуточный результат. С помощью оператора сравнения этот промежуточный результат сравнивается с битовой маской столбца. Если результат сравнения — истина, то набор инструкций SQL, составляющий тело триггера, будет выполнен, иначе — нет.

Пусть таблица имеет следующую структуру:

CREATE table mytable (a int, b int, c int, d int, e int)

Пять столбцов соответствуют пяти битам, из которых младший соответствует столбцу a , старший — столбцу e . Пусть операция, приведшая к срабатыванию триггера, изменяет столбцы a , b и e . Тогда функция columns_updated даст значение 10011. Пусть нас не интересует изменение столбцов b и d , но интересует изменение всех остальных столбцов (a , c и e ), то есть маска будет 10101. Напомним, что на момент написания триггера мы не знаем, какие столбцы затронет та или иная операция изменения или вставки, то есть какой результат даст функция columns_updated . Задав побитовый оператор сравнения во время выполнения, получим 10011 & 10101, что даст в результате 10001, что в десятичном представлении составляет 17. Сравнив это значение с помощью оператора сравнения и битовой маски столбца получим ответ — удовлетворяет ли операция изменения/вставки требуемым условиям. Так, например, если бизнес-логика требует, чтобы триггер сработал при изменении все интересующих нас столбцов (a , c , e ), то, естественно, параметры битовая_маска и битовая_маска_столбца должны иметь одинаковые значения, а оператором сравнения должен быть знак равенства. Таким образом, для нашего примера вся конструкция будет иметь вид:

IF (columns_updated & 17) = 17

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

IF (columns_updated & 17) > 0

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

Создавать триггеры можно и с помощью SQL Server Enterprise Manager.

1. Запустите SQL Server Enterprise Manager.
2. Щелкните правой кнопкой мыши на таблице, для которой хотите создать триггер, и в контекстном меню выберите команду Task > Manage Triggers . В результате этих действий появится диалоговое окно, в котором можно ввести текст триггера и присвоить ему имя.
3. После окончания ввода можно выполнить проверку синтаксиса и нажать кнопку OK для сохранения триггера в базе данных.

Ограничения при создании триггеров

• Оператор CREATE TRIGGER может применяться только в одной таблице.
• Триггер можно создавать только в текущей базе данных, но в нем можно ссылаться на внешние объекты.
• В одном операторе создания триггера можно указывать несколько действий, на которые он будет реагировать.
• В тексте триггера нельзя использовать следующие инструкции: ALTER DATABASE , ALTER PROCEDURE , ALTER TABLE , CREATE DEFAULT , CREATE PROCEDURE , ALTER TRIGGER , ALTER VIEW , CREATE DATABASE , CREATE RULE , CREATE SCHEMA , CREATE TRIGGER , CREATE VIEW , DISK INIT , DISK RESIZE , DROP DATABASE , DROP DEFAULT , DROP PROCEDURE , DROP RULE , DROP TRIGGER , DROP VIEW , RESOTRE DATABASE , RESTORE LOG , RECONFIGURE , UPDATE STATISTICS .
• Любая правильная операция SET работает только в период существования триггера.
• Нельзя выполнить триггер, анализируя в столбцах таблиц INSERTED и DELETED состояние большого двоичного объекта (BLOB ), имеющего тип данных text или image , независимо от того, записывается эта процедура в журнал или нет.
• Не следует применять инструкции SELECT , возвращающие результирующие наборы из триггера, для приложения-клиента, требующего специального управления результирующими наборами, независимо от того, делается это в хранимой процедуре или нет.
• Нельзя создавать INSTEAD OF UPDATE и DELETE триггеры на таблицы, имеющие внешние ключи с установленными опциями каскадного изменения или удаления соответственно.

2. Примеры использования триггеров

Пример 1. Триггеры вставки и обновления

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

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

CREATE TRIGGER Tri_Ins_Sales
ON Sales
FOR INSERT, UPDATE
AS
/* Объявить необходимые локальные переменные */
DECLARE @nDayOfMonth TINYINT
/* Найти информацию о добавленной записи */
SELECT @nDayOfMonth = DatePart (day, i.ord_date)
FROM Sales s, Inserted i
WHERE s.stor_id = i.stor_id
AND s.ord_num = i.ord_num
AND s.title_id = i.title_id
/* Проверить критерий отказа и в случае необходимости
послать сообщение об ошибке */
IF @nDayOfMonth > 15
BEGIN
/* Примечание: всегда сначала производите откат. Вы можете не знать,
какого рода ошибка обработки произошла, что может вызвать
неоправданно продолжительное время блокировки */
ROLLBACK TRAN
RAISERROR("Выполняются только заказы, поданные в первые
15 дней месяца", 16, 10)
END

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

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

Столбцы таблицы Inserted в точности совпадают со столбцами рабочей таблицы. Сравнение можно выполнить по столбцам, как это сделано в данном примере, где для проверки правильности дат продажи сравниваются столбцы таблицы Sales .

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

IF UPDATE(au_lname)
AND (@@ROWCOUNT=1)
BEGIN
…
END

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

Операция UPDATE задействует обе системные таблицы. В таблице Inserted хранятся новые значения, а в таблице Deleted — старые. Поэтому при анализе изменений вы можете использовать обе эти таблицы.

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

NUM_READER = NULL

Пример 2. Триггеры удаления

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

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

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

CREATE TRIGGER Tri_Del_Stores
ON Stores
FOR DELETE
AS
/* Проверка количества модифицируемых строк и запрещение удаления более одной строки за один раз */
IF @@ ROWCOUNT > 1
BEGIN
ROLLBACK TRAN
RAISERROR ("За один раз можно удалить только одну строку.", 16, 10)
END
/* Объявление временной переменной для сохранения уничтожаемой информации */
DECLARE @ StorID char (4)
/* Получение значения удаляемой строки */
SELECT @StorID = d.stor_id
FROM Stores s, Deleted d
WHERE s.stor_id *= d.stor_id
IF EXISTS (SELECT *
FROM Sales
WHERE stor_id = @storID)
BEGIN
ROLLBACK TRAN
RAISERROR ("Эта информация не может быть удалена, поскольку имеется соответствующая запись в таблице Sales.", 16, 10)
END

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

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

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

Пример 3. INSTEAD OF-триггеры

INSTEAD OF-триггеры отличаются от обычных (AFTER ) триггеров тем, что они выполняются не после выполнения операции, приведшей к его срабатыванию, а вместо нее, cо всеми вытекающими последствиями, например, такими как возможность их использования совместно с ограничениями целостности. Системные таблицы Inserted и Deleted используются в них так же, как и в AFTER -триггерах. Тело триггера может дублировать операцию, которая вызвала его срабатывание, но это не обязательное условие. Другими словами, если мы описываем INSTEAD OF DELETE -триггер, то ничто не мешает нам выполнить в нем операцию DELETE , удаляющую все строки, которые должны были быть удалены в соответствии с вызвавшей триггер операцией, но можно этого и не делать.

Приведем пример использования INSTEAD OF -триггера.

Таблица Jobs связана отношением 1:M c таблицей Employees , поэтому невозможно удалить работу, если на нее уже назначены сотрудники. Создадим триггер, который при удалении работы будет проверять, назначены ли на нее сотрудники или нет. Если назначены, то работа не будет удаляться. В связи с тем, что имеется ограничение целостности (DRI ), то работа AFTER -триггера совместно с ним невозможна. То есть можно создать такой триггер:

FOR DELETE
AS
IF EXISTS (SELECT * FROM Employee e JOIN Deleted d ON e.job_id=d.job_id)
BEGIN
ROLLBACK TRAN
END

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

Но можно создать INSTEAD OF -триггер:

CREATE TRIGGER Check_Job ON Jobs
INSTEAD OF DELETE
AS
DELETE FROM Jobs FROM Jobs j JOIN deleted d on d.job_id = j.job_id
WHERE j.job_id NOT IN (SELECT DISTINCT Job_id FROM Employee)

Такой триггер не будет иметь конфликтов с DRI и будет выполняться.

Проверка DRI выполняется сразу при выполнении операции, то есть раньше, чем выполнение AFTER -триггера. При использовании INSTEAD OF -триггера операция по сути не выполняется, а управление передается триггеру, поэтому DRI не будет выполняться.

Как уже было сказано, таблица Inserted содержит добавленные строки, а таблица Deleted — удаленные. Нетрудно догадаться, что при выполнении операции изменения будет использована и таблица Inserted , и таблица Deleted . В этом случае старые значеня окажутся в таблице Deleted , а новые — в таблице Inserted . Объединяя их по ключевому столбцу (столбцам), нетрудно определить, какие значения были изменены.

3. Использование вложенных триггеров

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

Примечание: Уровень вложенности триггера можно проверить в любое время, опросив значение, установленное в переменной @@NESTLEVEL . Оно должно находиться а пределах от 0 до 32.

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

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

Следует отметить, что INSTEAD OF -триггеры по своей природе не подвержены прямой рекурсии.

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

Предположим, что таблица Table_A включает триггер trigger_A , который выполняется, когда происходит обновление Table_A . При выполнении trigger_a вызывает обновление таблицы Table_B . Эта таблица включает в себя триггер trigger_b , который выполняется, когда обновляется Table_B , и вызывает обновление таблицы Table_A . Таким образом, если пользователь обновляет любую из этих двух таблиц, два триггера продолжают бесконечно вызывать выполнение друг друга. При возникновении такой ситуации SQL Server закрывает или отменяет выполнение триггера.

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

/* Первый триггер уничтожает строки в таблице Stores,
если уничтожаются строки таблицы Sales */
CREATE TRIGGER Tri_Del_Sales
ON Sales
FOR DELETE
AS

PRINT "Выполняется триггер удаления для таблицы Sales..."
/* Объявление временной переменной для хранения удаляемой информации */
DECLARE @sStorID char(4),@sMsg varchar(40)
/* Получение значения ID удаляемой строки */

FROM Deleted


/* Удаление строки */
SELECT @sMsg = "Магазин " + @sStorID + " удален"
PRINT @sMsg
DELETE FROM Stores
WHERE stor_id = @sStorID
PRINT "Конец выполнения триггера для таблицы Sales"
GO
/* Второй триггер уничтожает строки одной таблицы,
если уничтожаются строки другой */

ON Stores
FOR DELETE
AS
/* Объявление выполняемого триггера */
PRINT "Выполняется триггер удаления для таблицы Stores..."
/* Объявление временной переменной для хранения информации,
уничтожаемой из таблицы */
DECLARE @sStorID char(4), @sMsg varchar (200)
/* Получение уничтожаемого значения */
SELECT TOP 1 @sStorID = stor_id
FROM Deleted
/* Deleted - это вспомогательная таблица, которую SQL Server
использует для хранения уничтоженных записей */
IF @@ROWCOUNT = 0
BEGIN
PRINT "В таблице Stores нет соответствующих строк"
RETURN
END
/* Удаление записи */
SELECT @sMsg = "Удаление скидок, относящихся к магазину " + @sStorID
PRINT @sMsg
DELETE Discounts
WHERE Stor_id = @sStorID
PRINT "Количество удаленных скидок: " + CONVERT(VARCHAR(4), @@ROWCOUNT)
PRINT "Конец выполнения триггера для таблицы Stores"

Если инструкция DELETE выполняется в таблице Sales , как показано в следующем примере, активизируется триггер, что в свою очередь вызывает выполнение триггера таблицы Stores .

Выполним:

DELETE FROM Sales WHERE stor_id = "8042"

Результат:

Выполняется триггер удаления для таблицы Sales...
Магазин 8042 удален
Выполняется триггер удаления для таблицы Stores...
Удаление скидок, относящихся к магазину 8042
(1 row(s) affected)
Количество удаленных скидок: 1
Конец выполнения триггера для таблицы Stores
(1 row(s) affected)
(4 row(s) affected)
Конец выполнения триггера для таблицы Sales

Обратите внимание на порядок выдаваемых сообщений. Сначала запускается триггер для таблицы Sales . Он удаляет строку из таблицы Stores , запуская таким образом для нее триггер. При этом фактически ни из таблицы Sales , ни из таблицы Stroes удаления еще не произошло (удаление в процессе) — об этом свидетельствует отсутствие автоматического сообщения сервера (N row(s) affected) , которое появляется при удалении из любой таблицы и показывает, сколько строк было удалено.

После запуска триггер на таблицу Stores удаляет связанные строки из таблицы скидок (Discounts ), о чем выдается сообщение (1 row(s) affected) . Затем он выводит соответствующие сообщения и заканчивает свою работу. Как только он закончил свою работу, происходит удаление строки из таблицы Stores , удаление которой и вызвало его работу. Далее, поскольку эта строка удалена, происходит возврат к работе триггера на таблицу Stores . Этот триггер выдает свое последнее сообщение об окончании работы и завершается. Как только он завершился, выводится сообщение (1 row(s) affected) , свидетельствующее об удалении строки из таблицы Stores . И уже только после этого окончательно удаляются строки из таблицы Sales .

Примечание: Триггеры и механизм декларативной ссылочной целостности обычно не могут работать вместе. Например, в предыдущем примере показано, что перед выполнением инструкции DELETE необходимо сначала удалить условие на значение FOREIGN KEY в таблице Discounts . Везде, где это возможно, следует применять либо триггер, либо условие ссылочной целостности. Однако, как уже говорилось, в MS SQL Server 2000 появились INSTEAD OF -триггеры. Их можно использовать совместно с механизмами декларативной целостности, нельзя только использовать при этом каскадные операции в связях на ту же операцию, на которую создан INSTEAD OF -триггер. Например, если создан INSTEAD OF DELETE -триггер, то нельзя в связях, в которых эта таблица является подчиненной, использовать конструкцию ON DELETE CASCADE .

Пример 2

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

CREATE TRIGGER Del_Empl_Tr ON Employee
FOR DELETE
AS
IF EXISTS (SELECT * FROM Employee e
JOIN Deleted d on e.lname = d.lname OR e.Fname = d.fname)
DELETE FROM Employee
FROM Employee e JOIN Deleted d on e.lname = d.lname OR e.Fname = d.fname

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

Если теперь выполнить инструкцию:

DELETE FROM Employee WHERE Fname = "Иван" AND Lname = "Иванов"

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

Заметьте, что тут обязательно надо делать проверку IF EXISTS . Если ее не сделать, то когда дело дойдет до удаления Петра Васильева, будет выполнена инструкция DELETE и, хотя она фактически никого не удалит, вновь вызванный триггер опять вызовет самого себя (опять никого фактически не удаляя) и т. д., до превышения максимального уровня вложенности — 32. После достижения уровня вложенности 32 произойдет ошибка и все действия будут отменены.

Пример 3. Косвенная рекурсия

Изменим пример 1 таким образом, чтобы, если удаляется строка из таблицы Sales , то удалялся бы и магазин, в котором была сделана удаляемая продажа. Поскольку отношение между этими таблицами 1:M, то в удаляемом магазине может быть множество продаж, а не только та, которую мы пытаемся удалять. Поэтому цепочка должна быть следующая: удаляем продажу → удаляется магазин, в котором она была сделана, → удаляются все остальные продажи, сделанные в этом магазине, → удаляются все скидки, привязанные к этому магазину. Кроме того, реализуем эти триггеры в виде INSTEAD OF -триггеров, чтобы не было необходимости разрывать связи между таблицами.

CREATE TRIGGER Tri_Del_Sales
ON Sales
INSTEAD OF DELETE
AS
DELETE FROM Sales FROM Sales s JOIN Deleted d on d.ord_num = s.ord_num
IF EXISTS (SELECT * FROM Stores s JOIN Deleted d ON d.stor_id = s.stor_id)
DELETE FROM Stores FROM Stores s JOIN Deleted d ON d.stor_id = s.stor_id
GO

CREATE TRIGGER Tri_Del_Stores
ON Stores
INSTEAD OF DELETE
AS
DELETE FROM Discounts FROM Discounts di JOIN Deleted de on di.stor_id=de.stor_id
IF EXISTS(SELECT * FROM Sales s JOIN Deleted d on d.stor_id = s.stor_id)
DELETE FROM Sales FROM Sales s JOIN Deleted d on d.stor_id = s.stor_id
DELETE FROM Stores FROM Stores s JOIN Deleted d on d.stor_id = s.stor_id

Для проверки можно выполнить команду:

DELETE FROM Sales WHERE ord_num = "P723"

В результате из таблицы Sales будет удалена не только строка с кодом заказа "P723", но и три другие строки, относящиеся к тому же магазину (код 8042). Также будет удален сам магазин 8042 и относящаяся к нему скидка.

В приведенном примере, кроме всего прочего, удалены все выводы сообщений и изменены вызовы операторов DELETE — поскольку выводов сообщений нет, то нет и необходимости формировать значение локальной переменной @sStroID . Использование этой переменной в операторе DELETE несколько ограничивало применимость триггеров. Так, триггеры в примере 2 были рассчитаны на то, что будут удаляться записи только для одного магазина, и при удалении записей, относящихся сразу к нескольким магазинам, работали некорректно. Теперь же такого ограничения нет, поскольку удаляются все записи, связанные с записями в таблице Deleted (то есть со всеми фактически удаляемыми строками).

Можно задать вопрос: зачем использовать рекурсию? Не проще ли было бы при удалении из таблицы Sales удалять в триггере на нее все записи из самой себя, относящиеся к тому же магазину, что и удаляемая строка продажи, после этого удалять строку из таблицы Stores , а в триггере на таблицу Stores удалять связанные записи только из таблицы Discounts ? Да, так можно сделать, но только в том случае, если мы всегда будем давать команду удаления именно из таблицы Sales (как это было сделано выше при проведении проверки). Однако мы можем дать команду удаления и из таблицы Stores , например так:

DELETE FROM stores WHERE stor_id = 8042

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

Замечание 1: Чтобы уже созданные в предыдущих примерах триггеры не мешались, надо удалить их с помощью инструкции DROP TRIGGER имя_триггера .

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

Пример 4

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

CREATE TRIGGER trig_del_l ON Authors FOR DELETE AS
PRINT "Триггер удаления №1"
GO

CREATE TRIGGER trig_del_2 ON Authors FOR DELETE AS
PRINT "Триггер удаления №2"
GO

CREATE TRIGGER trig_upd_l ON Authors FOR UPDATE AS
PRINT "Триггер обновления №1"
GO

CREATE TRIGGER trig_upd_3 ON Authors FOR UPDATE AS
PRINT "Триггер обновления №3" "
GO

CREATE TRIGGER trig_upd_2 ON Authors FOR UPDATE AS
PRINT "Триггер обновления №2"
GO

А теперь попробуем изменить какую-либо запись в таблице:

UPDATE Authors
SET au_fname = "Юрий" WHERE au_lname = "Тихомиров";

Cработают все три триггера обновления:

Триггер обновления №1

Триггер обновления №3

Триггер обновления №2

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

Триггер обновления №1

Триггер обновления №2

Триггер обновления №3

Множественные триггеры достаточно активно используются при репликации.

4. Отображение информации о триггере и изменение триггера

Для выяснения назначения триггера таблицы необходимо отобразить информацию, описывающую любой триггер, которым владеет таблица. Существует несколько путей получения информации о триггере конкретной таблицы. Одним из них является SQL Server Enterprise Manager, другим — системные процедуры sp_help и sp_depends . Для того чтобы посмотреть текст триггера через Enterprise Manager, выполните следующие действия:

1. В Enterprise Manager выберите сервер и базу данных, с которой вы хотите работать.
2. Откройте таблицу в режиме проектирования командой Design Table и в ее окне нажмите кнопку Triggers на панели инструментов.
3. Появится диалоговое окно создания триггера, где можно посмотреть текст любого из установленных триггеров.

Системные хранимые процедуры sp_help и sp_depends уже были описаны в теме «Хранимые процедуры».

Для того чтобы изменить функциональность триггера, можно либо удалить его и создать новый с соответствующими изменениями, либо изменить уже существующий. Для того чтобы изменить существующий триггер в T-SQL существует команда ALTER TRIGGER . Ее синтаксис аналогичен синтаксису команды CREATE TRIGGER , за исключением ключевого слова ALTER вместо CREATE .

Можно также изменить триггер с помощью Enterprise Manager. Для этого после входа в Enterprise Manager надо просто внести изменения и применить их.

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

Иногда нужно удалить триггеры из таблицы или таблиц. Например, при перемещении приложения в рабочую среду может потребоваться удаление триггеров, обеспечивавших высокое качество обработки, но сильно уменьшавших производительность. Можно просто удалить триггеры для замены их на более новую версию. Для удаления триггера применяется инструкция DROP TRIGGER :

DROP TRIGGER [владелец.]имя_ триггера [,… n]

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

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

DROP TRIGGER Tri_Del_Authors

6. Приостановка и возобновление работы триггеров

Часто бывает необходимо отключить на некоторое время работу триггера без его фактического удаления. Этого можно достигнуть используя конструкцию ALTER TABLE <имя_таблицы> DISABLE TRIGGER <имя триггера> — для отключения триггера и ALTER TABLE <имя_таблицы> ENABLE TRIGGER <имя триггера> — для возобновления его работы.

Задания для самостоятельной работы

Перед началом выполнения заданий напомним, что триггеры — это системные хранимые процедуры, которые связаны с конкретной таблицей. Для вызова редактора триггеров необходимо выделить таблицу, по правой кнопке контекстного меню перейти в раздел Все задачи > Manage triggers , и вы попадаете в редактор триггеров (рис. 1).

Рис. 1. Начальное состояние редактора триггеров при создании нового триггера

Задание 1. Разработать триггер, который удалял бы запись о книге в том случае, если удаляется последний экземпляр данной книги. Для какой таблицы вы будете писать этот триггер? При написании триггера помните, что с таблицей «Книги» у нас связаны таблицы «Авторы» и «Системный каталог». Однако они связаны отношением «многие-ко-многим», для чего используются связующие таблицы. Удалить данные о книге нельзя, если на нее есть ссылки в этих связующих таблицах. Предусмотрите предварительное удаление данных из связующих таблиц. Проверьте работу данного триггера.

Задание 2. Разработать триггер, который не позволял бы удалить экземпляр книги, если этот экземпляр в данный момент находится на руках у читателя. Для отмены команды удаления применить команду отката транзакций ROLLBACK .

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

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

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

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

Задание 4. Разработать триггер, который добавлял бы один экземпляр при вводе новой книги. Действительно, мы определили, что книги у нас в каталоге присутствуют только в том случае, если они есть в нашей библиотеке, поэтому при вводе новой книги в таблицу «Экземпляр» должен добавляться один экземпляр данной книги.

Задание 5. Разработать триггер типа INSTEAD OF для таблицы «Читатели». Данный триггер должен проверять, есть ли информация хотя бы об одном из телефонов для оперативной связи с читателем, и если такой информации нет, то не вводить данные о читателе.

Задание 6. Разработать триггер, который при изменении значения поля, символизирующего присутствие экземпляра книги в библиотеке, например YES_NO , с "1" на "0" автоматически заменял бы значения в полях «Дата выдачи», «Дата возврата» и «Номер читательского билета» на неопределенное.

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

Задание 8. Разработать триггер, который при удалении экземпляра книги проверял бы, сколько экземпляров данной книги осталось в библиотеке, и если остался только один экземпляр, то повышал бы стоимость данной книги на 15 % как редкой и ценной.

Версия для печати