Как работают триггеры в sql. Выполнение триггеров в определенном порядке. Создание журнала логов

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

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

create table ( identity (1, 1) not null , null , null , null , (1) null) on go

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

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

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

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

create trigger trg_updatelast on dbo. for insert as declare @id int select @id = id from inserted update set = + where id = @id insert into triggerlog (triggername) values ("trg_updatelast")

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

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

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

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

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

Установить порядок

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

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

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

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

Разрешения

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

Получение порядка

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

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

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

Предостережения

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

Сервер sql 2005

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

Заключение

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

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

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

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

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

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

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

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

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

Для создания триггеров используйте оператор CREATE TRIGGER. В операторе указывается таблица, для которой объявляется триггер, событие, для которого триггер выполняется и индивидуальные инструкции для триггера. В общем команда показана в листинге 3.2.

Листинг 3.2. Общий вид команды CREATE TRIGGER

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

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

Сервер SQL не позволяет использовать следующие операторы в теле триггера:

ALTER DATABASE;
CREATE DATABASE;
DISK INIT;
DISK RESIZE;
DROP DATABASE;
LOAD DATABASE;
LOAD LOG;
RECONFIGURE;
RESTORE DATABASE;
RESTORE LOG.

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

3.4.2. Откат изменений в триггере

Объявление триггера может содержать оператор ROLLBACK TRANSACTION даже если не существует соответствующего BEGIN TRANSACTION. Как мы уже говорили, для любого изменения SQL сервер требует транзакции. Если она не указано явно, то создается неявная транзакция. Если выполняется оператор ROLLBACK TRANSACTION, то все изменения в триггере и изменения, которые стали причиной срабатывания триггера - откатываются.

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

Если срабатывает оператор ROLLBACK TRANSACTION, содержимое транзакции откатывается. Если есть операторы, следующие за ROLLBACK TRANSACTION, операторы выполняются. Это может быть не обязательным при использовании команды RETURN;
Если триггер откатывает транзакцию, определенную пользователем, то она откатывается полностью. Если триггер сработал, на выполнение модуля, для модуля команды также отменяются. Последующие операторы модуля не выполняются;
Вы должны минимизировать использование ROLLBACK TRANSACTION в коде триггера. Откат транзакции создает дополнительную работу, потому что все работы, которые не были закончены на данный момент в транзакции, будут незавершенными. Это будет негативно сказываться на производительности. Запускайте транзакцию после того, как все проверено, чтобы не пришлось ничего откатывать в триггере.

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

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS ROLLBACK TRANSACTION

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

имя начинается одной или сочетания букв u (update или обновление), i (insert или вставка) или d (delete или удаление). По этим буквам вы легко можете определить, на какие действия срабатывает триггер;
после подчеркивания идет имя таблицы, для которого создается триггер.

После имени идет ключевое слово ON и имя таблицы, для которой создается триггер.

Во второй строке идет ключевое слово FOR и событие, на которое срабатывает триггер. В данном примере указано действие UPDATE, т.е. обновление. И, наконец, после ключевого слова AS идет тело триггера, т.е. команды, которые должны выполняться. В данном примере выполняется только одна команда - ROLLBACK TRANSACTION, т.е. откат.

Теперь попробуем изменить данные в таблице tbPeoples, чтобы сработал триггер:

UPDATE tbPeoples SET vcFamil="dsfg"

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

SELECT * FROM tbPeoples

Не смотря на то, что при обновлении данных мы не запускали транзакцию, оператор ROLLBACK TRANSACTION был выполнен без ошибок, и изменения отменились.

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

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

Для обновления триггера используется оператор ALTER TRIGGER. Общий вид оператора можно увидеть в листинге 3.3.

Листинг 3.3. Оператор обновления триггера

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

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

ALTER TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE, INSERT AS ROLLBACK TRANSACTION

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

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

INSERT INTO tbPeoples(vcFamil) VALUES("ПЕТЕЧКИН")

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

ALTER TABLE table {ENABLE | DISABLE} TRIGGER {ALL | trigger_name [,..n]}

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

ALTER TABLE tbPeoples DISABLE TRIGGER u_tbPeoples

В первой строке мы пишем оператор ALTER TABLE и имя изменяемой таблицы. Во второй строке нужно указать ключевое слово DISABLE (отключить) или ENABLE (включить) и ключевое слово TRIGGER. И, наконец, имя триггера.

Попробуйте теперь добавить запить в таблицу tbPeoples. На этот раз, все пройдет успешно.

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

ALTER TABLE tbPeoples ENABLE TRIGGER ALL

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

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

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

DROP TRIGGER u_tbPeoples

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

3.4.5. Как работают триггеры?

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

Триггер INSERT

Что происходит, когда срабатывает триггер добавления записей? Давайте рассмотрим выполняемые сервером шаги:

Пользователем выполняется оператор INSERT для добавления записей;
Вызывается триггер;

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

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

Таблица inserted всегда содержит такую же структуру, что и у таблицы, на которую установлен триггер.

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

Листинг 3.4. Использование таблицы inserted

CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples FOR INSERT AS DECLARE @Name varchar(50) SELECT @Name=vcName FROM inserted IF @Name="ВАСЯ" BEGIN PRINT "ОШИБКА" ROLLBACK TRANSACTION END

В данном примере мы создаем триггер на добавление записей. Внутри триггера мы объявляем переменную @Name типа varchar длиной в 50 символов. В эту переменную мы сохраняем содержимое поля "vcName" таблицы inserted. Далее проверяем, если имя равно Вася, то сообщаем об ошибке и откатываем транзакцию. Иначе, строка будет удачно добавлена.

Давайте для закрепления материала, напишем триггер, который запретит нулевые значения для поля "vcName". Код такого триггера можно увидеть в листинге 3.5.

CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples FOR INSERT AS IF EXISTS (SELECT * FROM inserted WHERE vcName is NULL) BEGIN PRINT "ОШИБКА, вы должны заполнить поле vcName" ROLLBACK TRANSACTION END

В этом примере мы проверяем, если в таблице inserted есть записи с нулевым значением поля "vcName", то откатываем попытку добавления.

Триггер DELETE

Когда срабатывает триггер удаления, срабатывает примерно та же логика, что и при добавлении записей: <.p>

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

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

Вы должны учитывать:

когда строки добавляются в таблицу deleted, они еще существуют в таблице базы данных;
для таблицы deleted выделяется память, поэтому она всегда в кэше;
триггер удаления не выполняется на операцию TRUNCATE TABLE (очистка таблицы) потому что эта операция не заносится в журнал и не удаляет строк.

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

Листинг 3.6. Пример запрета удаления с помощью триггера

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF EXISTS (SELECT * FROM deleted WHERE vcName="рлр") BEGIN PRINT "ОШИБКА, нельзя удалить этого пользователя" ROLLBACK TRANSACTION END

В этом примере мы проверяем, если в таблице deleted существует запись с именем "рлр", то откатываем удаление. Добавьте в таблице запись с именем "рлр" и попытайтесь ее удалить. В ответ вы должны увидеть ошибку.

А что если попытаться удалить несколько записей? Например, в следующем примере удаляются записи две записи:

DELETE FROM tbPeoples WHERE vcName="рлр" or vcName="ВАСИЛИЙ"

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

Посмотрим на еще один пример в котором запрещается удаление генерального директора. Без триггера такое сделать невозможно:

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF EXISTS (SELECT * FROM deleted WHERE idPosition=1) BEGIN PRINT "ОШИБКА, нельзя удалить этого пользователя" ROLLBACK TRANSACTION END

В этом примере, запрещается удаление записи, если поле "idPosition" равно 1. Попробуйте удалить такую запись:

DELETE FROM tbPeoples WHERE idPosition=1

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

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

Триггер UPDATE

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

Вы можете объявить триггер для мониторинга обновления определенного поля с помощью указания опции IF UPDATE. Это позволяет триггеру изолировать активность определенной колонки. Когда обнаруживается обновление определенной колонки, триггер может выполнить определенные действия, такие как выброс сообщения об ошибке, которое сообщит о невозможности обновления колонки.

Давайте создадим триггер на таблицу tbPeoples, который будет выводить на экран сообщение, если изменяется поле "vcName"

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS IF UPDATE (vcName) PRINT "Я надеюсь, что вы правильно указали имя"

После оператора IF UPDATE, в скобках указано поле, которое необходимо проверить, было ли оно изменено. Если да, то будет выполнен следующий за проверкой оператор. В данном случае, это вывод на экран сообщения с помощью PRINT. Когда указанное поле не изменяется, то оператор конечно же не выполняется. Если нужно выполнить несколько операторов, то объедините их с помощью BEGIN и END.

Следующий запрос тестирует триггер:

UPDATE tbPeoples SET vcName="ИВАНУШКА" WHERE vcFamil="ПОЧЕЧКИН"

Убедитесь, что сообщение из триггера выводится на экран.

Давайте с помощью триггера попробуем запретить изменение полей, составляющих ФИО ("vcFamil", "vcName" и "vcSurName"). Для этого, если изменено одно из этих полей, то выводим на экран сообщение о запрете и откатываем транзакцию:

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS IF UPDATE (vcName) OR UPDATE (vcFamil) OR UPDATE (vcSurname) BEGIN PRINT "Нельзя изменять фамилию, имя и отчество" ROLLBACK TRANSACTION END

С помощью такого запроса легко увидеть, как проверять обновление сразу нескольких полей и выводить несколько операторов. Обратите внимание, что проверку делает именно оператор UPDATE, а не IF UPDATE. Я даже не знаю, почему разработчики SQL Server объединяют эти два оператора. Первый, это логический оператор, а второй – проверка, было ли обновлено поле.

3.4.6. INSTEAD OF

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

Каждая таблица или просмотрщик ограничены одним триггером INSTEAD OF на каждое событие. Вы не можете создавать триггеры INSTEAD OF на просмотрщик у которого включена опция CHECK OPTIONS.

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

Давайте создадим объект просмотра, который будет выбирать фамилию работника и название должности. Назовем этот объект просмотра Peoples:

CREATE VIEW People AS SELECT vcFamil, vcPositionName FROM tbPosition ps, tbPeoples pl WHERE ps.idPosition=pl.idPosition

Теперь создадим триггер INSTEAD OF на этот объект просмотра, с помощью которого, можно будет добавлять записи и при этом, они корректно будут прописываться, каждая в свою таблицу:

Листинг 3.7. Триггер INSTEAD OF для вставки данных

CREATE TRIGGER i_People ON dbo.People INSTEAD OF INSERT AS BEGIN -- Добавление должности INSERT INTO tbPosition (vcPositionName) SELECT vcPositionName FROM inserted i -- Добавление работника INSERT INTO tbPeoples (vcFamil, idPosition) SELECT vcFamil, idPosition FROM inserted i,tbPosition pn WHERE i.vcPositionName=pn.vcPositionName END

В этом примере интересности начинаются прямо со второй строки. Здесь указывается оператор INSTEAD OF и событие, на которое нужно реагировать. В данном случае в качестве события выступает вставка (INSERT).

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

INSERT INTO tbPeoples (vcFamil, idPosition) SELECT vcFamil, idPosition FROM inserted i,tbPosition pn WHERE i.vcPositionName=pn.vcPositionName

Попробуйте выполнить следующий запрос на добавление записей в объект просмотра:

INSERT INTO People VALUES("ИВАНУШКИН", "Клерк")

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

SELECT * FROM People

При обновлении таблицы есть одна проблема – нужно связать обновляемые данные с существующими. Первым на ум приходит запрос типа:

UPDATE tbPosition SET vcPositionName=i.vcPositionName FROM tbPosition pn, inserted i WHERE i.vcPositionName = pn.vcPositionName

Здесь мы связываем таблицу должностей с таблицей inserted. Но такой запрос никогда не будет выполнен. Почему? В inserted находятся новые значения, а в tbPosition еще старые и названия должностей никогда не свяжутся. Если связать с таблицей deleted, то записи свяжутся, но мы не будем знать новых значений, которые нужно занести в таблицу. Проблему можно решить, но лучшим вариантом будет добавление в объект просмотра ключевых полей:

ALTER VIEW People AS SELECT idPeoples, pl.idPosition, vcFamil, vcPositionName FROM tbPosition ps, tbPeoples pl WHERE ps.idPosition=pl.idPosition

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

Листинг 3.8. Обновление связанной вьюшки с помощью триггера

CREATE TRIGGER u_People ON dbo.People INSTEAD OF UPDATE AS BEGIN UPDATE tbPosition SET vcPositionName=i.vcPositionName FROM tbPosition pn, inserted i WHERE i.idPosition=pn.idPosition UPDATE tbPeoples SET vcFamil=i.vcFamil FROM tbPeoples pl, inserted i WHERE i.idPeoples=pl.idPeoples END

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

UPDATE People SET vcFamil="ИВАНУШКИН", vcPositionName="Генеральный директор" WHERE idPeoples=40 AND idPosition=13

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

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

3.4.7. Дополнительно о триггерах

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

Выполнения действий или каскадного обновления или удаления. Целостность ссылок может отличаться при использовании ограничений FOREIGN KEY и REFERENCE в операторе CREATE TABLE. Но триггер выгоден для гарантирования необходимых действий, когда должны быть произведены каскадные удаления или обновления, потому что триггеры более мощные. Если ограничение существует для таблицы с триггером, оно проверяется до выполнения триггера. Если ограничение нарушено, то триггер не работает. Если ограничение не сработает, то с помощью триггера можно реализовать более сложные проверки, которые уж точно будут гарантировать, что данные не нарушат целостность и пользователь внесет только те данные, которые разрешены;
Вы должны учитывать, что в таблицу может вставляться сразу несколько строк. Вы должны учитывать это при написании триггеров, как мы это делали при создании примеров с использованием INSTEAD OF;
Ограничения, правила и значения по умолчанию могут генерировать только стандартные системные ошибки. Если вам нужны собственные сообщения, вы должны использовать триггеры.

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

CREATE TRIGGER iu_tbPeoples ON dbo.tbPeoples FOR INSERT, UPDATE AS Действие

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

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

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

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

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

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

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

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

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

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

CREATE TRIGGER ud_tbPeoples ON dbo. tbPeoples FOR UPDATE, DELETE AS INSERT INTO tbPeoplesHistory SELECT newid(), del.* FROM Deleted del

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

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

Но в нашей задаче первичный ключ автоматически увеличиваемый и его нельзя генерировать. Придется перечислять все поля:

CREATE TRIGGER ud_tbPeoplesHistory ON dbo.tbPeoples FOR UPDATE, DELETE AS INSERT INTO tbPeoplesHistory (idPeoples, vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) SELECT del.* FROM Deleted del

Теперь посмотрим, как можно запретить удаление более чем одной строки:

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF (SELECT count(*) FROM deleted)>1 BEGIN PRINT "Нельзя удалять более одной строки" ROLLBACK TRANSACTION END

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

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

Sp_configure ‘nested triggers’, 0

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

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

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

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

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

ALTER DATABASE FlenovSQLBook SET RECURSIVE_TRIGGERS ON

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

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

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS DELETE pn FROM tbPhoneNumbers pn, inserted i WHERE pn.idPeoples=i.idPeoples

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

При добавлении записи в таблицу телефонов увеличиваем значение поля в таблицы работников;
При удалении номера телефона, уменьшаем значения поля.

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

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

EXEC sp_depends "tbPeoples"

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

EXEC sp_helptrigger People

Для просмотра кода существующего триггера используйте sp_helptext. Например, следующая команда позволяет увидеть текст триггера u_People, которую мы создавали для объекта просмотра.

Триггер (базы данных)

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

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

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

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

Пример (Oracle):

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

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

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

Wikimedia Foundation . 2010 .

Домашнее хозяйство
Спектроскопия

Смотреть что такое "Триггер (базы данных)" в других словарях:

Представление (базы данных) - У этого термина существуют и другие значения, см. Представление. Представление (англ. view, более созвучное не стандартное название «вид», в сленге программистов часто используется в качестве заимствования из английского «вьюха», «вьюшка»)… … Википедия

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

Реляционные базы данных - Реляционная база данных база данных, основанная на реляционной модели данных. Слово «реляционный» происходит от англ. relation (отношение). Для работы с реляционными БД применяют реляционные СУБД. Использование реляционных баз данных было… … Википедия

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

Курсор (базы данных) - У этого термина существуют и другие значения, см. Курсор (значения). Курсор ссылка на контекстную область памяти[источник не указан 126 дней]. В некоторых реализациях информационно логического языка SQL (Oracle,… … Википедия

Триггер (значения) - Триггер (англ. trigger в значении существительного «собачка, защёлка, спусковой крючок в общем смысле, приводящий нечто в действие элемент»; в значении глагола «приводить в действие»): в русском языке первоначально термин из области… … Википедия

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

База данных - Запрос «БД» перенаправляется сюда; см. также другие значения. База данных представленная в объективной форме совокупность самостоятельных материалов (статей, расчётов, нормативных актов, судебных решений и иных подобных материалов),… … Википедия

Проектирование баз данных - процесс создания схемы базы данных и определения необходимых ограничений целостности. Содержание 1 Основные задачи проектирования баз данных … Википедия

Модель данных - В классической теории баз данных, модель данных есть формальная теория представления и обработки данных в системе управления базами данных (СУБД), которая включает, по меньшей мере, три аспекта: 1) аспект структуры: методы описания типов и… … Википедия

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

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

(на примере 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 для соответствующего студента.

База данных Oracle позволяет определять триггеры, срабатывающие при выполнении команды DDL - проще говоря, любых команд SQL, создающих или модифицирующих объекты базы данных (таблицы, индексы и т. д.). Несколько примеров команд DDL: CREATE TABLE , ALTER INDEX , DROP TRIGGER - каждая из них создает, изменяет или удаляет объект базы данных. Синтаксис создания триггеров команд DDL почти не отличается от синтаксиса триггеров PL/SQL для DML . Они различаются лишь перечнем инициирующих событий и тем, что триггеры DDL не связываются с конкретными таблицами.

Весьма специфический триггер INSTEAD OF CREATE TABLE , описанный в конце раздела, позволяет манипулировать со стандартным поведением события CREATE TABLE . Не все аспекты синтаксиса и использования триггеров, описанные далее, применимы к этому типу триггеров.

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

Команда создания (или замены) триггера DDL имеет следующий синтаксис :

1 CREATE TRIGGER имя_триггера 2 {BEFORE | AFTER } { событие_DDL} ON {DATABASE | SCHEMA} 3 4 DECLARE 5 Объявления переменных 6 BEGIN 7 ...код триггера... 8 END;

Элементы триггера описаны в следующей таблице.

Строки	Описание
1	Создание триггера с заданным именем. Если триггер существует, а секция REPLACE отсутствует, попытка создания триггера приведет к ошибке ORA-4081
2	Строка определяет, должен ли триггер запускаться до или после наступления заданного события DDL , а также должен ли он запускаться для всех операций в базе данных или только в текущей схеме. Секция INSTEAD OF поддерживается только в Oracle9i Release 1 и последующих версиях
3	Необязательное условие WHEN , позволяющее задать логику для предотвращения ненужного выполнения триггера
4-7	Образец тела триггера

Приведем пример простого триггера, оповещающего о создании любых объектов:

SQL> CREATE OR REPLACE TRIGGER town_crier 2 AFTER CREATE ON SCHEMA 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE("I believe you have created something!"); 5 END; 6 / Trigger created. SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE a_table 2 (col1 NUMBER); Table created. SQL> CREATE INDEX an_index ON a_table(col1); Index created. SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS 2 BEGIN 3 RETURN(TRUE); 4 END; 5 / Function created. SQL> /* Очистка буфера DBMS_OUTPUT */ SQL> BEGIN NULL; END; 2 / I believe you have created something! I believe you have created something! I believe you have created something! PL/SQL procedure successfully completed.

Текст, возвращаемый встроенным пакетом DBMS_OUTPUT , не будет выводиться триггером DDL до успешного выполнения блока PL/SQL, даже если этот блок не выполняет никаких действий.

Однако сообщая о создании объекта, этот триггер не уточняет, что же именно создается. Но на самом деле триггеры DDL могут предоставлять и более полную информацию, ведь триггер можно переписать и таким образом:

SQL> CREATE OR REPLACE TRIGGER town_crier 2 AFTER CREATE ON SCHEMA 3 BEGIN 4 -- Использование атрибутов события для получения более полной информации 5 DBMS_OUTPUT.PUT_LINE("I believe you have created a " || 6 ORA_DICT_OBJ_TYPE || " called " || 7 ORA_DICT_OBJ_NAME); 8 END; 9 / Trigger created. SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE a_table 2 col1 NUMBER); Table created. SQL> CREATE INDEX an_index ON a_table(col1); Index created. SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS 2 BEGIN 3 RETURN(TRUE); 4 END; 5 / Function created. SQL> /*-- Очистка буфера DBMS_OUTPUT */ SQL> BEGIN NULL; END; 2 / I believe you have created a TABLE called A_TABLE I believe you have created a INDEX called AN_INDEX I believe you have created a FUNCTION called A_FUNCTION PL/SQL procedure successfully completed.

В этих примерах представлены два важнейших аспекта триггеров DDL: события, с которыми они связываются, и атрибуты событий, которые в них доступны.

События триггеров

Список событий, которые можно связать с триггерами DDL, приведен в табл. 1. Любой триггер DDL может вызываться как до (BEFORE), так и после (after) наступления указанного события.

Таблица 1. События DLL

Событие DDL	описание
ALTER	Создание объекта базы данных командой SQL ALTER
ANALYZE	Анализ состояния объекта базы данных командой SQL
ASSOCIATE STATISTICS	Связывание статистики с объектом базы данных
AUDIT	Включение аудита базы данных командой SQL AUDIT
COMMENT	Создание комментария для объекта базы данных
CREATE	Создание объекта базы данных командой SQL CREATE
DDL	Любое из перечисленных событий
DISASSOCIATE STATISTICS	Удаление статистики,связанной с объектом баз данных
DROP	Удаление объекта базы данных командой SQL DROP
GRANT	Назначение прав командой SQL GRANT
NOAUDIT	Отключение аудита базы данных командой SQL NOAUDIT
RENAME	Переименование объекта базы данных командой SQL RENAME
REVOKE	Отмена прав командой SQL REVOKE
TRUNCATE	Очистка таблицы командой SQL TRUNCATE

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

Атрибутные функции

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

Таблица 2. События триггеров DDL и атрибутные функции

Функция	Что возвращает
ORA_CLIENT_IP_ADDRESS	IP-адрес клиента
ORA_DATABASE_NAME	Имя базы данных
ORA_DES_ENCRYPTED_PASSWORD	Пароль текущего пользователя, зашифрованный с использованием алгоритма DES
ORA_DICT_OBJ_NAME	Имя объекта базы данных, связанного с командой DDL, которая вызвала запуск триггера
ORA_DICT_OBJ_NAME_LIST	Количество обработанных командой объектов. В параметре NAME_LIST возвращается полный список этих объектов в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T
ORA_DICT_OBJ_OWNER	Имя владельца объекта базы данных, связанного с командой DDL, которая вызвала запуск триггера
ORA_DICT_OBJ_OWNER_LIST	Количество обработанных командой объектов. В параметре NAME_LIST возвращается полный список имен этих объектов в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T
ORA_DICT_OBJ_TYPE	Тип объекта базы данных, связанного с командой DDL, вызвавшей запуск триггера (например, TABLE или INDEX)
ORA_GRANTEE	Количество пользователей, получивших привилегии. В аргументе USER_LIST содержится полный список этих пользователей в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T
ORA_INSTANCE_NUM	Номер экземпляра базы данных
ORA_IS_ALTER_COLUMN	TRUE , если изменяется столбец, заданный параметром COLUMN_NAME ; FALSE в противном случае
ORA_IS_CREATING_NESTED_TABLE	TRUE , если создается вложенная таблица; FALSE в противном случае
ORA_IS_DROP_COLUMN	TRUE , если удаляется столбец, заданный параметром COLUMN _ NAME; FALSE в противном случае
ORA_LOGIN_USER	Имя пользователя Oracle , для которого запущен триггер
ORA_PARTITION_POS	Позиция команды SQL для корректной вставки секции PARTITION
ORA_PRIVILEGE_LIST	Количество предоставленных или отмененных привилегий. В аргументе PRIVILEGE_LIST содержится полный список привилегий в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T
ORA_REVOKEE	Количество пользователей, лишенных привилегий. В аргументе USER_LIST содержится полный список этих пользователей в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T
ORA_SQL_TXT	Количество строк в команде SQL, которая вызвала запуск триггера. Аргумент SQL_TXT возвращает каждую строку команды в виде аргумента типа DBMS_STANDARD.ORA_NAME_LIST_T
ORA_SYSEVENT	Тип события, вызвавшего запуск триггера DDL (например, CREATE , DROP или ALTER)
ORA_WITH_GRANT_OPTION	TRUE , если привилегии предоставлены конструкцией GRANT ; FALSE в противном случае

Об атрибутных функциях необходимо дополнительно сказать следующее:

Тип данных 0RA_NAME_LIST_T определен в пакете DBMS_STANDARD так:

TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);

Иными словами, это вложенная таблица строк, каждая из которых может содержать до 64 символов.

События триггеров DDL и атрибутные функции также определены в пакете DBMS_ STANDARD . Для каждой из функций этого пакета Oracle создает независимую функцию, добавляя к ее имени префикс 0RA_, для чего при создании базы данных выполняется сценарий $ORACLE_HOME/rdbms/dbmstrig.sql. В некоторых версиях Oracle этот сценарий содержит ошибки, из-за которых независимые функции не видны или не выполняются. Если вы сомневаетесь в правильности определения этих элементов, попросите администратора базы данных проверить сценарий и внести необходимые исправления.
Представление словаря данных USER_S0URCE не обновляется до срабатывания обоих триггеров DDL, BEFORE и AFTER . Иначе говоря, вы не сможете использовать эти функции для реализации системы контроля версий «до и после», построенной исключительно в границах базы данных и основанной на триггерах.

Применение событий и атрибутов

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

TRIGGER no_create AFTER CREATE ON SCHEMA BEGIN RAISE_APPLICATI0N_ERR0R (-20000, "ERROR: Objects cannot be created in the production database."); END;

После его создания в базе данных не удастся создать ни один объект:

SQL> CREATE TABLE demo (coll NUMBER); * ERROR at line 1: ORA-20000: Objects cannot be created in the production database.

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

TRIGGER no_create AFTER CREATE ON SCHEMA BEGIN RAISE_APPLICATION_ERROR (-20000, "Cannot create the " || ORA_DICT_OBJ_TYPE || " named" || ORA_DICT_OBJ_NAME || " as requested by" || ORA_DICT_OBJ_OWNER || " in production."); END;

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

SQL> CREATE TABLE demo (coll NUMBER); * ERROR at line 1: ORA-20000: Cannot create the TABLE named DEMO as requested by SCOTT in production

Можно было бы даже реализовать эту логику в виде триггера BEFORE и воспользоваться событием ora_sysevent:

TRIGGER no_create BEFORE DDL ON SCHEMA BEGIN IF ORA_SYSEVENT = "CREATE" THEN RAISE_APPLICATION_ERROR (-20000, "Cannot create the " || ORA_DICT_OBJ_TYPE || " named " ||ORA_DICT_OBJ_NAME || " as requested by " || ORA_DICT_OBJ_OWNER); ELSIF ORA_SYSEVENT = "DROP" THEN -- Логика операций DROP ... END IF; END;

Какой столбец был изменен?

Для получения информации о том, какой столбец был изменен командой ALTER TABLE , можно воспользоваться функцией ORA_IS_ALTER_COLUMN . Пример:

TRIGGER preserve_app_cols AFTER ALTER ON SCHEMA DECLARE -- Курсор для получения информации о столбцах таблицы CURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2) IS SELECT column_name FROM all_tab_columns WHERE owner = cp_owner AND table_name = cp_table; BEGIN -- Если была изменена таблица... IF ora_dict_obj_type = "TABLE" THEN -- Для каждого столбца в таблице... FOR v_column_rec IN curs_get_columns (ora_dict_obj_owner, ora_dict_obj_name) LOOP -- Является ли текущий столбец измененным? IF ORA_IS_ALTER_COLUMN (v_column_rec.column_name) THEN -- Отклонить изменения в "критическом" столбце IF mycheck.is_application_column (ora_dict_obj_owner, ora_dict_obj_name, v_column_rec.column_name) THEN CENTRAL_ERROR_HANDLER ("FAIL", "Cannot alter core application attributes"); END IF; -- критическая таблица/столбец END IF; -- текущий столбец был изменен END LOOP; -- для каждого столбца в таблице END IF; -- таблица была изменена END;

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

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

IF ORA_IS_DROP_COLUMN ("COL2") THEN что-то сделать ELSE сделать что-то другое! END IF;

Функции ORA_IS_DROP_COLUMN и ORA_IS_ALTER_COLUMN не обращают внимания на то, к какой таблице присоединен столбец; они работают исключительно по имени столбца.

Списки, возвращаемые атрибутными функциями

Некоторые атрибутные функции возвращают данные двух типов: список элементов и количество элементов. Например, функция ORA_GRANTEE возвращает список и количество пользователей, которым предоставлены определенные права, а функция 0RA_PRIVILEGE_LIST - список и количество предоставленных прав. Обычно обе эти функции применяются в триггерах AFTER GRANT . Пример использования этих функций представлен в файле privs.sql на сайте github. Фрагмент кода этого примера:

TRIGGER what_privs AFTER GRANT ON SCHEMA DECLARE v_grant_type VARCHAR2 (30); v_num_grantees BINARY_INTEGER; v_grantee_list ora_name_list_t; v_num_privs BINARY_INTEGER; v_priv_list ora_name_list_t; BEGIN -- Получение информации о типе с последующей выборкой списков. v_grant_type:= ORA_DICT_OBJ_TYPE; v_num_grantees:= ORA_GRANTEE (v_grantee_list); v_num_privs:= ORA_PRIVILEGE_LIST (v_priv_list); IF v_grant_type = "ROLE PRIVILEGE" THEN DBMS_OUTPUT.put_line ("The following roles/privileges were granted"); -- Вывод привилегии для каждого элемента списка. FOR counter IN 1 .. v_num_privs LOOP DBMS_OUTPUT.put_line ("Privilege " || v_priv_list (counter)); END LOOP;

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

SQL> GRANT DBA TO book WITH ADMIN OPTION; Grant succeeded. SQL> EXEC DBMS_OUTPUT.PUT_LINE("Flush buffer"); The following roles/privileges were granted Privilege UNLIMITED TABLESPACE Privilege DBA Grant Recipient BOOK Flush buffer SQL> GRANT SELECT ON x TO system WITH GRANT OPTION; Grant succeeded. SQL> EXEC DBMS_OUTPUT.PUT_LINE("Flush buffer"); The following object privileges were granted Privilege SELECT On X with grant option Grant Recipient SYSTEM Flush buffer

Можно ли удалить неудаляемое?

Я показал, что триггеры DDL могут использоваться для предотвращения выполнения определенного типа операций DDL с конкретными объектами или типами объектов. А если я создам триггер, который предотвращает DDL -операции DROP , а затем попытаюсь удалить сам триггер? Не появится ли триггер, который по сути невозможно удалить? К счастью, в Oracle этот сценарий был предусмотрен:

SQL> CREATE OR REPLACE TRIGGER undroppable 2 BEFORE DROP ON SCHEMA 3 BEGIN 4 RAISE_APPLICATION_ERROR(-20000,"You cannot drop me! I am invincible!"); 5 END; SQL> DROP TABLE employee; * ERROR at line 1: ORA-20000: You cannot drop me! I am invincible! SQL> DROP TRIGGER undroppable; Trigger dropped.

При работе с подключаемыми базами данных (Oracle Database 12c и выше) можно вставить ключевое слово PLUGGABLE перед DATABASE в определении триггера. DATABASE (без PLUGGABLE) определяет триггер на корневом уровне. В мультиарендной (multitenant) контейнерной базе данных (CDB) только пользователь, подключившийся к корневому уровню, может создать триггер для всей базы данных. PLUGGABLE DATABASE определяет триггер для подключаемой базы данных, к которой вы подключены. Триггер срабатывает каждый раз, когда любой пользователь заданной базы данных или PDB инициирует активизирующее событие.

Триггер INSTEAD OF CREATE

Oracle предоставляет триггер INSTEAD OF CREATE для автоматической группировки данных таблиц. Для этого триггер должен перехватить выполняемую команду SQL , вставить в нее условие группировки, а затем выполнить при помощи функции ORA_SQL_TXT . Следующий пример показывает, как это делается.

TRIGGER io_create INSTEAD OF CREATE ON DATABASE WHEN (ORA_DICT_OBJ_TYPE = "TABLE") DECLARE v_sql VARCHAR2 (32767); -- Генерируемый код sql v_sql_t ora_name_list_t; -- таблица sql BEGIN -- Получение выполняемой команды SQL FOR counter IN 1 .. ora_sql_txt (v_sql_t) LOOP v_sql:= v_sql || v_sql_t (counter); END LOOP; -- Для определения условия PARTITION будет вызвана -- функция magic_partition. v_sql:= SUBSTR (v_sql, 1, ora_partition_pos) || magic_partition_function || SUBSTR (v_sql, ora_partition_pos + 1); /* Вставить перед именем таблицы имя пользователя. | Комбинации CRLF заменяются пробелами. | Операция требует наличия явной привилегии CREATE ANY TABLE, | если только вы не переключились на модель AUTHID CURRENT_USER. */ v_sql:= REPLACE (UPPER (REPLACE (v_sql, CHR (10), " ")) , "CREATE TABLE " , "CREATE TABLE " || ora_login_user || "."); -- Выполнение сгенерированной команды SQL EXECUTE IMMEDIATE v_sql; END;

Теперь группировка таблиц будет осуществляться автоматически в соответствии с логикой функции my_partition .

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

Если не включить только что приведенную секцию WHEN , при попытке создания объектов, отличных от таблиц, происходит ошибка:

ORA-00604: error occurred at recursive SQL level 1 ORA-30511: invalid DDL operation in system triggers

Кроме того, при попытке создания триггера INSTEAD OF для любой другой операции DDL , кроме CREATE , компилятор выдает сообщение об ошибке (ORA-30513).

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