Триггеры. Отличие от других хранимых процедур

Триггеры Триггеры. Отличие от других хранимых процедур • Вызываются событием, нельзя вызвать вручную • Нельзя вызвать из внешнего интерфейса (клиентского приложения) • Не имеют параметров • Не могут быть функциями, т.е. возвращать значения Ссылочная целостность • Декларативная (create) • Активная (триггеры) Назначение триггеров • Поддержание ссылочной целостности • Передача пользователю предупреждения об ошибках или сообщений о данных • Отладка (т.е. отслеживание ссылок на указанные переменные и/или контроль над изменениями состояния этих переменных). • Аудит (например, регистрация информации о том, кто и когда внес те или иные изменения в определенные переменные отношения). • Измерение производительности (например, регистрация времени наступления или трассировка указанных событий в базе данных). • Проведение компенсирующих действий (например, каскадная организация удаления кортежа поставщика для удаления также соответствующих кортежей поставок). Из чего состоит триггер • собы т ие — операция в базе,вызвавшая триггер • Время вызова триггера, относительно операции • условие— это логическое выражение, которое должно принимать значениеTRUE для того, чтобы было выполнен триггер • дейст вие — тело триггерной процедуры • До      Триггеры по времени действия Для каскадного удаления Обработки ошибок Сохранение старых значений Отладка Шифрование данных • Вместо      Для каскадного удаления Обработки ошибок Сохранение старых значений Отладка Шифрование данных • После  Логирование изменений  Проведение компенсирующих действий (Удаление с очисткой справочника) Типы триггеров по способу обработки команд • FOR EACH ROW Для каждой обработанной строки • FOR EACH STATEMENT Для каждой обработанной команды Как в триггере узнать старые и новые данные? MySQL • OLD • NEW MS SQL server (transact SQL) • DELETED • INSERTED Что может использоваться в триггерах • Команды по манипулированию и определению данных • Процедурные расширения SQL • Работа с транзакциями • Сигналы (для сообщения об ошибках) Транзакции • Транзакция — это логическая единица работы; она начинается с выполнения операции BEGIN TRANSACTION и заканчивается операцией COMMIT (выполнение всех действий транзакции) или ROLLBACK(откат всех действий транзакции). Создание My SQL MS SQL Server CREATE [DEFINER = user] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body CREATE [ OR ALTER ] TRIGGER trigger_name ON { table | view } [ WITH [ ,...n ] ] trigger_time trigger_event [ NOT FOR REPLICATION ] AS trigger_body trigger_time: { BEFORE | AFTER } trigger_time : { FOR | AFTER | INSTEAD OF } trigger_event: { INSERT | UPDATE | DELETE } trigger_event: {[ INSERT ][ , ] [UPDATE ][ , ] [DELETE ]} trigger_order: { FOLLOWS | PRECEDES } other_trigger_name trigger_body: BEGIN ... END trigger_body: AS … GO Пример реализации каскадного удаления student id_student id_gr surname name patronym int int varchar(30) varchar(30) varchar(30) st_group id_gr int number_gr varchar(8) My SQL MS SQL Server BEFORE INSTEAD OF delimiter // Create trigger my_trigger before delete on st_group FOR EACH ROW Begin delete from student where id_gr =OLD.id_gr; End// delimiter ; Create trigger my_trigger instead of delete on st_group as begin delete * from student where id_gr in (select id_gr from deleted) delete * from st_group where id_gr in (select id_gr from deleted) end go student id_student id_gr surname name patronym Пример реализации подсчёта int int varchar(30) varchar(30) varchar(30) st_group id_ gr int number_gr varchar(8) stud_count tinyint My SQL MS SQL Server delimiter // Create trigger my_trigger after update on student FOR EACH ROW Begin Update st_group Set stud_count=stud_count-1 where id_gr =OLD.id_gr; Update st_group Set stud_count=stud_count+1 where id_gr =NEW.id_gr; End// Create trigger my_trigger after update on student as begin update st_group Set stud_count=stud_count+ count(id_student) From inserted where st_group.id_gr=inserted.id_gr Group by inserted.id_gr update st_group Set stud_count=stud_countcount(id_student) student id_student id_gr surname name patronym int int varchar(30) varchar(30) varchar(30) My SQL Транзакции в триггерах st_group id_ gr int number_gr varchar(8) stud_count tinyint MS SQL Server Нельзя начинать Create trigger my_trigger откатывать или завершать after insert on student транзакции в триггерах as MySQL begin If exists (select * from inserted where surname ='' ) rollback transaction; end go Удаление • DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name Изменение триггера Транзакции MySQL • START TRANSACTION [transaction_characteristic [, transaction_characteristic] ...] • transaction_characteristic: { WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY } • BEGIN [WORK] • COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] • ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] • SET autocommit = {0 | 1} Сигнал • СИГНАЛ - это способ «вернуть» ошибку из процедуры. • SIGNAL предоставляет информацию об ошибке обработчику, внешней части приложения или клиенту. Кроме того, он обеспечивает контроль характеристик ошибки (номер ошибки, значение SQLSTATE, сообщение) Сигнал синтаксис • SIGNAL condition_value [SET signal_information_item [, signal_information_item] ...] • condition_value: { SQLSTATE [VALUE] sqlstate_value | condition_name } • signal_information_item: condition_information_item_name = simple_value_specification • condition_information_item_name: { CLASS_ORIGIN | SUBCLASS_ORIGIN | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME } SQLSTATE • Class = '00' (success) • Class = '01' (warning) Значение системной переменной warning_count увеличивается. SHOW WARNINGS показывает сигнал. Обработчики SQLWARNING ловят сигнал. Предупреждения не могут быть возвращены из хранимых функций, потому что оператор RETURN, который вызывает возврат функции, очищает область диагностики. оператор RETURN очищает все предупреждения, которые могли там присутствовать (и сбрасывает warning_count в 0). • Class = '02' (not found) Обработчики NOT FOUND ловят сигнал. Нет влияния на курсоры. Если сигнал не обрабатывается в хранимой функции, выполнение заканчивается. • Class > '02' (exception) Если сигнал не обрабатывается в хранимой функции, выполнение заканчивается. • Class = '40' Рассматривается как обычное исключение. Чтобы указать общее значение SQLSTATE, используйте ‘45000’, что означает «необработанное пользовательское исключение». Сигнал пример Пример триггера с сигналом об ошибке delimiter // use test// create table trigger_test ( id int not null )// drop trigger if exists trg_trigger_test_ins // create trigger trg_trigger_test_ins before insert on trigger_test for each row begin declare msg varchar(255); if new.id < 0 then set msg = concat('MyTriggerError: Trying to insert a negative value in trigger_test: ', cast(new.id as char)); signal sqlstate '45000' set message_text = msg; end if; end // delimiter ; Вызов триггера • insert into trigger_test values (2); • insert into trigger_test values (-1); Курсоры • • • • • • • • Необязательный результат Только чтение Только в одном направлении DECLARE cursor_name CURSOR FOR select_statement OPEN cursor_name FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ... CLOSE cursor_name Если больше нет строк, возникает условие «Нет данных» со значением SQLSTATE «02000». Чтобы обнаружить это условие, можно настроить обработчик для него (или для условия NOT FOUND) Курсоры пример