Алексей Паутов - MySQL: руководство профессионала Страница 35

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

OLD и NEW представляют собой MySQL-расширения триггеров.

Используя конструкцию BEGIN … END, Вы можете определять триггер, который выполняет много инструкций. Внутри блока BEGIN Вы также можете использовать другой синтаксис, который позволяется внутри сохраненных подпрограмм, типа условных выражений и циклов. Однако, точно как для сохраненных подпрограмм, если Вы используете программу mysql, чтобы определить триггер, необходимо переопределить операторный разделитель mysql так, чтобы Вы могли использовать ; как операторный разделитель внутри описания триггера. Следующий пример иллюстрирует эти моменты. Это определяет триггер UPDATE, который проверяет новое значение, которое нужно использовать для модифицирования каждой строки, и изменяет значение, чтобы оставаться внутри диапазона от 0 до 100. Это должно быть триггером типа BEFORE, потому что значение должно быть проверено прежде, чем оно используется, чтобы модифицировать строку:

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 ;

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

Имеются некоторые ограничения на то, что может появляться в инструкциях:

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

Триггер не может использовать инструкции, которые явно или неявно начинают или заканчивают транзакцию, типа START TRANSACTION, COMMIT или ROLLBACK.

До MySQL 5.0.10 триггер не может содержать прямые ссылки к именам таблиц.

MySQL обрабатывает ошибки в выполнении триггеров следующим образом:

Если проблемы с триггером BEFORE, операции на соответствующей строке просто не выполняются.

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

Триггер AFTER выполнен только, если триггер BEFORE и операция со строкой (вместе!) выполняются успешно.

Ошибка в триггере BEFORE или AFTER вызывает сбой всей инструкции, которая вызвала триггер.

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

6.4. MySQL 5 FAQ по триггерам

6.4.1: Имеется ли форум для обсуждения триггеров в MySQL?

Да. http://forums.mysql.com/list.php?99.

6.4.2: MySQL 5.1 имеет триггеры операторного уровня или уровня строки?

В MySQL 5.1 все триггеры FOR EACH ROW, то есть триггер активизирован для каждой строки, которая вставлена, модифицируется или удалена. MySQL 5.1 не поддерживает использование триггеров FOR EACH STATEMENT.

6.4.3: Имеется ли любое значение по умолчанию для триггеров?

Неявно. MySQL имеет специфическое специальное поведение для некоторых столбцов TIMESTAMP, а также для столбцов, которые определены, используя AUTO_INCREMENT.

6.4.4: Как управлять триггерами в MySQL?

В MySQL 5.1 триггер может быть создан, используя инструкцию CREATE TRIGGER, а удален инструкцией DROP TRIGGER.

Информация относительно триггеров может быть получена, запрашивая таблицу INFORMATION_SCHEMA.TRIGGERS.

6.4.5: Имеется ли способ просмотреть все триггеры в конкретной базе данных?

Да. Вы можете получать распечатку всех триггеров, определенных в базе данных dbname, запросом к таблице INFORMATION_SCHEMA.TRIGGERS примерно так:

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE,

ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS

WHERE TRIGGER_SCHEMA='dbname';

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

6.4.6: Где хранятся триггеры?

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

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

6.4.7: Может триггер вызывать сохраненную процедуру?

Да.

6.4.8: Может триггер обращаться к таблицам?

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

6.4.9: Может триггер вызывать внешнюю прикладную программу через UDF?

Нет, не в настоящее время.

6.4.10: Может триггер модифицировать таблицы на удаленном сервере?

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

Глава 7. Views

Views (включая обновляемые views) выполнены в MySQL Server 5.0. Views доступны в двоичных выпусках 5.0.1 и выше.

Эта глава обсуждает следующие темы:

Создание или изменение views через CREATE VIEW или ALTER VIEW

Удаление views командой DROP VIEW

Обсуждение ограничений на использование views дано в разделе " 11.4. Ограничения на Views".

Чтобы использовать views, если выполнен апгрейд до MySQL 5.0.1, Вы должны обновить таблицы предоставления привилегий, чтобы они содержали привилегии, связанные с view.

Метаданные относительно views могут быть получены из таблицы INFORMATION_SCHEMA.VIEWS или используя инструкцию SHOW CREATE VIEW.

7.1. Синтаксис ALTER VIEW

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

[DEFINER = {user | CURRENT_USER}]

[SQL SECURITY {DEFINER | INVOKER}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

Эта инструкция изменяет определение существующего view. Синтаксис для CREATE VIEW подобен этому. Эта инструкция требует привилегий CREATE VIEW и DROP для этого view, а также некоторой привилегии для каждого столбца, упоминаемого в инструкции SELECT.

Эта инструкция была добавлена в MySQL 5.0.1. Предложения DEFINER и SQL SECURITY могут использоваться с MySQL 5.0.16, чтобы определить контекст защиты, который нужно использовать при проверке привилегий доступа при вызове view. Подробности изложены в разделе "7.2. Синтаксис CREATE VIEW ".

7.2. Синтаксис CREATE VIEW

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

[DEFINER = {user | CURRENT_USER}]

[SQL SECURITY {DEFINER | INVOKER}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

Эта инструкция создает новый view или заменяет существующий, если дано предложение OR REPLACE. Инструкция SELECT select_statement обеспечивает определение view. Инструкция может выбирать из основных таблиц или других views.

Эта инструкция требует привилегии CREATE VIEW для view и некоторой привилегии для каждого столбца, выбранного инструкцией SELECT. Для столбцов, используемых в другом месте в инструкции SELECT, Вы должны иметь привилегию SELECT. Если присутствует предложение OR REPLACE, Вы должны также иметь привилегию DROP для view.