Главная / Сети / Создание триггеров sql server. Триггер (базы данных)
18.09.2023

Создание триггеров sql server. Триггер (базы данных)

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

Три́ггер (англ. 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) аспект структуры: методы описания типов и… … Википедия

Триггер это специальный вид хранимых процедур, которые выполняются на определенные события в таблице. Триггер связывается с определенной таблицей и чаще всего выполняет защитную роль для данных. В разделе 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 должно быть поле, в котором сохраняется количество номеров телефонов. Конечно же, это денормализация данных, ведь количество можно всегда подсчитать, но не забывайте, что это пример. Для поддержки поля можно создать следующие триггеры:

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

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

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

EXEC sp_depends "tbPeoples"

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

EXEC sp_helptrigger People

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

Триггер базы данных – это оформленный специальным образом именованный блок PL/SQL, хранящийся в базе данных. Каждый триггер связан с определенной таблицей и автоматически запускается ORACLE при выполнении одного из DML-операторов (INSERT, DELETE, UPDATE) или их совокупности над этой таблицей.

Назначение триггеров. Триггеры могут быть использованы:

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

2) предотвращения неверных транзакций;

3) выполнения процедур комплексной проверки прав доступа и секретности данных;

4) генерации некоторых выражений на основе значений, имеющихся в столбцах таблиц;

5) реализации сложных бизнес-правил для обработки данных (возможность отследить «эхо», т.е. возможность при изменении одной таблицы, обновлять данные связанных с ней таблиц).

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

CREATE TRIGGER имя_триггера

{BEFORE | AFTER}

{INSERT | DELETE | UPDATE }

ON имя_таблицы

< PL/SQL_блок >

При наличии ключевых слов OR REPLACE триггер создается заново, если он уже существует.

Конструкция BEFORE | AFTER указывает на момент запуска триггера. Вариант BEFORE означает, что триггер будет запускаться перед выполнением активизирующего DML-оператора; вариант AFTER означает, что триггер будет запускаться после выполнения активизирующего DML-оператора.

Конструкция INSERT | DELETE | UPDATE указывает тип активизирующего триггер DML-оператора. Разрешается, используя логическую операцию OR, задать совокупность активизирующих операторов, например: INSERT OR DELETE. Если при использовании варианта UPDATE указан список столбцов, то триггер будет запускаться при модификации одного из указанных столбцов; если список столбцов отсутствует, то триггер будет запускаться при изменении любого из столбцов связанной с триггером таблицы.

Конструкция FOR EACH ROW указывает на характер воздействия триггера: строковый или операторный. Если конструкция FOR EACH ROW присутствует, то триггер является строковым; при отсутствии ее триггер является операторным. Операторный триггер запускается один раз до или после выполнения активизирующего триггер DML-оператора независимо от того, сколько строк в связанной с триггером таблице подвергается модификации. Строковый триггер запускается один раз для каждой из строк, которая подвергается модификации DML-оператором, активизирующим триггер.

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

Конструкция PL/SQL_блок представляет блок PL/SQL, который ORACLE запускает при активизации триггера.

Классификация триггеров. В основном различают двенадцатьтипов триггеров. Тип триггера определяется сочетанием следующих трех параметров:

1) характером воздействия триггера на строки связанной с ним таблицы (строковый или операторный);

2) моментом запуска триггера: до (BEFORE) или после (AFTER) исполнения активизирующего триггер DML-оператора;

3) типом активизирующего триггер DML-оператора (INSERT, DELETE, UPDATE);

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

1) выполняется операторный триггер BEFORE (если их несколько, то ничего о порядке их выполнения сказать нельзя);

2) выполняется строковый триггер BEFORE;

3) выполняется активизирующий триггер DML-оператор с последующей проверкой всех ограничений целостности данных;

4) выполняется строковый триггер AFTER с последующей проверкой всех ограничений целостности данных;

5) выполняется операторный триггер AFTER.

Триггерные предикаты. Если в триггере указывается совокупность активизирующих триггер DML-операторов (например, INSERT OR DELETE), то для распознавания того, какой конкретно из DML-операторов выполняется над связанной с триггером таблицей, используются триггерные предикаты: INSERTING, DELETING, UPDATING. Они представляют собой логические функции, возвращающие TRUE, если тип активизирующего оператора совпадает с типом предиката, и FALSE – в противном случае. Для задания одних и тех же действий в случае выполнения различных DML-операторов в условном операторе триггерные предикаты объединяются с помощью логических операций.

Псевдозаписи. Для строковых триггеров существуют специальные конструкции, которые позволяют при выполнении DML-операторов над строкой таблицы, обращаться как к старым значениям, которые находились в ней до модификации, так и к новым, которые появятся в строке после ее модификации. Эти конструкции называются псевдозаписями и обозначаются old и new. Структура этих псевдозаписей идентична структуре строки модифицируемой таблицы, но оперировать можно только отдельными полями псевдозаписи. Обращение к полям псевдозаписи происходит по следующей схеме: перед old или new ставится символ «:», далее через точку указывается название поля. Значения, которые принимают поля псевдозаписи при выполнении активизирующих DML-операторов, определяются следующим образом.

Оператор INSERT – псевдозапись:new эквивалентна вставляемой строке, а псевдозапись:old во всех полях имеет значение NULL.

Оператор DELETE – псевдозапись:old эквивалентна удаляемой строке, а псевдозапись:new во всех полях имеет значение NULL.

Оператор UPDATE – псевдозапись:new эквивалентна строке, полученной в результате модификации, а псевдозапись:old во всех полях имеет исходное значение строки.

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

ALTER TRIGGER имя_триггера DISABLE;

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

ALTER TRIGGER имя_триггера ENABLE;

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

ALTER TABLE имя_таблицы {DISABLE | ENABLE} ALL TRIGGERS;

где вариант DISABLE используется для отключения, а вариант ENABLE – для включения всех триггеров данной таблицы.

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

DROP TRIGGER имя_триггера;

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

SELECT * FROM USER_TRIGGERS;

Примеры.

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

Добавление новых строк в таблицу KNIGA_POS­TAVKA выполняется оператором INSERT. Поскольку триггер должен запускаться перед выполнением каждого оператора INSERT, следовательно, он должен быть строковым BEFORE-триггером. Для сохранения целостности данных необходимо проверить, имеются ли вносимые коды книг и в таблице KNIGA. Для этого с помощью однострочного оператора SELECT осуществляется выборка информации из таблицы KNIGA, где в условии выборки используется поле КОД_КНИГИ псевдозаписи:new. Если количество строк с данным кодом книги в таблице KNIGA окажется равным нулю, будет сгенерировано исключение и выдано соответствующее сообщение.

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

CREATE OR REPLACE TRIGGER TR1

BEFORE INSERT ON KNIGA_POSTAVKA

SELECT COUNT(*) INTO KOL FROM KNIGA

WHERE КОД_КНИГИ = :NEW.КОД_КНИГИ;

IF KOL = 0 THEN RAISE_APPLICATION_ERROR

(–20212,"В таблице KNIGA нет информации о данной книге");

Действие триггера TR1 может быть проверено выполнением следую­щего оператора, осуществляющего вставку строки в таблицу KNIGA_POSTAVKA и тем самым вызывающих активизацию триггера TR2:

INSERT INTO KNIGA_POSTAVKA VALUES(21,15,’Иванов’,15,

Поскольку код книги 15 отсутствует в таблице KNIGA, то будет сгенерировано исключение и выдано соответствующее сообщение.

2. Создать триггер, который запрещает вносить в таблицу KNIGA строки со значением поля ЦЕНА больше, чем 5000 рублей, а также осуществлять увеличение цены книг, информация о которых хранится в таблице KNIGA, более чем на 20 %. При нарушении данного требования должно генерироваться исключение с выдачей соответствующего сообщения.

Так как внесение новых строк в таблицу KNIGA осуществляется в результате выполнения оператора INSERT, а значение поля ЦЕНА в таблице KNIGA, содержащего цену книги, может быть изменено в результате выполнения оператора UPDATE, то в триггере указывается совокупность запускающих DML-операторов. Поскольку триггер должен запускаться перед выполнением каждого из указанных DML-операторов, следовательно, он является строковым BEFORE-триггером. Так как действия, выполняемые триггером, различны для каждого из запускающих DML-операторов, модифицирующих таблицу KNIGA, то для распознавания типа DML-оператора используются соответствующие триггерные предикаты INSERTING и UPDAITING. Вследствие того что при вставке новых строк проверке должно быть подвергнуто новое значение поля ЦЕНА, а при модификации значения поля ЦЕНА новое значение должно сравниваться со старым значением, необходимо использовать псевдозаписи:new и:old.

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

CREATE OR REPLACE TRIGGER TR2

BEFORE INSERT OR UPDATE OF ЦЕНА ON KNIGA

IF INSERTING THEN

IF:NEW.ЦЕНА > 5000 THEN

RAISE_APPLICATION_ERROR

(–20102, "В таблицу KNIGA нельзя вносить записи с ценой книги > 5000");

IF UPDATING THEN

IF:NEW.ЦЕНА > :OLD.ЦЕНА*1.2 THEN

RAISE_APPLICATION_ERROR

(–20103, "В таблице KNIGA нельзя изменять цену книги более чем на 20 %");

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

Оператор вставки строк в таблицу KNIGA, вызывающие активизацию триггера TR2:

INSERT INTO KNIGA VALUES(21, "Дюна", "Герберт", 5268, "Аст",

"Фантастика");

Оператор обновления строк в таблице KNIGA, вызывающие активизацию триггера TR2:

UPDATE KNIGA SET ЦЕНА=6000;

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

3. Создать триггер, который в созданную таблицу STAT, содержащую столбцы:

имя издательства – IZD,

количество книг в жанре «Роман» – KOL_ROM,

количество книг в жанре «Фантастика» – KOL_FAN,

при каждой модификации таблицы KNIGA формирует и заносит в соответствующие столбцы таблицы STAT суммарное количество книг по каждому из издательств в разрезе указанных тематик: «Роман» и «Фантастика».

Модификация таблицы KNIGA осуществляется выполнением следующих DML-операторов: INSERT, DELETE или оператора UPDATE, модифицирующего значения столбца ЖАНР в таблице KNIGA. Так как действия по формированию информации таблицы STAT выполняются после выполнения каждого из модифицирующих таблицу KNIGA операторов, то по типу это операторный AFTER-триггер. Поскольку действия, выполняемые триггером, одинаковы для всех типов активизирующих его операторов, то триггерные предикаты не используются. Перед созданием триггера должна быть создана таблица STAT.

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

DROP TABLE STAT;

CREATE TABLE STAT

(IZD VARCHAR2(15),

KOL_ROM NUMBER(7),

KOL_FAN NUMBER(7)

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

CREATE OR REPLACE TRIGGER TR3

AFTER INSERT OR DELETE OR UPDATE OF ЖАНР

CURSOR V1 IS SELECT ИЗДАТЕЛЬСТВО,

COUNT(НАЗВАНИЕ) KOL1

FROM KNIGA WHERE ЖАНР = "Роман"

GROUP BY ИЗДАТЕЛЬСТВО;

CURSOR V2 IS SELECT ИЗДАТЕЛЬСТВО,

COUNT(НАЗВАНИЕ) KOL2

FROM KNIGA WHERE ЖАНР = "Фантастика"

GROUP BY ИЗДАТЕЛЬСТВО;

DELETE FROM STAT;

FOR Z1 IN V1 LOOP

INSERT INTO STAT VALUES(Z1.ИЗДАТЕЛЬСТВО,

FOR Z1 IN V2 LOOP

UPDATE STAT SET KOL_FAN = Z1.KOL2

WHERE IZD = Z1.ИЗДАТЕЛЬСТВО;

IF SQL%NOTFOUND THEN

INSERT INTO STAT VALUES(Z1.ИЗДАТЕЛЬСТВО, 0,

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

Операторы вставки строк в таблицу KNIGA, вызывающие активизацию триггера TR3:

INSERT INTO KNIGA VALUES(46, "Еретики Дюны", "Герберт",368,

"Аст", "Фантастика");

INSERT INTO KNIGA VALUES(42, "Ингвар и Ольха",

"Никитин",168, "Аст", "Роман ");

Операторы удаления строк из таблицы KNIGA, вызывающие активизацию триггера TR3:

DELETE KNIGA WHERE НАЗВАНИЕ = "Казаки";

Операторы модификации строк в таблице KNIGA, вызывающие активизацию триггера TR3:

UPDATE KNIGA SET ЖАНР="Фантастика" WHERE НАЗВАНИЕ =

"Ингвар и Ольха";

Просмотр информации в таблице STAT можно выполнить следующим оператором.

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

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

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

Обзор триггеров Триггеры DML-триггеры DDL-триггеры DML-события: Insert, Delete, Update Logon-триггеры DDL-события: Create, Drop, Alter Logon Появились в SQL Server 2005

DML - trigger Объект - таблица, VIEW Событие - insert, update, delete для таблицы и для VIEW. Время активации – до (вместо) или после выполнения оператора.

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

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

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

Когда нужны триггеры Для каскадных изменений в связанных таблицах БД (если их нельзя выполнить при помощи каскадных ограничений ссылочной целостности). Для предотвращения случайных или неправильных операций INSERT, UPDATE и DELETE Для реализации ограничений целостности, которые нельзя определить при помощи ограничения CHECK. DML-триггеры могут ссылаться на столбцы других таблиц.

Еще… Журнализация и аудит. С помощью триггеров можно отслеживать изменения таблиц, для которых требуется поддержка повышенного уровня безопасности. Данные об изменении таблиц могут сохраняться в других таблицах и включать, например, идентификатор пользователя, время операции обновления; сами обновляемые данные и т. д. Согласование и очистка данных. С любым простым оператором SQL, обновляющим некоторую таблицу, можно связать триггеры, производящие соответствующие обновления других таблиц. Операции, не связанные с изменением базы данных. В триггерах могут выполняться не только операции обновления базы данных. Стандарт SQL позволяет определять хранимые процедуры (которые могут вызываться из триггеров), посылающие электронную почту, печатающие документы и т. д.

Когда не надо использовать триггеры Не нужно реализовывать триггерами возможности, достигаемые использованием декларативных средств СУБД (ограничения целостности или внешние ключи) Избегайте сложных цепочек триггеров

Советы Не используйте триггеры, если можно применить проверочное ограничение CHECK Не используйте ограничение CHECK, если можно обойтись ограничением UNIQUE.

Основные параметры триггера Имя таблицы (или представления) Время срабатывания: AFTER(FOR) или INSTEAD OF Событие: INSERT, UPDATE, DELETE (TRUNCATE TABLE – это не удаление!) Тело триггера! Последовательность срабатывания однотипных триггеров произвольна

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

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

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

Пример CREATE TRIGGER trg ON my_table FOR INSERT, UPDATE, DELETE AS select "this is trigger"

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

Выбор типа триггера Триггеры INSTEAD OF используются для: – Выборочного запрещения исполнения команды, для которой определен триггер (проверки предусловия); – Подсчета значений столбцов до завершения команды INSERT или UPDATE. Триггеры AFTER используются для: – Учета выполненных операций; – Проверки пост-условий исполнения команды.

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

Триггер INSTEAD OF Триггер INSTEAD OF выполняется вместо запуска оператора SQL. Тем самым переопределяется действие запускающего оператора. Можно задать по одному триггеру INSTEAD OF на один оператор INSERT, UPDATE или DELETE. Триггер INSTEAD OF можно задать для таблицы и/или представления Можно использовать каскады триггеров INSTEAD OF, определяя представления поверх представлений, где каждое представление имеет отдельный триггер INSTEAD OF. Триггеры INSTEAD OF не разрешается применять для модифицируемых представлений, содержащих опцию WITH CHECK.

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

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

Порядок AFTER-триггеров sp_settriggerorder @triggername = "Another. Trigger", @order = "first" sp_settriggerorder @triggername = "My. Trigger", @order = "last" sp_settriggerorder @triggername = "My. Other. Trigger", @order = "none" sp_settriggerorder @triggername = "Yet. Another. Trigger", @order = "none"

Использование inserted, deleted Специальные таблицы: inserted – вставленные значения (для INSERT, UPDATE) deleted – удаленные значения (для UPDATE, DELETE)

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

Использование таблиц deleted и inserted Таблица deleted содержит копии строк, на которые повлиял оператор DELETE или UPDATE. Строки, удаляемые из таблицы данного триггера, перемещаются в таблицу deleted. После этого к данным таблицы deleted можно осуществлять доступ из данного триггера. Таблица inserted содержит копии строк, добавленных к таблице данного триггера при выполнении оператора INSERT или UPDATE. Эти строки добавляются одновременно в таблицу триггера и в таблицу inserted.

Использование таблиц deleted и inserted Поскольку оператор UPDATE обрабатывается как DELETE, после которого следует INSERT, то при использовании оператора UPDATE старые значения строк копируются в таблицу deleted, а новые значения строк – в таблицу триггера и в таблицу inserted. Триггер INSERT => deleted пуст Триггер DELETE => inserted пуст но сообщение об ошибке не возникнет!

Создание триггера CREATE TRIGGER [ schema_name. ]trigger_name ON { table | view } { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement}

CREATE TRIGGER plus_1 ON table 1 instead of insert AS insert table 1 (id, col 1) select id+1, col 1 from inserted;

Обработка исключений Команда ROLLBACK указывает серверу остановить обработку модификации и запретить транзакцию. Существует также команда RAISEERROR, с помощью которой вы можете отправить сообщение об ошибке пользователю. TRY…CATCH

Обработка исключений сообщение об ошибке RAISERROR ("Error raised because of wrong data. ", -- Message text. 16, -- Severity. 1 -- State.); Severity – число от 0 до 25 Определенный пользователем уровень серьезности ошибки. 0 до 18 может указать любой пользователь. 19 до 25 могут быть указаны только sysadmin 20 до 25 считаются неустранимыми - соединение с клиентом обрывается и регистрируется сообщение об ошибке в журналах приложений и ошибок. State Целое число от 0 до 255. Отрицательные значения или значения больше 255 приводят к формированию ошибки. Если одна и та же пользовательская ошибка возникает в нескольких местах, то при помощи уникального номера состояния для каждого местоположения можно определить, в каком месте кода появилась ошибка.

Функции об ошибках Функция ERROR_LINE() возвращает номер строки, в которой произошла ошибка. Функция ERROR_MESSAGE() возвращает текст сообщения, которое будет возвращено приложению. Текст содержит значения таких подставляемых параметров, как длина, имена объектов или время. ERROR_NUMBER() возвращает номер ошибки. Функция ERROR_PROCEDURE() возвращает имя хранимой процедуры или триггера, в котором произошла ошибка. Эта функция возвращает значение NULL, если данная ошибка не была совершена внутри хранимой процедуры или триггера. ERROR_SEVERITY() возвращает уровень серьезности ошибки. ERROR_STATE() возвращает состояние.

Пример триггера CREATE TRIGGER Low. Credit ON Purchasing. Purchase. Order. Header AFTER INSERT AS BEGIN DECLARE @creditrating tinyint, @vendorid int ; SELECT @creditrating = v. Credit. Rating, @vendorid = p. Vendor. ID FROM Purchasing. Purchase. Order. Header p JOIN inserted i ON p. Purchase. Order. ID = i. Purchase. Order. ID JOIN Purchasing. Vendor v ON v. Vendor. ID = i. Vendor. ID ; IF @creditrating = 5 RAISERROR ("This vendor""s credit rating is too low to accept new purchase orders. ", 16, 1) ; END

Управление триггерами Отключение/включение триггера: – DISABLE/ENABLE TRIGGER trigger_name ON object_name Отключение/включение всех триггеров таблицы: – DISABLE/ENABLE TRIGGER ALL ON object_name Изменение триггера: – ALTER TRIGGER trigger_name … Удаление триггера: – DROP TRIGGER trigger_name

Активация/деактивация триггера DISABLE TRIGGER {trigger_name [ , . . . n ] | ALL } ON { object_name} ; ENABLE TRIGGER {trigger_name [ , . . . n ] | ALL } ON { object_name}

Применение триггеров Защита – Запрещение доступа в зависимости от значений данных Учет – Ведение журналов изменений Целостность данных – Сложные правила целостности – Сложная ссылочная целостность Производные данные – автоматическое вычисление значений

Типы триггеров Функция Триггер AFTER Триггер INSTEAD OF Сущности Таблицы и представления Количество триггеров на таблицу/представление Несколько на одно событие Один триггер на одно событие Нет ограничений INSTEAD OF UPDATE и DELETE нельзя определять для таблиц, на которые распространяются каскадные ограничения ссылочной целостности. Каскадные ссылки После следующих операций: Обработка ограничений. Выполнение Декларативные ссылочные действия. Создание таблиц inserted и deleted. Действие, запускающее триггер. Перед следующей операцией: Обработка ограничений. Вместо следующей операции: Действие, запускающее триггер. После следующих операций: Создание таблиц inserted и deleted.

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

DDL - триггеры Триггеры DDL, как и обычные триггеры, вызывают срабатывание хранимых процедур в ответ на событие. Срабатывают в ответ на разнообразные события языка определения данных (DDL). Эти события в основном соответствуют инструкциям языка Transact-SQL, начинающимся ключевыми словами CREATE, ALTER или DROP.

Задачи для DDL - триггеров Предотвратить внесение определенных изменений в схему базы данных. Выполнить в базе данных некоторые действия в ответ на изменения в схеме базы данных. Записывать изменения или события схемы базы данных. Триггеры DDL срабатывают только после выполнения соответствующих инструкций DDL. Триггеры DDL нельзя использовать в качестве триггеров INSTEAD OF.

CREATE TRIGGER trigger_name ON { DATABASE | ALL SERVER } { FOR | AFTER } { event_type | event_group } AS { sql_statement [ ; ] [ , . . . n ] [ ; ] }

Создание/удаление DDL-тр CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT "Database Created. " DROP TRIGGER ddl_trig_database ON ALL SERVER;

DDL - trigger CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT "You must disable Trigger "safety" to drop or alter tables!" ROLLBACK ;

Для одной инструкции Transact-SQL можно создать несколько триггеров DDL. Триггер DDL и инструкция, приводящая к его срабатыванию, выполняются в одной транзакции. Откат событий ALTER DATABASE, возникших внутри триггера DDL, невозможен. Триггеры DDL выполняются только после завершения инструкции Transact-SQL. Триггеры DDL нельзя использовать в качестве триггеров INSTEAD OF. Триггеры DDL не создают таблицы inserted и deleted.

Logon - trigger Триггеры входа выполняют хранимые процедуры в ответ на событие LOGON. Это событие вызывается при установке пользовательского сеанса с экземпляром SQL Server. Триггеры входа срабатывают после завершения этапа проверки подлинности при входе, но перед тем, как пользовательский сеанс реально устанавливается.

Logon - trigger CREATE TRIGGER trigger_name ON ALL SERVER { FOR| AFTER } LOGON AS { sql_statement }

Популярное
Категории

© 2024
exotop.ru - ExoTop - интернет и технологии