SQL Хранимые процедуры и триггеры Хранимая процедура-

SQL. Хранимые процедуры и триггеры

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

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

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

Реализация хранимых процедур Хранимые процедуры обычно создаются с помощью языка SQL или конкретной его реализации в выбранной СУБД. Например, для этих целей в СУБД Microsoft SQL Server существует язык Transact-SQL , в Oracle Database— PL/SQL, в Interbase и Firebird - PSQL, в Postgre. SQL — PL/pg. SQL, PL/Tcl, PL/Perl, в IBM DB/2 — SQL/PL и My. SQL достаточно близко следует стандарту SQL: 2003, её язык похож на SQL/PL. В некоторых СУБД возможно использование хранимых процедур, написанных на любом языке программирования, способном создавать независимые исполняемые файлы, например, на C++ или Delphi. В терминологии Microsoft SQL Server такие процедуры называются расширенными хранимыми процедурами и являются просто функциями, содержащимися в DLL Win 32. В Interbase и Firebird для функций, вызываемых из DLL/SO, определено другое название - UDF (User Defined Function).

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

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

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

для журнализации всех операций, которые изменяют значение заработной платы в таблице служащих: CREATE TRIGGER log_salupdate BEFORE UPDATE OF salary ON employees REFERENCING OLD ROW as oldrow NEW ROW as newrow FOR EACH ROW INSERT INTO log_table VALUES (CURRENT_USER, oldrow. salary, newrow. salary) Пример (MS SQL Server)

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

Триггеры создаются с помощью оператора CREATE TRIGGER

Генерация триггеров в ERWin

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

Триггер ссылочной целостности

Связь «Сотрудник» - «Загран-паспорт» (MS SQL-сервер)

Фрагмент ER-модели

Ограничения целостности связи

В общем случае будет создано 6 триггеров – на каждое действие (INSERT, UPDATE, и DELETE) с записями родительской таблицы(PARENT_TABLE) и порожденной таблицы (CHILD_TABLE) Может быть меньше – если используется NONE

create trigger t. U_сотрудник on сотрудник for UPDATE as …. /* сотрудник владеет загран_паспорт ON PARENT UPDATE CASCADE */ if update(код_сотрудника) begin if @numrows = 1 begin select @insкод_сотрудника = inserted. код_сотрудника from inserted update загран_паспорт set загран_паспорт. код_сотрудника = @insкод_сотрудника from загран_паспорт, inserted, deleted where загран_паспорт. код_сотрудника = deleted. код_сотрудника end else begin select @errno = 30006, @errmsg = 'Cannot cascade сотрудник UPDATE because more than one row has been affected. ' goto error end

create trigger t. D_сотрудник on сотрудник for DELETE as …. /* сотрудник владеет загран_паспорт ON PARENT DELETE CASCADE */ …. delete загран_паспорт from загран_паспорт, deleted where … загран_паспорт. код_сотрудника = deleted. код_сотрудника

create trigger t. I_загран_паспорт on загран_паспорт for INSERT as … begin declare @numrows int, @nullcnt int, @validcnt int, @errno int, @errmsg varchar(255) select @numrows = @@rowcount If update(код_сотрудника) begin select @nullcnt = 0 select @validcnt = count(*) from inserted, сотрудник where inserted. код_сотрудника = сотрудник. код_сотрудника if @validcnt + @nullcnt != @numrows begin select @errno = 30002, @errmsg = 'Cannot INSERT загран_паспорт because сотрудник does not exist. ' goto error end

create trigger t. U_загран_паспорт on загран_паспорт for UPDATE as begin declare @numrows int, @nullcnt int, @validcnt int, @insсерия char(18), @insномер char(18), @errno int, @errmsg varchar(255) select @numrows = @@rowcount If update(код_сотрудника) begin select @nullcnt = 0 select @validcnt = count(*) from inserted, сотрудник where inserted. код_сотрудника = сотрудник. код_сотрудника if @validcnt + @nullcnt != @numrows begin select @errno = 30007, @errmsg = 'Cannot UPDATE загран_паспорт because сотрудник does not exist. ' goto error end

Позиция «Triggers» присутствует не для всех СУБД: есть для корпоративных СУБД и отсутствует для настольных.

Другие примеры триггеров

CREATE TRIGGER SALARY_CHANGE_HISTORY FOR EMPLOYEE AFTER UPDATE AS BEGIN IF (old. SALARY <> new. SALARY) THEN INSERT INTO SALARY_HISTORY (EMP_NO, CHANGE_DATE, UPDATER_ID, OLD_SALARY, PERCENT_CHANGE) VALUES old. EMP_NO, "now", USER, old. SALARY, (new. SALARY - old. SALARY) * 100 / old. SALARY); END хронология изменений зарплаты служащих (Interbase)