Операторы SQL реализующие транзакции

START TRANSACTION | BEGIN [WORK] — начало транзакции

COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] – фиксация транзакции

ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] – откат транзакции

SET AUTOCOMMIT = {0 | 1} – настройка режима автофиксации транзакции

 

 

Тема 12: Триггеры

 

Введение

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

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

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

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

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

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

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

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

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

· накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;

· поддержка репликации.

 

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

Основной формат команды CREATE TRIGGER показан ниже:

<Определение_триггера>::=

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

BEFORE | AFTER <триггерное_событие>

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

[REFERENCING

<список_старых_или_новых_псевдонимов>]

[FOR EACH { ROW | STATEMENT}]

[WHEN(условие_триггера)]

<тело_триггера>

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

Выполняемые триггером действия задаются для каждой строки (FOR EACH ROW), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT).

Обозначение <список_старых_или_новых_псевдонимов> относится к таким компонентам, как старая или новая строка (OLD / NEW) либо старая или новая таблица (OLD TABLE / NEW TABLE). Ясно, что старые значения не применимы для событий вставки, а новые – для событий удаления.

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

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

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

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

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

Триггеры в MySQL

Поддержка триггеров включена в MySQL начиная с версии 5.0.2. Триггер – то именованный объект БД ассоциируемый с таблицей и активизируемый при возникновении отдельного события для таблицы.

Следующие пример демонстрирует создание таблицы и INSERT триггера (триггера добавления). Триггер суммирует значения одного из добавляемых в таблицу столбцов.

CREATE TABLE account (acct_num INT, amount DECIMAL(10,2))

CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;

Оператор CREATE TRIGGER

CREATE [DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

Это выражение создает новый триггер. Для использования триггера необходима SUPER привилегия. Триггер связывается с таблицей с именем tbl_name, которая должна быть постоянной таблицей. Триггер не может быть создан для временной таблицы или представления.

Выражение DEFINER определяет права, которые получает триггер при активизации

trigger_time - время срабатывания (активизации) триггера. Возможны значения BEFORE или AFTER, которые определяют время выполнения триггера (до или после события вызвавшего триггер).

trigger_event – определяет какое событие активизирует триггер. События могут быть следующие:

INSERT: Триггер активизируется всякий раз когда в таблицу добавляется новая строка. Например, при выполнении операторов: INSERT, LOAD DATA, и REPLACE.

UPDATE: Триггер активизируется всякий раз когда какая либо строка таблицы изменяется. Например, при выполнении оператора UPDATE.

DELETE: Триггер активизируется всякий раз когда из таблицы удаляется строка. Например, при выполнении операторов DELETE и REPLACE. Однако операторы DROP TABLE и TRUNCATE не активизируют триггер удаления, потому, что эти операторы не используют удаление строк.

Важно понимать, что события trigger_event не являются буквальными ссылками на операторы SQL, которые активизируют триггер. Например, INSERT триггер (триггер добавления) срабатывает не только по оператору INSERT, но и по оператору LOAD DATA, поскольку оба эти оператора используют вставку строк в таблицу.

Рассмотрим пример использования оператора INSERT INTO... ON DUPLICATE KEY UPDATE...: BEFORE INSERT триггер будет срабатывать при каждом добавлении строки, а триггеры AFTER INSERT или BEFORE UPDATE и AFTER UPDATE будут активизироваться в зависимости от возникновения ситуации дублирования ключа (DUPLICATE KEY). При дублировании ключа триггер AFTER INSERT не активизируется, но активизируются триггеры BEFORE UPDATE и AFTER UPDATE

Для таблицы не может быть создано два триггера, у которых совпадают и время (trigger_time) и событие (trigger_event). Например, нельзя создать два BEFORE UPDATE триггера для одной и той же таблицы, но можно создать BEFORE UPDATE и BEFORE INSERT триггеры, или BEFORE UPDATE и AFTER UPDATE триггеры.

trigger_stmt – это оператор исполняемый при активизации триггера. Для использования нескольких оператором необходимо использовать конструкцию BEGIN... END.

 

 

Тема 13: Представления

Введение

Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.
Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.

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

Преимущества использования представлений:

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

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

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

Представления (включая обновляемые представления) включены в версии MySQL начиная с 5.0.

Информацию о структуре представлений можно посмотреть в таблице INFORMATION_SCHEMA.VIEWS, а также, используя оператор SHOW CREATE VIEW.