Что такое триггеры в sql

Что такое триггеры в sql

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

Синтаксис

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

Описание

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

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

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

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

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

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

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

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

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

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

Когда указывается REFERENCING , для триггера собираются переходные отношения, представляющие собой множества строк, включающие все строки, которые были добавлены, удалены или изменены текущим оператором SQL. Это позволяет триггеру наблюдать общую картину того, что сделал оператор, а не только одну строку за другой. Это указание допускается только для триггера AFTER , не являющегося триггером ограничения; кроме того, если это триггер для UPDATE , у него должен отсутствовать список имён_столбцов . Указание OLD TABLE может быть задано только один раз и только для триггера, который может срабатывать при UPDATE или DELETE ; оно создаёт переходное отношение, содержащее образы-до-изменения всех строк, модифицированных или удалённых оператором. Указание NEW TABLE , подобным образом, может быть задано только единожды и только для триггера, который может срабатывать для UPDATE или INSERT ; оно создаёт переходное отношение, содержащее образы-после-изменения всех строк, модифицированных или добавленных оператором.

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

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

Параметры

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

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

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

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

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

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

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

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

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

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

Это ключевое слово непосредственно предшествует объявлению одного или двух имён, по которым можно будет обращаться к переходным отношениями, образуемым при выполнении целевого оператора. OLD TABLE
NEW TABLE

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

Имя (неполное, без схемы), которое будет использоваться в триггере для обращения к этому переходному отношению. FOR EACH ROW
FOR EACH STATEMENT

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

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

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

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

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

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

В синтаксисе CREATE TRIGGER ключевые слова FUNCTION и PROCEDURE равнозначны, но указываемая триггерная функция должна в любом случае быть функцией, а не процедурой. Ключевое слово PROCEDURE здесь поддерживается по историческим причинам и считается устаревшим. аргументы

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

Замечания

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

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

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

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

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

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

В некоторых случаях одна команда SQL может вызывать сразу нескольких видов триггеров. Например, INSERT с предложением ON CONFLICT DO UPDATE может выполнять операции как добавления, так и изменения, так что она при необходимости будет вызывать триггеры обоих видов. При этом переходные отношения, предоставляемые триггерам, будут разными в зависимости от типа события; то есть триггер INSERT будет видеть только добавленные строки, а триггер UPDATE — только изменённые.

Изменения или удаления строк, вызванные действиями по обеспечению целостности внешнего ключа, например, ON UPDATE CASCADE или ON DELETE SET NULL , считаются частью SQL-команды, вызвавшей эти действия (заметьте, что такие действия не могут быть отложенными). В затрагиваемой таблице будут вызваны соответствующие триггеры, и таким образом появляется возможность вызова триггеров для SQL-команды, не соответствующей непосредственно их типу. В простых ситуациях триггеры, запрашивающие переходные отношения, будут видеть все изменения, произведённые в их таблице одной исходной командой SQL, в виде одного переходного отношения. Однако возможны случаи, в которых присутствие триггера AFTER ROW , запрашивающего переходные отношения, приведёт к тому, что операции для обеспечения целостности внешнего ключа, вызванные одной SQL-командой, будут разделены на несколько этапов, и на каждом будут свои переходные отношения. В таких случаях все существующие триггеры уровня оператора будут срабатывать единожды при создании переходного отношения, что гарантирует, что эти триггеры будут видеть каждую обрабатываемую строку в переходном отношении один и только один раз.

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

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

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

Примеры

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

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

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

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

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

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

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

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

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

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

Выполнение функции check_transfer_balances_to_zero для каждого оператора, проверяющей, что строки transfer в совокупности дают нулевой баланс:

CREATE TRIGGER transfer_insert AFTER INSERT ON transfer REFERENCING NEW TABLE AS inserted FOR EACH STATEMENT EXECUTE FUNCTION check_transfer_balances_to_zero();

Выполнение функции check_matching_pairs для каждой строки, проверяющей, что соответствующие пары пунктов изменены синхронно (одним оператором):

CREATE TRIGGER paired_items_update AFTER UPDATE ON paired_items REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab FOR EACH ROW EXECUTE FUNCTION check_matching_pairs();

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

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

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

Тогда как имена переходных таблиц для триггеров AFTER задаются предложением REFERENCING стандартным образом, переменные строк, применяемые в триггерах FOR EACH ROW нельзя объявлять в предложении REFERENCING . Порядок обращения к таким строкам зависит от языка, на котором написана триггерная функция, но для каждого языка он вполне определённый. Некоторые языки по сути действуют так, как будто в команде присутствует предложение REFERENCING с указанием OLD ROW AS OLD NEW ROW AS NEW .

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

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

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

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

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

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

См. также

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

Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Тема 13: Триггеры в SQL на примере базы данных SQLite

• 15.07.2016
• SQLite библиотека, Базы данных
• Один комментарий

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Тем в рубрике SQLite осталось не так уж и много. Вернее про SQLite можно писать очень много, долго и упорно, и всё равно часть вопросов останется не освещенной и освещенной не в полной мере. Под словосочетанием «тем осталось немного» я понимаю следующее: мы практически закончили изучать реализацию SQL в библиотеки SQLite. Задачу, которую я сам перед собой поставил, можно озвучить следующим образом: дать начинающему разработчику максимально понятное и подробное представление о языке SQL, а в качестве примера используется библиотека SQLite. В данной теме мы поговорим о том, что собой представляют триггеры в SQL на примере базы данных под управлением SQLite. Тему триггеров я не стал делить на части, поэтому запись получилось довольно объемной(более 4300 слов, поэтому пользуйтесь постраничной навигацией).

Триггеры в SQL на примере базы данных SQLite

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

1. Сначала мы поговорим о назначении триггеров в SQL и реляционных базах данных, попутно рассмотрев синтаксис триггеров в SQLite.
2. Затем мы поговорим о том, как срабатывают триггеры в базах данных: до возникновения события (триггер BEFORE) и после возникновения события (триггер AFTER) и параллельно разберемся в чем между ними разница.
3. Далее мы опишем триггеры по событиям, на которые они срабатывают. Событий у нас всего три, так как триггеры в SQLite срабатывают только на операции, которые тем или иным образом изменяют данные в таблицы: UPDATE, INSERT, DELETE.
4. Далее мы рассмотрим, как составить уточняющее выражение WHEN для триггера.
5. Рассмотрим особенности INSTEAD OF триггера, который позволяет реализовать команды манипуляции данными для представлений, отметим, что в SQLite представления нельзя редактировать, об этом мы поговорим в следующей теме.
6. Также мы поговорим про устранение конфликтов и обеспечение целостности данных при помощи триггеров и специальной функции RAISE.
7. И в завершении публикации вы узнаете о том, как получить информацию о триггерах/списков триггеров в базах данных SQLite3.Рассмотрим явное и неявное удаление триггеров из базы данных SQLite. И разберемся с некоторыми особенностями работы временных триггеров в SQLite.

Что такое триггер в контексте SQL? Использование триггеров в базах данных SQLite

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

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

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

Для любой СУБД триггер – это в первую очередь объект базы данных, поэтому имя триггера должно быть уникальным во всей базе данных, SQLite в этом плане не исключение. У триггеров в SQL есть момент запуска. Момент запуска триггера можно разделить на два вида: BEFORE и AFTER. Момент запуска триггера AFTER говорит о том, что триггер будет запущен после выполнения какого-либо события в базе данных. Соответственно, момент запуска триггера BEFORE говорит о том, что триггер будет запущен до выполнения события в базе данных.

Мы еще поговорим про представления или VIEW в SQL, и вы узнаете, что SQLite позволяет только читать данные из VIEW, в отличии, скажем, от MySQL или Oracle. Триггеры могут быть назначены для представлений с целью расширить набор операций манипуляции данными того или иного представления. Такой вид триггеров получил название INSTEAD OF триггер.

Итак, триггеры можно разделить на три вида по их применению:

• триггер BEFORE, который срабатывает до выполнения какого-либо события в базе данных;
• триггер AFTER, который срабатывает после выполнения события в базе данных;
• INSTEAD OF триггер, который используется для манипуляции данными представлений.

Так же мы можем разделить триггеры по типам SQL команд:

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

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

Давайте перечислим самые распространенные функции триггеров:

1. Функция журнализации. Часто при помощи триггеров разработчики создают таблицы-журналы, в которых фиксируются различные изменения в базе данных. Обычно журналы создаются для фиксации изменений, которые вносят различные пользователи базы данных, таким образом можно отследить какой пользователь внес то или иное изменение в ту или иную таблицу базы данных.
2. Функция согласования данных. Мы уже упоминали, что триггеры используются для обеспечения целостности данных в базе данных. Мы можем связать триггер с той или иной SQL командой, таким образом, чтобы триггер проверял связанные таблицы на согласованность данных, тем самым мы обезопасим данные.
3. Функция очистки данных. Данная функция является подмножество функции из второго пункта. Например, вы выполняете каскадное удаление данных, в этом случае данные удаляются из таблиц, связанных ограничением внешнего ключа, но что если данные об одном объекте хранятся в несвязанных таблицах? В этом случае нас спасают триггеры. То же самое можно сказать и про операции каскадной модификации данных.
4. Другие функции триггеров. К сожалению, в SQLite3 нет хранимых процедур за исключением триггеров. В тех СУБД, у которых реализованы хранимые процедуры, мы можем создавать собственные процедуры в теле триггера, которые могут выполнять операции, не связанные с изменением данных.

Давайте приступим к рассмотрению триггеров на примере библиотеки SQLite.

SQL синтаксис триггеров в базах данных SQLite

Здесь мы коротко рассмотрим SQL синтаксис триггеров, реализованный в реляционных базах данных под управлением библиотеки SQLite3. Ранее мы уже говорили о том, как создать триггер, когда разбирались с командой CREATE в SQLite (у нас был раздел CREATE TRIGGER) и мы рассматривали, как удалить триггер, когда разбирались с особенностями команды DROP в SQLite3 (раздел DROP TRIGGER). Давайте повторим и дополним уже имеющуюся информацию о триггерах. Общий SQL синтаксис создания триггеров в SQLite вы можете увидеть на рисунке ниже.

Общий синтаксис создания триггера в базе данных под управлением SQLite3

Мы видим, что операция по созданию триггера начинается с команды CREATE, как и операция создания таблицы в базе данных, это обусловлено тем, что триггер, как и таблица, является объектом базы данных.

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

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

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

Далее мы указываем как мы хотим, чтобы триггер работал: для VIEW – INSTEAD OF, перед выполнением SQL команды – BEFORE, после выполнения SQL операции – AFTER. После чего мы связываем триггер с той или иной командой. Обратите внимание: для всех трех команд манипуляции данными обязательным является указание таблицы или представления, для которых триггер создается, а вот для команды UPDATE можно указать еще и столбец, который будет отслеживать триггер.

Обратите внимание: мы можем создавать строковые триггеры при помощи конструкции FOR EACH ROW. Обычно триггеры создаются для какой-нибудь команды и, соответственно, выполняются по событию DELETE, UPDATE или INSERT, но мы можем сделать так, чтобы код триггера вызывался после изменения каждой строки таблицы при помощи конструкции FOR EACH ROW.

Так же стоит отметить, что выше мы говорили не совсем правду в контексте SQLite3. Многие СУБД поддерживают две разновидности триггеров: табличные и строчные. Строчные триггеры создаются при помощи конструкции FOR EACH ROW, но в SQLite нет табличных триггеров, поэтому даже если вы не укажите FOR EACH ROW явно, SQLite будет считать триггер строчным.

Также вы можете использовать уточняющую фразу WHEN, с которой мы разберемся на примере ниже. После того, как вы описали триггер, вы можете задать SQL команды, которые будут выполняться по тому или иному событию, другими словами – создать тело триггера. В теле триггера, создаваемого в базе данных SQLite, можно использовать четыре команды манипуляции данными: INSERT, UPDATE, SELECT, DELETE. Команды определения данных, команды определения доступа к данным и команды управления транзакциями в теле триггера SQLite не предусмотрены. Но нам стоит заметить,что триггеры, выполняя команды и отлавливая события сами работают так, как будто это транзакция.

Обратим внимание на то, что перечисленные команды в теле триггера поддерживают свой практически полный синтаксис. Например, вы можете составить сколь угодно сложный SQL запрос SELECT, в котором будете объединять таблицы или объединять результаты запросов. Чтобы сообщить SQLite, что тело триггера закончилось, используйте ключевое слово END.

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

Синтаксис удаления триггеров из базы данных SQLite

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

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

SQL событие BEFORE: выполнение триггера перед запросом

Итак, не забываем, что триггер создается для какой-либо конкретной таблицы и отслеживает события, происходящие с таблицей, для которой он создан. В SQLite нет табличных триггеров, а есть только триггеры строчные, то есть FOR EACH ROW триггеры, которые срабатывают при изменении каждой строки в таблице.

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

CREATE TRIGGER

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

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

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

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

Кроме того, триггеры могут быть определены для операции TRUNCATE , но только с указанием FOR EACH STATEMENT.

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

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

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

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

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

Если указан параметр REFERENCING, для триггера собираются переходные отношения, которые представляют собой наборы строк, включающие все строки, которые были добавлены, удалены или изменены текущим оператором SQL. Эта функциональность позволяет триггеру иметь глобальное представление о том, что сделал оператор, а не только об одной строке за раз. Это указание разрешено только для триггера с режимом AFTER, который не является триггером ограничения; также, если такой триггер является триггером для операций UPDATE , у него должен отсутствовать список имен_столбцов. Указание OLD TABLE может быть задано только один раз и только для триггера, который срабатывает для операций UPDATE или DELETE ; это указание создает переходное отношение, которое содержит образы до изменения всех строк, обновленных или удаленных оператором. Аналогично указание NEW TABLE может быть задано только один раз и только для триггера, который срабатывает для операций UPDATE или INSERT ; это указание создает переходное отношение, которое содержит образы после изменения всех строк, обновленных или добавленных оператором.

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

Дополнительную информацию о триггерах см. в главе Триггеры.

Параметры

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

BEFORE
AFTER
INSTEAD OF

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

событие

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

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

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

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

Для событий INSTEAD OF UPDATE не допускается использование списка столбцов. Список столбцов также не может быть указан при запросе переходных отношений.

имя_таблицы

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

имя_ссылающейся_таблицы

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

DEFERRABLE
NOT DEFERRABLE
INITIALLY IMMEDIATE
INITIALLY DEFERRED

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

REFERENCING

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

OLD TABLE NEW TABLE

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

имя_переходного_отношения

Имя (без указания схемы), которое будет использоваться в триггере для этого переходного отношения.

FOR EACH ROW FOR EACH STATEMENT

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

условие

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

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

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

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

имя_функции

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

В синтаксисе команды CREATE TRIGGER ключевые слова FUNCTION и PROCEDURE равнозначны, но указанная функция должна в любом случае быть функцией, а не процедурой. Ключевое слово PROCEDURE оставлено по историческим причинам и является устаревшим.

аргументы

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

Примечания

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

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

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

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

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

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

В некоторых случаях одна команда SQL может запустить несколько видов триггеров. Например, команда INSERT с предложением ON CONFLICT DO UPDATE может вызвать как операции добавления, так и операции изменения, поэтому она будет запускать оба типа триггеров по мере необходимости. Отношения перехода, предоставляемые триггерам, являются специфичными для их типа событий; таким образом, триггер INSERT будет видеть только добавленные строки, в то время как триггер UPDATE будет видеть только измененные строки.

Изменения или удаления строк, вызванные принудительными действиями внешнего ключа, такими как ON UPDATE CASCADE или ON DELETE SET NULL, рассматриваются как часть команды SQL, которая вызвала их (обратите внимание, что такие действия никогда не откладываются). В затрагиваемой таблице будут запущены соответствующие триггеры, так что это дает команде SQL еще один способ запускать триггеры, не вполне соответствующие их типу. В простых случаях триггеры, которые запрашивают отношения перехода, будут видеть все изменения, сделанные в их таблице одной исходной командой SQL, в виде одного отношения перехода. Однако существуют случаи, в которых наличие триггера AFTER ROW, который запрашивает отношения перехода, приведет к тому, что действия принудительного применения внешнего ключа, инициированные одной командой SQL, будут разделены на несколько этапов, каждый со своим(и) собственным(ми) отношением(ями) перехода. В таких случаях любые имеющиеся триггеры уровня оператора будут вызваны один раз при создании отношения перехода, гарантируя, что триггеры будут видеть каждую затронутую строку в отношения перехода один и только один раз.

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

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

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

Примеры

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

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

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

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

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

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

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

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

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

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

Выполнение функции check_transfer_balances_to_zero для каждого оператора, чтобы убедиться, что строки таблицы transfer в совокупности дают нулевой баланс:

CREATE TRIGGER transfer_insert AFTER INSERT ON transfer REFERENCING NEW TABLE AS inserted FOR EACH STATEMENT EXECUTE FUNCTION check_transfer_balances_to_zero(); 

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

CREATE TRIGGER paired_items_update AFTER UPDATE ON paired_items REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab FOR EACH ROW EXECUTE FUNCTION check_matching_pairs(); 

Раздел Полный пример запуска содержит полный пример триггерной функции, написанной на языке C.

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

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

• В то время как имена переходных таблиц для триггеров AFTER задаются с помощью предложения REFERENCING стандартным образом, переменные строк, используемые в триггерах FOR EACH ROW нельзя указывать в предложении REFERENCING. Порядок обращения к таким строкам зависит от языка, на котором написана функция триггера, но для каждого языка он вполне определенный. Некоторые языки действуют так, как будто предложение REFERENCING присутствует в команде, и содержит указание OLD ROW AS OLD NEW ROW AS NEW.
• Стандарт позволяет использовать переходные таблицы со специфичными для столбцов триггерами UPDATE, но тогда набор строк, которые должны быть видны в переходных таблицах, должен зависеть от списка целевых столбцов триггера. В настоящее время в QHB это не реализовано.
• QHB разрешает задавать в качестве действия триггера только пользовательскую функцию. Стандарт же позволяет выполнять в качестве действия триггера ряд других команд SQL, таких как CREATE TABLE . Это ограничение нетрудно обойти, создав пользовательскую функцию, которая выполняет нужные команды.

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

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

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

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

Вариант команды CREATE CONSTRAINT TRIGGER является расширением стандарта SQL, реализованным в QHB.

Что такое триггеры в sql

Издание: Microsoft SQL Server 2000. Для профессионалов

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

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

Триггер (trigger) SQL Server 2000 — это специальный тип хранимых процедур, запускаемых сервером автоматически при выполнении тех или иных действий с данными таблицы. Каждый триггер привязывается к конкретной таблице. Когда пользователь пытается, например, изменить данные в таблице, сервер автоматически запускает триггер и, если он завершается успешно, разрешается выполнение изменений. Все производимые триггером модификации данных рассматриваются как одна транзакция. В случае обнаружения ошибки или нарушении целостности данных происходит откат этой транзакции. Тем самым внесение изменений запрещается. Отменяются также все изменения, уже сделанные триггером.
Область применения триггеров достаточно широка. Например, при репликации сведением триггеры используются для отслеживания всех выполняемых в таблице изменений. Триггеры собирают информацию о производимых изменениях и сохраняют ее в системных таблицах поддержки репликации. Триггеры также позволяют создавать сложные значения по умолчанию, вычисляя их с помощью данных нескольких столбцов таблиц и функций Transact-SQL. Другим примером использования триггеров является обеспечение нестандартной целостности ссылок, поддержание которой обычными средствами SQL Server невозможно. Кроме того, с помощью триггеров можно выполнять каскадные изменения в нескольких связанных таблицах. Например, в таблице titles базы данных pubs можно определить триггер DELETE, который будет удалять связанные строки в таблицах titleauthor, sales и roysched. Из этих таблиц будут удалены все строки, в которых значения столбца title_id совпадает со значением аналогичного столбца в удаляемой строке таблицы titles.

ПРИМЕЧАНИЕ Если триггер нельзя определить для представления, то для выполнения каких-либо действий в ответ на изменение данных через представление следует определить триггер для таблиц, на основе которых создано представление.

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

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

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

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

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

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

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

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

• trigger_name. Этот аргумент задает имя триггера, под которым он будет опознаваться хранимыми процедурами и командами Transact-SQL. Имя триггера должно быть уникальным в пределах базы данных. Дополнительно к имени триггера можно указать имя владельца.
• table | view. Имя таблицы (или представления) базы данных, к которой будет привязан триггер.

• WITH ENCRYPTION. При указании этого аргумента сервер выполняет шифрование кода триггера, чтобы никто, включая администратора, не мог получить к нему доступ и прочитать его. Шифрование часто используется для скрытия авторских алгоритмов обработки данных, являющихся интеллектуальной собственностью программиста или коммерческой тайной.
• AFTER. При указании этого аргумента триггер будет запускаться только после того, когда все вызвавшие триггер команды будут успешно выполнены. Этот аргумент используется по умолчанию.
• INSTEAD OF. При указании этого аргумента триггер будет заменять собой выполнение соответствующих запросов пользователей, приведших к вызову триггера. Собственно пользовательские запросы выполняться не будут.
• [DELETE] [,] [INSERT] [,] [UPDATE]. Эта конструкция определяет, на какие команды будет реагировать триггер. При создании триггера должно быть указано хотя бы одно из этих ключевых слов. Допускается создание триггера, реагирующего на две или три команды.
• WITH APPEND. Задание этого аргумента требуется только в том случае, если для базы данных установлен уровень совместимости 6.5 или ниже. Так как до версии SQL Server 7.0 для таблицы было разрешено создание не более одного триггера каждого типа (INSERT, UPDATE или DELETE), то по умолчанию создание нового триггера будет приводить к удалению ранее созданного. Аргумент WITH APPEND позволяет создавать несколько триггеров каждого типа для базы данных с уровнем совместимости 6.5 или ниже. Для баз данных с уровнем совместимости 7.0 использование этого аргумента не обязательно, так как в последней версии реализована автоматическая поддержка до 16 триггеров каждого типа.
• NOT FOR REPLICATION. При создании триггера с этим аргументом запрещается его запуск при выполнении модификации таблиц механизмами репликации.
• AS sql_statement [. n]. Эта конструкция определяет набор команд Transact-SQL, которые будут выполнены при запуске триггера. Общие рекомендации по написанию кода триггера будут даны далее в этой главе.
• FOR [INSERT] [,] [UPDATE]. Определяет команду, при выполнении которой будет запускаться триггер. Возможно связывание триггера с несколькими командами.
• IF UPDATE (column). Использование этого аргумента позволяет выполнять триггер при модификации конкретного столбца таблицы. Применяется только для команд INSERT или UPDATE, но не для команды DELETE. Имя таблицы определяется аргументом table, поэтому указание имени таблицы при определении имени столбца не требуется.
• UPDATE (column). Эта конструкция применяется совместно с предыдущим аргументом, если необходимо выполнить запуск триггера при модификации нескольких столбцов. Параметр column задает имя столбца, при модификации которой будет производиться запуск триггера. Ключевое слово AND предписывает запускать триггер только в том случае, если были модифицированы оба столбца (указанные в этой и в предыдущей конструкциях). При использовании ключевого слова OR триггер будет выполнен при изменениях в любом из столбцов.
• [. n]. Этот аргумент указывает, что допускается использование нескольких конструкций < AND | OR>UPDATE (column).
• IF (COLUMNS_UPDATED()). С помощью этой конструкции можно узнать, какие столбцы таблицы были изменены (UPDATE) или добавлены (INSERT). Использование этой конструкции допускается только для команд INSERT и UPDATE. Функция COLUMNS_UPDATED() может быть вызвана в любом месте внутри триггера и возвращает двоичное число, каждый бит которого соответствует конкретному столбцу таблицы. Если бит установлен в 1, то соответствующий столбец был изменен. Младший бит соответствует первому столбцу таблицы, второй бит справа соответствует второму столбцу и т. д.
• bitwise_operator. Этот аргумент задает оператор побитовой обработки, с помощью которого можно определить, изменялся конкретный столбец или нет. Например, можно использовать оператор & (битовый AND) для определения того, был ли изменен конкретный столбец. Оператор побитовой обработки выполняется для операндов COLUMNS_UPDATED() и updated_bitmask.
• updated_bitmask. Этот аргумент задает битовую маску для определения изменения в одном или нескольких столбцах. Каждый столбец представлен отдельным битом. Младший бит соответствует первому столбцу таблицы, второй бит — второму столбцу и т. д. Например, если в таблице имеется 7 столбцов, то значение updated_bitmask для проверки изменения столбцов 2, 3 и 6 должно быть равно 38 (двоичное значение 0100110). Для проверки изменения столбцов 4, 5, 6 и 7 значение updated_bitmask будет равно 120 (двоичное значение 1111000).
• comparison_operator. Оператор сравнения, предназначенный для проверки значения, возвращенного в результате выполнения побитовой операции сравнения на соответствие установленным критериям. Чаще всего используется оператор =, хотя допускается использование любых операторов ( , !=).
• column_bitmask. Битовая маска, определяющая, были ли действительно изменены проверяемые столбцы.

Модификация триггера

SQL Server 2000 не поддерживает непосредственное изменение триггеров. При модификации сервер сначала удаляет ранее созданный триггер, а затем создает новый с таким же именем и привязанный к той же таблице, что и старый. Средствами Enterprise Manager можно выполнять редактирование кода триггера, но алгоритм изменения остается тот же — сначала удаляется старый триггер, а затем создается новый. Enterprise Manager получает с помощью хранимых процедур и команд Transact-SQL всю информацию о параметрах триггера и предоставляет ее с помощью графического интерфейса в понятном виде. Получение информации о параметрах и SQL-коде триггера будет рассмотрено позже в этой главе.
Для изменения триггера используется команда ALTER TRIGGER, имеющая следующий синтаксис:
ALTER TRIGGER trigger_name
ON ( table | view )
[ WITH ENCRYPTION ]
<
< ( FOR | AFTER | INSTEAD OF ) < [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] >
[ NOT FOR REPLICATION ]
AS
sql_statement [ . n ]
>
|
< ( FOR | AFTER | INSTEAD OF ) < [ INSERT ] [ , ] [ UPDATE ] >
[ NOT FOR REPLICATION ]
AS
< IF UPDATE ( column )
[ < AND | OR >UPDATE ( column ) ]
[ . n ]
| IF ( COLUMNS_UPDATED ( ) < bitwise_operator >updated_bitmask )
< comparison_operator >column_bitmask [ . n ]
>
sql_statement [ . n ]
>
>
Перед тем как выполнять команду ALTER TRIGGER, следует убедиться в существовании в таблице table триггера trigger_name. Аргументы команды ALTER TRIGGER были рассмотрены при описании команды CREATE TRIGGER.

Удаление триггера

Для удаления триггера используется команда DROP TRIGGER со следующим синтаксисом:
DROP TRIGGER < trigger >[ . n ]
Единственный аргумент trigger задает имя триггера, который необходимо удалить. Так как имя триггера уникально в пределах базы данных, то указание имени таблицы не требуется. С помощью одной команды DROP TRIGGER можно удалить несколько триггеров, перечислив их имена через запятую.

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

Внутри триггера допускается использование любых команд Transact-SQL, за исключением перечисленных в предыдущем разделе. Также допускается вызов хранимых процедур, включая системные.
Хорошим тоном при написании триггера является отказ от возвращения триггером каких-либо результатов, хотя это и не запрещается. Не рекомендуется использование команд SELECT, возвращающих какой-либо результат. Тем не менее, при выполнении определенной группы команд сервер выдает сообщение об успешном или неудачном завершении команды. Для подавления этих сообщений можно использовать команду SET NOCOUNT. Если триггер возвращает какой-либо набор результатов, то он должен быть обработан каждым приложением индивидуально.
При написании триггера следует свести к минимуму обращения к внешним таблицам и другим объектам базы данных. Если пользователь удалит объект, на который ссылался триггер, то при следующем запуске триггера сервер выдаст сообщение об ошибке. SQL Server не отслеживает автоматически связь триггера с внешними объектами и не запрещает их удаление. Тем не менее, если после удаления объекта в базе данных был создан новый объект с таким же именем, то триггер будет ссылаться на новый объект. Если структура нового и старого объектов одинакова, то работа триггера не нарушится. Если же в структуру нового объекта внесены существенные изменения, то триггер необходимо соответствующим образом модифицировать.
Внутри триггера можно использовать любые команды группы SET. Установленное значение действует на все время выполнения триггера и снимается после его завершения, когда сервер восстанавливает все настройки конфигурации соединения, которые существовали до начала выполнения триггера. Кроме того, при установлении соединения с использованием ODBC сервер автоматически устанавливает следующие параметры конфигурации:

• SET QUOTED_IDENTIFIER ON;
• SET TEXTSIZE 2147483647;
• SET ANSI_DEFAULTS ON;
• SET CURSOR_CLOSE_ON_COMMIT OFF;
• SET IMPLICIT_TRANSACTIONS OFF.

SQL Server 2000 предлагает несколько команд и хранимых процедур для получения служебной информации, которая может быть использована внутри триггера.
Для определения уровня вложенности триггеров используется следующая команда:
TRIGGER_NESTLEVEL( [ object_id ] )
Аргумент object_id задает идентификационный номер триггера, который может быть получен с помощью команды OBJECT_ID(‘name_object’).
Для получения списка столбцов, которые были изменены при выполнении команды INSERT или UPDATE, вызвавшей выполнение триггера, можно использовать следующую функцию:
COLUMNS_UPDATED()
Эта функция возвращает двоичное число, каждый бит которого, начиная с младшего, соответствует одному столбцу таблицы. Если бит установлен в 1, то соответствующий столбец была изменен. Также для определения факта изменения столбца можно использовать функцию UPDATE:
UPDATE (column_name)
Аргумент column_name определяет имя столбца, который необходимо проверить. Если функция возвращает значение TRUE, то столбец был изменен.
Для получения информации о количестве строк, которое будет изменено при успешном завершении триггера, можно использовать функцию @@ROWCOUNT. Эта функция возвращает количество строк, которые было изменено последней командой. Помните, что триггер запускается не при попытке изменить конкретную строку, а при попытке выполнить команду изменения. Одна такая команда может воздействовать на множество строк, поэтому триггер должен обрабатывать все эти строки.
Триггер выполняется как неявно определенная транзакция, поэтому внутри триггера допускается применение команд управления транзакциями. В частности, при обнаружении нарушения ограничений целостности для прерывания выполнения триггера и отмены всех изменений, которые пытался выполнить пользователь, необходимо использовать команду ROLLBACK TRANSACTION. В случае успешного завершения триггера можно использовать команду COMMIT TRANSACTION.
Выполнение команды ROLLBACK TRANSACTION или COMMIT TRANSACTION не прерывает работу триггера. Поэтому следует внимательно отслеживать попытки многократного отката транзакции при выполнении разных условий. Выходом из ситуации может быть использование флагов.
Когда сервер начинает выполнение триггера, он создает две специальных таблицы: inserted и deleted. В этих таблицах содержатся списки строк, которые будут соответственно вставлены и удалены по завершении транзакции. Структура таблиц inserted и deleted идентична структуре таблицы, для которой определен триггер.
Для каждого триггера создается свой комплект таблиц inserted и deleted, так что никакой другой триггер не сможет получить к ним доступ. В зависимости от типа операции, вызвавшей выполнение триггера, содержимое таблиц inserted и deleted может быть разным.

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

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

Управление триггерами

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

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

Если необходимо изменить имя триггера, следует воспользоваться следующей системной хранимой процедурой:
sp_rename ‘index_name’, ‘new_name’, ‘OBJECT’
Аргумент ‘index_name’ определяет старое имя триггера. Новое имя триггера задается с помощью аргумента ‘new_name’. Аргумент ‘OBJECT’ задает тип изменяемого объекта и в нашем случае не должен изменяться. Эта хранимая процедура применяется для переименования не только триггеров, но и индексов, баз данных, пользовательских типов данных, столбцов таблицы и т. д. Для переименования триггера trigg_07 в trigg_03 необходимо выполнить следующую команду:
EXEC sp_rename ‘trigg_07’, ‘trigg_03’, ‘OBJECT’

ВНИМАНИЕ При изменении имени триггера SQL Server не меняет автоматически старое имя на новое внутри кода триггера, поэтому если пользователь явно ссылался на имя триггера из кода Transact-SQL, то необходимо также изменить код триггера.

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

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

• создание, изменение и удаление базы данных (CREATE DATABASE, ALTER DATABASE и DROP DATABASE);
• восстановление резервной копии базы данных или журнала транзакций (RESTORE DATABASE, RESTORE LOG, LOAD DATABASE и LOAD LOG);
• выполнение команд RECONFIGURE, DISK RESIZE и DISK INIT.

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

Получение информации о триггере

Для получения кода Transact-SQL, выполняемого при вызове триггера, нужно воспользоваться следующей системной хранимой процедурой:
sp_helptext [@objname =] ‘name’
Аргумент ‘name’ должен содержать имя триггера, о котором необходимо получить информацию.
Для получения списка триггеров, определенных для конкретной таблицы базы данных, используется следующая хранимая процедура:
sp_helptrigger [@tabname =] ‘table’
[,[@triggertype =] ‘type’]
Аргумент ‘table’ задает имя таблицы, для которой нужно получить список созданных триггеров. Аргумент ‘type’ определяет тип триггеров, о которых будет выведена информация. Если этот аргумент опущен, то будет возвращен список всех триггеров. Возвращаемая после выполнения хранимой процедуры информация представлена в виде таблицы, каждая строка которой соответствует одному триггеру. Ниже перечислены столбцы возвращаемого результата и их дано назначение.

• TRIGGER_NAME (sysname). Имя триггера, присвоенное ему при создании или после переименования.
• TRIGGER_OWNER (sysname). Имя владельца триггера.
• ISUPDATE (int). Значение 1 означает, что триггер будет вызываться при выполнении команды UPDATE.
• ISDELETE (int). Значение 1 означает, что триггер будет вызываться при выполнении команды DELETE.
• ISINSERT (int). Значение 1 означает, что триггер будет вызываться при выполнении команды INSERT.

Для просмотра списка объектов, от которых зависит триггер, можно использовать следующую хранимую процедуру:
sp_depends [@objname =] ‘object’
Аргумент ‘object’ должен содержать имя триггера, о котором необходимо получить информацию. Хранимая процедура sp_depends может быть также использована для получения информации о зависимостях других объектов. Возвращаемый результат разделен на две таблицы: первая — для объектов, от которых зависит триггер, вторая — для объектов, зависящих от триггера.
Список столбцов первой таблицы следующий:

• NAME (nvarchar(40)) — имя объекта, от которого зависит триггер;
• TYPE (nvarchar(16)) — тип объекта, от которого зависит триггер;
• UPDATED (nvarchar(9)) — определяет, является ли объект изменяемым;
• SELECTED (nvarchar(8)) — определяет, включается ли объект в результат выборки SELECT;
• COLUMN (sysname) — имя столбца или другого параметра, от которого конкретно зависит триггер.

Список столбцов второй таблицы следующий:

• NAME (nvarchar(40)) — имя объекта, который зависит от триггера;
• TYPE (nvarchar(16)) — тип объекта, который зависит от триггера.

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

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

SELECT au_id, au_fname, au_lname, phone INTO authsmall FROM authors PRINT ‘Содержимое таблицы authsmall :’ SELECT * FROM authsmall

В результате будет выведена следующая информация:

(23 row(s) affected)
Содержимое таблицы authsmall :
au_id	au_fname	au_lname	phone
————	————	—————-	———
172-32-1176	Johnson	White	408 496-7223
213-46-8915	Marjorie	Green	415 986-7020
238-95-7766	Cheryl	Carson	415 548-7723
267-41-2394	Michael	O’Leary	408 286-2428
274-80-9391	Dean	Straight	415 834-2919
341-22-1782	Meander	Smith	913 843-0462
409-56-7008	Abraham	Bennet	415 658-9932
427-17-2319	Ann	Dull	415 836-7128
472-27-2349	Burt	Gringlesby	707 938-6445
486-29-1786	Charlene	Locksley	415 585-4620
527-72-3246	Morningstar	Greene	615 297-2723
648-92-1872	Reginald	Blotchet-Halls	503 745-6402
672-71-3249	Akiko	Yokomoto	415 935-4228
712-45-1867	Innes	del Castillo	615 996-8275
722-51-5454	Michel	DeFrance	219 547-9982
724-08-9931	Dirk	Stringer	415 843-2991
724-80-9391	Stearns	MacFeather	415 354-7128
756-30-7391	Livia	Karsen	415 534-9219
807-91-6654	Sylvia	Panteley	301 946-8853
846-92-7186	Sheryl	Hunter	415 836-7128
893-72-1158	Heather	McBadden	707 448-4982
899-46-2035	Anne	Ringer	801 826-0752
998-72-3567	Albert	Ringer	801 826-0752
(23 row(s) affected)

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

CREATE TRIGGER auth_del_1 ON authsmall FOR DELETE AS PRINT ‘Попытка удаления ‘+STR(@@ROWCOUNT)+’ строк в таблице authsmall’ PRINT ‘Пользователь ‘+CURRENT_USER IF CURRENT_USER<>‘dbo’ BEGIN PRINT ‘Удаление запрещено’ ROLLBACK TRANSACTION END ELSE PRINT ‘Удаление разрешено’

Созданный триггер будет выводить информацию о количестве строк, которое пытается удалить пользователь, и имя пользователя, выполнившего команду DELETE. Если пользователь не ‘dbo’, то удаление запрещается и выдается соответствующее предупреждение.
Выполним следующую команду:
DELETE FROM authsmall WHERE au_fname=’Johnson’
Будет выдана следующая информация:

Попытка удаления 1 строк в таблице authsmall Пользователь dbo Удаление разрешено (1 row(s) affected)

Если попытаться удалить строку, удаление которой заведомо невозможно, триггер, тем не менее, выполнит весь код:
DELETE FROM authsmall WHERE 2*2=5
Будет получен следующий результат:

Попытка удаления 0 строк в таблице authsmall Пользователь dbo Удаление разрешено (0 row(s) affected)

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

CREATE TRIGGER auth_upd_1 ON authsmall FOR UPDATE AS SET NOCOUNT ON PRINT ‘Попытка изменения данных в таблице authsmall’ IF (COLUMNS_UPDATED() & 1)!=0 PRINT ‘Изменение столбца au_id’ IF (COLUMNS_UPDATED() & 2)!=0 PRINT ‘Изменение столбца au_fname’ IF (COLUMNS_UPDATED() & 4)!=0 PRINT ‘Изменение столбца au_lname’ IF UPDATE(phone) PRINT ‘Изменение столбца phone’ IF ((CURRENT_USER = ‘dbo’) AND (COLUMNS_UPDATED() & 1)!=0) BEGIN PRINT ‘Пользователь dbo не может изменять идентификационный номер автора’ ROLLBACK TRANSACTION END

Попытаемся выполнить изменение телефона и имени автора:

UPDATE authsmall SET phone = ‘415 986-7020’, au_fname = ‘John’ WHERE au_lname = ‘Green’

Будет выдан следующий результат:
Попытка изменения данных в таблице authsmall
Изменение столбца au_fname
Изменение столбца phone

(1 row(s) affected)
Теперь попытаемся изменить идентификационный номер:

UPDATE authsmall SET phone = ‘913 843-7302’, au_id = ‘748-12-6859’ WHERE au_lname = ‘Smith’

Будет выдан следующий результат:
Попытка изменения данных в таблице authsmall
Изменение столбца au_id
Изменение столбца phone
Пользователь dbo не может изменять идентификационный номер автора
Создадим теперь триггер, который не будет разрешать вставку новых или изменение существующих строк таким образом, чтобы имя автора было Billy, а фамилия Geitsi:

CREATE TRIGGER auth_insupd_1 ON authsmall FOR INSERT, UPDATE AS IF EXISTS(SELECT * FROM inserted WHERE au_lname = ‘Geitsi’ AND au_fname = ‘Billy’) BEGIN PRINT ‘Недопустимо написание книги автором Billy Geitsi’ ROLLBACK TRANSACTION END

Теперь попытаемся выполнить соответствующую команду:

UPDATE authsmall SET au_lname = ‘Geitsi’, au_fname = ‘Billy’ WHERE au_lname = ‘Smith’

Будет возвращен следующий результат:
Попытка изменения данных в таблице authsmall
Изменение столбца au_fname
Изменение столбца au_lname
Недопустимо написание книги автором Billy Geitsi
Как видите, триггер auth_upd_1 был успешно выполнен. После его завершения началось выполнение триггера auth_insupd_1, который не дал команде модификации успешно завершиться.
Для следующего примера сначала создадим таблицу authsml2, скопировав в нее часть информации из таблицы authors:

SELECT au_id, state, city, contract INTO authsml2 FROM authors SELECT * FROM authsml2 ORDER BY city

Будет возвращен следующий результат:

au_id	state	city	contract
————	——	———————	———
712-45-1867	MI	Ann Arbor	1
238-95-7766	CA	Berkeley	1
409-56-7008	CA	Berkeley	1
648-92-1872	OR	Corvallis	1
472-27-2349	CA	Covelo	1
722-51-5454	IN	Gary	1
341-22-1782	KS	Lawrence	0
172-32-1176	CA	Menlo Park	1
527-72-3246	TN	Nashville	0
213-46-8915	CA	Oakland	1
274-80-9391	CA	Oakland	1
724-08-9931	CA	Oakland	0
724-80-9391	CA	Oakland	1
756-30-7391	CA	Oakland	1
427-17-2319	CA	Palo Alto	1
846-92-7186	CA	Palo Alto	1
807-91-6654	MD	Rockville	1
899-46-2035	UT	Salt Lake City	1
998-72-3567	UT	Salt Lake City	1
486-29-1786	CA	San Francisco	1
267-41-2394	CA	San Jose	1
893-72-1158	CA	Vacaville	0
672-71-3249	CA	Walnut Creek	1
(23 row(s) affected)

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

CREATE TRIGGER auth_del_1 ON authsml2 FOR DELETE AS DECLARE @@Result int SET @@Result = 1 IF EXISTS(SELECT * FROM deleted WHERE contract=1) BEGIN PRINT (‘Попытка удаления автора с подписанным контрактом’) SET @@Result = 0 END IF EXISTS(SELECT * FROM deleted WHERE state != ‘CA’) BEGIN PRINT (‘Попытка удаления автора, проживающего вне Калифорнии’) SET @@Result = 0 END IF @@Result = 0 BEGIN PRINT ‘Удаление запрещено’ ROLLBACK TRANSACTION END

В триггер пришлось ввести локальную переменную @@Result, которая используется в качестве флага неудачного выполнения удаления. Выполнять команду ROLLBACK TRANSACTION в данном случае нельзя, так как возможно соблюдение обоих условий, приводящее к попытке отката одной и той же транзакции в двух местах, а это недопустимо.
Попытаемся удалить все строки таблицы:
DELETE FROM authsml2
Будет получен следующий результат:
Попытка удаления автора с подписанным контрактом
Попытка удаления автора, проживающего вне Калифорнии
Удаление запрещено
Попытаемся удалить все строки таблицы, соответствующие авторам, проживающим в городе ‘Oakland’:
DELETE FROM authsml2 WHERE city = ‘Oakland’
Будет получен следующий результат:
Попытка удаления автора с подписанным контрактом
Попытаемся удалить все строки таблицы, соответствующие авторам, проживающим в городе ‘Lawrence’:
DELETE FROM authsml2 WHERE city = ‘Lawrence’
Будет получен следующий результат:
Попытка удаления автора, проживающего вне Калифорнии
Попытаемся удалить все строки таблицы, соответствующие авторам, проживающим в городе ‘Vacaville’:
DELETE FROM authsml2 WHERE city = ‘Vacaville’
Будет получен следующий результат:
(1 row(s) affected)