Триггеры в SQL 3 Триггеры иногда называемые правилами

Триггеры в SQL 3 Триггеры, иногда называемые правилами типа событие-условиедействие (правилами ЕСА) • Триггеры применяются только при наступлении конкретных событий, определенных программистом БД, обычно при вставке, удалении или изменении отдельного отношения. Во многих системах SQL таким событием считается также конец транзакции. • Вместо немедленного предотвращения вызвавшего события триггер проверяет заданное условие. Если оно не выполняется, в ответ на событие не выполняется никаких действий, связанных с триггером. • Если условие триггера выполняется, связанное с ним действие выполняется системой. Это действие состоит либо в предотвращении события, либо в отмене его результата (например, в удалении вставленного кортежа). Фактически таким действием может быть любая последовательность операций БД, возможно даже операций, никак не связанных с событием, вызвавшим срабатывание триггера.

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

Рассмотрим сначала пример, в котором триггер выполняется один раз для каждого изменяемого кортежа. Запишем триггер для таблицы Продюсер(имя, адрес, серт#, доход) относящийся к изменениям атрибута «доход» . Он предназначен для возобновления любой попытки снизить чистый доход президента студии. • CREATE TRIGGER Net. Worth. Trigger • AFTER UPDATE OF доход ON продюсер • REFERENCING • OLD AS Old. Tuple, • NEW AS New. Tuple • WHEN(Old. Tuple. доход > New. Tuple. доход) • UPDATE продюсер • SET доход = Old. Tuple. доход • WHERE серт# = New. Tuple. серт# • FOR EACH ROW

• • • Согласно строке (2), действие выполняется после события, вызывающего срабатывание триггера, что выражено ключевым словом AFTER. Возможны другие варианты: BEFORE означает, что условие пункта WHEN проверяется перед наступлением события, вызывающего срабатывание триггера. Если условие истинно, выполняется действие триггера. Тогда событие, вызвавшее изменение, выполняется независимо от того, было ли истинным условие. INSTEAD OF – действие выполняется (при выполнении условия WHEN), а инициирующее триггер событие не выполняется вообще. Помимо UPDATE, инициировать триггер могут события INSERT и DELETE. Предложение OF доход в строке (2) применяется для событий UPDATE и означает, что событием является только изменение атрибутов, перечисленных после ключевого слова OF. Применять OF с событиями INSERT или DELETE запрещено; эти события имеют смысл только для целых кортежей. Если вызывающим срабатывание триггера событием является вставка, слова NEW AS используются для именования вводимого кортежа, а OLD AS запрещены. И наоборот, при удалении слова OLD AS применяются для именования удаляемого кортежа, а слова NEW AS запрещены. При удалении ключевых слов FOR EACH ROW из строки (10) триггер уровня строки становится триггером уровня предложения. Последний выполняется один раз для каждого предложения, генерирующего одно или более событий, вызывающих срабатывание триггера. Например, если вся таблица изменяется с помощью SQLпредложения, триггер уровня предложения выполняется только один раз, а триггер уровня кортежа выполняется по одному разу для каждого кортежа. В триггере уровня предложения невозможно прямо ссылаться на старые и новые кортежи, как это делалось в строках (4) и (5), а можно ссылаться на множество старых кортежей (удаленных или старых версий измененных кортежей) и на множество новых кортежей (вставленных кортежей или новых версий измененных кортежей) как на два отношения. Значит, вместо строк (4) и (5) в примере мы пишем соответственно OLD_TABLE AS <имя старого отношения> NEW_TABLE AS <имя отношения, содержащего новые кортежи>.

Пример. Допустим, нужно предотвратить падение среднего чистого дохода администраторов ниже 500 тыс. дол. Такое ограничение может быть нарушено вставкой, удалением или изменением в столбце «доход» таблицы Продюсер(имя, адрес, серт#, доход) Необходимо ввести по одному триггеру для каждого из этих событий. Ниже приведен триггер для изменения. Триггеры для вставки и удаления похожи на него, хотя и более просты • CREATE TRIGGER Avg. Net. Worth. Trigger • INSTEAD OF UPDATE OF доход ON продюсер • REFERENCING • OLD_TABLE AS Old. Stuff, • NEW_TABLE AS New. Stuff • WHEN(500000<= • (SELECT AVG(доход) • FROM ((продюсер EXCEPT Old. Stuff) UNION New. Stuff) ) • DELETE FROM продюсер • WHERE (имя, адрес, серт#, доход) IN Old. Stuff; • INSERT INTO продюсер • (SELECT * FROM New. Stuff);

Поддержка триггеров включена, начиная с My. SQL 5. 0. 2. • • • Имена триггеров существуют в пространстве имен схемы. Это означает, что все триггеры должны иметь уникальные имена внутри схемы. Триггеры в различных схемах могут иметь то же самое имя. В дополнение к требованию, чтобы имя триггера было уникальным для схемы, имеются другие ограничения на типы триггеров, которые можно создавать. Вы не можете иметь два триггера для таблицы, которые имеют то же самое событие и время активации. Например, Вы не можете определять два триггера типа BEFORE INSERT или AFTER UPDATE для таблицы. Это редко должно быть значительным ограничением, поскольку запросто можно определить триггер, выполняющий много инструкций с помощью конструкции BEGIN. . . END после FOR EACH ROW. Ключевые слова OLD и NEW дают возможность Вам обратиться к столбцам в строках, на которые воздействует триггер OLD и NEW не чувствительны к регистру. В триггере INSERT может использоваться только NEW. col_name : не имеется никакой старой строки. В триггере DELETE не ожидается никакой новой строки, так что может использоваться исключительно OLD. col_name. В триггере UPDATE Вы можете использовать OLD. col_name, чтобы обратиться к столбцам строки прежде, чем они изменятся, и NEW. col_name , чтобы обратиться к ним уже после внесения изменений. Столбец, именованный OLD только для чтения. Вы можете обратиться к этому столбцу (если Вы имеете привилегию SELECT, но не изменяете его. Столбец, именованный NEW может упоминаться, если Вы имеете привилегию SELECT для него. В триггере BEFORE Вы можете также изменять значение с SET NEW. col_name = value, если Вы имеете привилегию UPDATE для этого. Это означает, что Вы можете использовать триггер, чтобы изменить значения, которые будут вставлены в новую строку, или использовать это, чтобы модифицировать строку. В триггере BEFORE значение NEW для столбца AUTO_INCREMENT: 0, а не автоматически сгенерированный порядковый номер, который будет сгенерирован, когда новая запись фактически вставлена. mysql> delimiter // mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account -> FOR EACH ROW -> BEGIN -> IF NEW. amount < 0 THEN SET NEW. amount = 0; -> ELSEIF NEW. amount > 100 THEN SET NEW. amount = 100; -> END IF; -> END; // mysql> delimiter ;

Курсор – это объект, используемый в приложениях для размещения результатов запроса и иногда для изменения данных в таблицах, на которые ссылается запрос Оператор определения курсора имеет вид • DECLARE имя курсора [INSENSITIVE] [SCROLL] CURSOR • FOR {SELECT оператор • [предложение для обновления]} • | {подготовленный оператор} • предложение для обновления: : = • FOR {READ ONLY | UPDATE [OF имя столбца. , . . ]} • В отличие от базовой таблицы или представления, строки открытого курсора имеют определенный порядок. Для того чтобы установить необходимый порядок строк, можно использовать в операторе SELECT предложение ORDER BY. • Если задано предложение INSENSITIVE, то содержимое курсора будет зафиксировано сразу после открытия курсора. Любые изменения, производимые другими операторами над данными курсора, будут игнорироваться курсором до тех пор, пока он открыт. Свойством INSENSITIVE могут обладать только курсоры типа «только чтение» . • Если задано предложение SCROLL, то необязательно извлекать строки в том порядке, который задан при открытии. SCROLL позволяет извлекать строки в произвольном порядке. Этим свойством могут обладать только курсоры типа «только чтение» .

Курсоры могут быть двух типов – «обновляемые» и «только для чтения» . «Обновляемый курсор можно преобразовать в курсов типа «только для чтения» с помощью предложения для обновления, но не наоборот. Предложение для обновления может относиться к отдельным столбцам. В качестве предложения для обновления можно использовать только FOR UPDATE, если курсор обновляемый в принципе. Для того, чтобы быть обновляемым в принципе, курсор должен удовлетворять следующим условиям: • Нельзя объявлять курсор как INSESITIVE, SCROLL или с предложением ORDER BY. • Курсор должен базироваться на одной и только одной простой таблице. Другими словами нельзя использовать соединения таблиц. • Курсор должен содержать один и только один запрос, т. е. недопустимы предложения UNION, EXCEPT или INTERSECT. • Если простая основная таблица курсора является представлением, то это представление должно быть обновляемым. • Предложение SELECT, определяющее содержимое курсора, должно иметь ссылки только на столбцы, а не на полученные каким-либо способом значения или функции агрегирования. Ни на один столбец нельзя делать более одной ссылки. • Запрос не должен содержать предложения GROUP BY или HAVING. • Запрос не должен содержать предложения DISTINCT.

• Следующий оператор определяет пролистываемый, нечувствительный курсор под названием «Клиенты_из_Ростова» , который содержит список всех клиентов, проживающих в Ростове. Так курсор является пролистываемым и нечувствительным, для него автоматически устанавливается признак «только чтение» . • DECLARE Клиенты_из_Ростова INSENSITIVE SCROLL CURSOR • FOR SELECT таб_ном, имя • FROM Клиенты • WHERE Город=’Ростов’;

Чтобы подготовить курсор для дальнейшего использования, предварительно определенный с помощью оператора DECLARE CURSOR, используется оператор OPEN: • OPEN имя курсора [USING источник значений]; • Источник значений: : = • Список параметров При выполнении этого оператора все параметры и переменные заполняются их текущими значениями. Запрос, определяющий курсор, выполняется, и данные извлекаются. Эти данные теперь готовы для построчной обработки, осуществляемой оператором FETCH. Предложение USING используется только в динамическом SQL. Оператор CLOSE имя курсора закрывает открытый в данный момент курсор.

Операции, не использующие курсоров Ниже перечислены операторы обработки, для которых не требуется использовать курсоры: • • • «единичный SELECT» INSERT UPDATE (кроме формы CURRENT) DELETE (тоже кроме формы CURRENT) Рассмотрим примеры для каждого из этих операторов. Единичный SELECT. Получить статус и город для поставщика, чей номер поставки задан базовой переменной Giv# EXEC SQL SELECT STATUS, CITY INTO : RANK, : CITY FROM S WHERE S# = Giv# ; Термин «единичный SELECT» используется для обозначения выражения выборки, значением которого будет таблица, содержащая не более одной строки. Если в данном примере существует ровно одна строка в таблице S, удовлетворяющая условию WHERE, то значения STATUS и CITY из этой строки в соответствии с запросом будут присвоены базовым переменным RANK и CITY, а значение SQLSTATE будет равно 00000. Если в таблице S нет ни одной строки, удовлетворяющей условию WHERE, то переменной SQLSTATE будет присвоено значение 02000; а если таких строк больше одной, то будет сгенерирована ошибка и переменная SQLSTATE будет содержать код ошибки.

• INSERT. Вставить новую деталь в таблицу Р (номер детали, ее название и вес определены базовыми переменными P#, PNAME, PWT соответственно; цвет и город неизвестны) • EXEC SQL INSERT • INTO P (P#, PNAME, COLOR, WEIGHT, CITY) • VALUES (: P#, : PNAME, DEFAULT, : PWT, DEFAULT) ; • UPDATE. Увеличить статус всех поставщиков из Ростова на значение, определенное базовой переменной Inc. • EXEC SQL UPDATE S • SET STATUS = STATUS + : Inc • WHERE CITY = ‘Ростов’ ; Если нет строк поставщиков, удовлетворяющих условию WHERE, переменной SQLSTATE присваивается значение 02000. • DELETE. Удалить все поставки для поставщиков из города, определенного базовой переменной CITY • EXEC SQL DELETE • FROM SP • WHERE : CITY = • (SELECT CITY FROM S WHERE S. S# = SP. S#) ; И снова, если нет строк, удовлетворяющих условию WHERE, , переменной SQLSTATE присваивается значение 02000

Оператор FETCH Этот оператор извлекает значения из одной строки курсора в список целевых спецификаций (возвращаемых переменных), при этом в первую переменную записывается первый столбец курсора, во вторую – второй и т. д. оператор имеет следующий синтаксис: • FETCH [[ориентация] FROM] • Имя курсора INTO целевая спецификация. , . . • Ориентация: : = • NEXT | PRIOR | FIRST | LAST | • {ABSOLUTE | RELATIVE спецификация значения} При первом открытии курсор устанавливается перед первой строкой. Оператор FETCH изменяет положение курсора, затем копирует значения из строки, куда был помещен курсор в список целевых спецификаций. Поскольку с помощью этого оператора можно определить текущее положение курсора относительно набора строк, его можно использовать вместе с операторами DELETE или UPDATE, которым требуется информация о положении курсора, чтобы вносить изменения в его содержимое (обновляемый курсор) и, таким образом, в связанную с ним таблицу.

Переменная ориентация определяет положение курсора. Спецификация значения – это переменная типа EXACT NUMERIC, изменяющаяся от нуля до назначенного целого. Функции значений переменной ориентация определены следующим образом: • Если курсор расположен перед первой строкой, то значение NEXT вызывает перемещение курсора на первую строку. Если курсор расположен на какой-либо другой строке, то он перемещается на следующую. Если курсор расположен последней строки, то NEXT инициирует условный код «нет данных» . • Если курсор расположен последней строки, то значение PRIOR вызывает перемещение курсора на эту строку. Если курсор расположен на какой-либо другой строке, кроме первой, то он перемещается на предыдущую. Если курсор расположен на первой строке или перед ней, то PRIOR инициирует условный код «нет данных» . • Значение FIRST вызывает перемещение курсора на первую строку. • Значение LAST вызывает перемещение курсора на последнюю строку. • Значение ABSOLUTE N вызывает перемещение курсора на N-ю строку. Если N больше общего количества строк, то инициируется условный код «нет данных» . • Значение RELATIVE N вызывает перемещение курсора на N строк вперед, если аргумент положителен, и на N строк назад, если – отрицателен. Если при этом курсор оказывается выше первой или ниже последней строки, то инициируется условный код «нет данных» .

Значение NEXT является значением по умолчанию, если положение курсора не определено, и, в любом случае, единственной возможностью его определения, если курсор не был объявлен как SCROLL. Приведенный ниже оператор FETCH перемещает курсор «продажи_сегодня» на три строки назад и считывает данные в список целевой спецификации. Предполагается, что курсор является пролистываемым. • FETCH RELATIVE – 3 • FROM продажи_сегодня • INTO : имя, : таб_ном, : число_продаж;

Хранимые процедуры • С точки зрения приложений, работающих с БД, хранимые процедуры (Stored Procedure) – это подпрограммы, которые выполняются на сервере. По отношению к БД – это объекты, которые создаются и хранятся в БД. Они могут быть вызваны из клиентских приложений. При этом одна процедура может быть использована в любом количестве клиентских приложений, что позволяет сэкономить трудозатраты на создание прикладного программного обеспечения и эффективно применять стратегию повторного использования кода. Наконец, использование хранимых процедур сокращает объем пересылаемой по сети информации. • Хранимые процедуры могут быть активизированы не только пользовательскими приложениями, но и триггерами. • Хранимые процедуры пишутся на специальном встроенном языке программирования, они могут включать любые операторы SQL, а также некоторый набор операторов, управляющих ходом выполнения программ (операторы процедурного языка). В коммерческих СУБД для написания текстов хранимых процедур используются собственные языки программирования, так, в СУБД Oracle для этого используется PL/SQL, а в MS SQL SERVER и System 11 фирмы Sybase используется язык Transact SQL. В последних версиях Oracle используется язык Java для написания хранимых процедур.

По умолчанию выполнить хранимую процедуру может только ее владелец, которым является владелец БД, и создатель хранимой процедуры. Однако владелец хранимой процедуры может делегировать права на ее запуск другим пользователям. В MS SQL Server хранимая процедура создается оператором: • CREATE PROC[EDURE] <имя_процедуры> [; <версия>] • [{@параметр1 тип_данных] • [VARYING] [=<значение_по_умолчанию>] [OUTPUT] • [, . Параметр. N…] • [WITH • {RECOMPILE • | ENCRYPTION • |RECOMPILE, ENCRYPTION}] • [FOR REPLICATION] • AS • Тело процедуры Здесь необязательное ключевое слово VARYING используется только с параметрами типа курсора и говорит о том, что содержимое параметра может меняться. Ключевое слово RECOMPILE определяет режим компиляции создаваемой хранимой процедуры. Если задано ключевое слово RECOMPILE, то процедура будет перекомпилироваться каждый раз, когда она будет вызываться на исполнение. Это может замедлить выполнение процедуры. Однако, с другой стороны, если данные, обрабатываемые хранимой процедурой, настолько динамичны, что предыдущий план выполнения, составленный при ее первом вызове, может оказаться неэффективен при последующих вызовах, то стоит применять данный параметр. Ключевое слово ENCRYPTION определяет режим, при котором исходный текст хранимой процедуры не сохраняется в БД. Часто такой режим применяется, когда вы ставите готовую базу заказчику и не хотите, чтобы исходные тексты разработанных вами хранимых процедур были доступны администратору БД.

Хранимая процедура может быть вызвана несколькими способами. Простейший способ – это использование оператора EXEC: • EXEC <имя процедуры> <значение_входного_параметра 1>… • <имя_переменной_для_выходного_параметра 1>… При этом все входные и выходные параметры должны быть заданы обязательно и в том же порядке, в котором они определены в процедуре. Однако если в процедуре определены значения входных параметров по умолчанию, то при запуске могут быть указаны значения не всех параметров. В этом случае оператор вызова процедуры может быть записан в следующем виде: • EXEC <имя процедуры> <Имя_параметра 1>= <значение параметра 1>… • <имя_параметра. N>=<значение параметра. N> • Если мы при вызове процедуры задаем параметры по именам, то необязательно приводить их в том порядке, в котором они описаны при создании процедуры.

• • • • • • • • • • CREATE PROCEDURE Ren. Questions. Proc -- Перенумерация вопросов @Code_Theme int--Код темы – параметр процедуры as declare @i int, --объявление локальных переменных @Code_Quest int, @Text char BEGIN /* объявляется курсор, содержащий код вопроса данной темы */ declare Qu cursor for select Code_Quest from Questions where Code_Theme=@Code_Theme open Qu --открывается курсор BEGIN TRAN --начало транзакции set @i=0 -- начальное значение счетчика вопросов /* осуществляем перебор вопросов FETCH NEXT FROM Qu into @Code_Quest WHILE (@@FETCH_STATUS = 0) begin set @i=@i+1 --set @Text=str(@i) & str(@Code_Quest) --RAISERROR 20000 @Text /* изменяем номер вопроса при помощи оператора обновления */ Update Questions set Quest_No=@i where Code_Quest=@Code_Quest FETCH NEXT FROM Qu into @Code_Quest end if @i=0 --если значение счетчика не изменилось, значит по данной --теме нет вопросов. Это скорей всего связано с тем, что --указан код несуществующей темы. В этом случае генерируем ошибку, выводящую соответствующее -сообщение. begin RAISERROR 20000 'Указан код несуществующей темы ' end CLOSE Qu --курсор закрывается IF (@@error!=0) --если ошибка, выполняется откат транзакции BEGIN RAISERROR 20000 '? ? ' ROLLBACK TRAN RETURN(1) END COMMIT TRAN --если ошибки нет, транзакция завершается END GO

• • • Синтаксис хранимых процедур в My. SQL Хранимая процедура является процедурой или функцией. Хранимые процедуры созданы командами CREATE PROCEDURE и CREATE FUNCTION. Процедура вызывается, используя инструкцию CALL, и может только передавать обратные значения, используя переменные вывода. Функция может быть названа точно так же, как и любая другая функция языка (то есть, вызывая имя функции), и может возвращать скалярное значение. Хранимые процедуры, разумеется, могут вызывать другие хранимые процедуры. Чтобы вернуть значение из процедуры, использующей параметр OUT или INOUT, передайте параметр посредством переменной пользователя, и затем проверьте значение переменной после возврата из процедуры. Если Вы вызываете процедуру изнутри другой хранимой процедуры или функции, Вы можете также передавать стандартный параметр или локальную стандартную переменную как параметр IN или INOUT. Для параметра INOUT инициализируйте значение перед его передачей процедуре. Следующая процедура имеет параметр OUT, который процедура устанавливает в текущую (актуальную) версию сервера, и значение INOUT, которое процедура увеличивает: CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT) BEGIN # Set value of OUT parameter SELECT VERSION() INTO ver_param; # Increment value of INOUT parameter SET incr_param = incr_param + 1; END; Перед вызовом процедуры, инициализируйте переменную, которая будет передана как параметр INOUT. После вызова процедуры, значения в двух переменных будут установлены или изменены: mysql> SET @increment = 10; mysql> CALL p(@version, @increment); mysql> SELECT @version, @increment;