Триггеры

4. Триггеры

 

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

Существует три основных SQL-операции, применимые к данным, - INSERT/DELETE/UPDATE. А поскольку срабатывание триггера может происходить "до" (BEFORE)и "после"(AFTER) операции, то получается шесть возможных видов триггеров на таблицу - до и после каждой из трех возможных SQL-операций.

Триггер может быть активный (ACTIVE) и неактивный (INACTIVE).

·  Польза от применения триггеров.

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

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

- Автоматическая регистрация изменений в таблицах. Приложение может регистрировать с помощью триггера изменения в таблице.

- Автоматическая регистрация изменений в базе данных с помощью обработчиков событий в триггерах.

·  Примеры триггеров.

Триггер (англ. trigger «собачка, защёлка, спусковой крючок»):

в русском языке первоначально — термин из области радиосхем, позже электронной техники: пусковая схема, схема с несколькими устойчивыми состояниями, см. триггер (электроника); в последнее время заимствовались и другие значения английского оригинала:

спусковой крючок пистолета, арбалета и т. п., кнопка на джойстике, находящаяся под указательным пальцем; в более широком смысле: некая причина возникновения события вообще условия, при наступлении которых должно происходить предписанное действие Триггеры являются особой разновидностью хранимых процедур, которые активизируются не по прямому вызову пользователя, а при наступлении определенного события в базе данных, а именно — вставки, удаления или модификации записи в таблице. Триггеры привязаны к конкретной таблице. Каждая таблица может иметь до трех триггеров — по одному на каждый вид операции с данными (INSERT, UPDATE, DELETE). Но один триггер может также обрабатывать и два и все три события.

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

Триггер срабатывает после удаления строки из таблицы DAN и записывает удаленную строку в таблицу UD_DAN.

SET TERM^;

CREATE TRIGGER CHRAN_UD_DAN FOR DAN

ACTIVE AFTER DELETE POSITION 0

AS

BEGIN

INSERT INTO UD_DAN

VALUES(OLD.TAB_NO,OLD.FAM,OLD.IMIA,OLD.OTSH,OLD.G_R,OLD.ADRES,OLD.TEL,OLD.DATA_P,OLD.DATA_U, OLD.OBRAZ,OLD.SEM_POLOJ, OLD.S_N_PASPORT, OLD.K_V, OLD.K_O, OLD.K_P);

END^

SET TERM;^

Теперь если выполнить команду

DELETE FROM DAN

WHERE TAB_NO=1014; // (и 1013)

произойдёт конечно удаление строки из таблицы DAN, сработает триггер и удаленная строка запишется в таблицу UD_DAN, которая собственно и предназначена для хранения удаленных данных.

Что бы отключить триггер следует выполнить команду

ALTER TRIGGER <имя триггера> INACTIVE;

Что бы включить триггер следует выполнить команду

ALTER TRIGGER <имя триггера> ACTIVE;

Что бы удалить триггер команда

DROP TRIGGER <имя триггера>;

Следующий триггер, срабатывает ДО ВСТАВКИ (BEFORE INSERT) в таблицу DAN и заполняет поле первичного ключа (TAB_NO).

SET TERM ^;

CREATE TRIGGER VS_T FOR DAN

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

IF (NEW.TAB_NO IS NULL) THEN

NEW.TAB_NO=GEN_ID (TAB, 1);

END ^

SET TERM; ^

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

CREATE GENERATOR TAB;

SET GENERATOR TAB TO 1012;

При вставки новой строки в таблицу DAN, даже не указав значение поля TAB_NO,

INSERT INTO DAN (FAM, IMIA, OTSH, G_R, ADRES, TEL, DATA_P, DATA_U, OBRAZ,

SEM_POLOJ, S_N_PASPORT, K_V, K_O, K_P)

VALUES ('Кротов', 'Андрей', 'Федорович', '12.08.1961', 'Кирова 52-124', '281344', '21.02.2008', '28.03.2008','среднее''холост', '9999 22', '900' , '50' , '1');

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

Генератор, в свою очередь, теперь хранит значение 1014.

Кроме контекстной переменной NEW, существует переменная OLD. В отличие от NEW, OLD содержит старые значения записи, которые удаляются или изменяются. Например, можно использовать переменную OLD для получения значений записей, которые удаляются из таблицы.

Следующий триггер срабатывает ПОСЛЕ УДАЛЕНИЯ (AFTER DELETE) и записывает строки, удаленные из таблицы в отдельную таблицу.

Сначала необходимо создать таблицу для хранения удаленных данных.

CREATE TABLE DAN_D

(TAB_NO_D Float,

FAM_D Varchar (20),

IMIA _D Varchar (20),

OTSH_D Varchar (20),

G_R_D Date,

ADRES_D Varchar (20),

TEL_D Char (6) );

SET TERM ^;

CREATE TRIGGER DEL_DAN FOR DAN

ACTIVE AFTER DELETE POSITION 0

AS

BEGIN

INSERT INTO DAN_D

VALUES (OLD.TAB_NO, OLD.FAMILIA, OLD. IMIA E, OLD.OTSHO, OLD.GOD_R,

OLD.ADRES, OLD.TEL);

END ^

SET TERM; ^

Как только происходит удаление строки из таблицы DANNIE,

DELETE FROM DANNIE

WHERE TAB_NO=1013;

Так как, в данной БД при удалении строки из таблицы DAN удаляется и строка из таблицы NASH (начисления) с идентичным табельным номером (TAB_NO), уместно было бы создать триггер, который сохранял бы и данные из таблицы NASH. Чтобы не создавать лишние таблицы для хранения удаленных данных можно расширить уже созданную таблицу DAN_D.

ALTER TABLE DAN_D ALL KOD_Z_D FLOAT;

ALTER TABLE DAN_D ALL KOD_D_D FLOAT;

ALTER TABLE DAN_D ALL KOD_V_D FLOAT;

ALTER TABLE DAN_D ALL SLOJ_D FLOAT;

ALTER TABLE DAN_D ALL PAIK_D FLOAT;

Следующий триггер срабатывает после удаления строки из таблицы NASH.

SET TERM ^;

CREATE TRIGGER DEL_NASH FOR NASH

ACTIVE AFTER DELETE POSITION 0

AS

BEGIN

UPDATE DAN_D

SET KOD_Z_D=OLD.KOD_Z, KOD_D_D=OLD.KOD_D,

KOD_V_D=OLD.KOD_V, SLOJ_D=OLD.SLOJ,

PAIK_D=OLD.PAIK

WHERE TAB_NO_D=OLD.TAB_NO;

END ^

SET TERM; ^

Теперь после команды

DELETE FROM DAN

WHERE TAB_NO=1012;

произойдет следующее:

1.  Произойдет удаление строки с TAB_NO=1014 из таблицы DAN.

2.  Сработает триггер DEL_DAN, который запишет удаленную строку в таблицу DAN_D.

3.  Произойдет удаление строки с TAB_NO=1014 из таблицы NASH.

4.  Сработает триггер DEL_NASH и допишет в, уже наполовину заполненную строку таблицы DAN_D, данные из таблицы NASH, соответствующие этому сотруднику (WHERE TAB_NO_D=OLD.TAB_NO).

Следующий триггер срабатывает после добавления строки в таблицу DAN и запускает хранимую процедуру, которая, в свою очередь, заполняет строку с таким же табельным номером (TAB_NO) в таблице начислений (NASH). Поскольку нельзя предугадать, какими будут начисления того или иного работника, по умолчанию поля таблицы NASH будут заполняться значениями соответствующими минимальным начислениям предприятия, которые потом, безусловно, следует изменить. Хранимая процедура, вызванная триггером, лишь предварительно заполняет таблицу NASH, как бы занимая место для нового сотрудника, уже занесенного в таблицу DAN.

SET TERM ^;

CREATE TRIGGER Z_NASH FOR DANNIE

ACTIVE AFTER INSERT POSITION 0

AS

BEGIN

EXECUTE PROCEDURE VSTAV_NASH (NEW.TAB_NO);

END^

SET TERM;^

Хранимая процедура, вызываемая триггером.

SET TERM ^;

CREATE PROCEDURE VSTAV_NASH (T FLOAT)

AS

BEGIN

INSERT INTO NASH

VALUES (: T, 200, 307, 100, 0.5, 20);

END^

SET TERM; ^

При вставке строки

INSERT INTO DAN (FAM, IMIA,OTSH,G_R,ADRES,TEL)

VALUES ('Кротов', 'Андрей', 'Федорович', '12.08.1961', 'Кирова 52-124', '281344');

произойдет следующее:

1.  Сработает триггер VS_T, который присвоит новому сотруднику,

следующий по порядку, табельный номер.

2.  Заполнятся строка в таблице DANNIE соответствующими данными о новом сотруднике.

3. Срабатывает триггер Z_NASH, который вызывает ХП.

4. Хранимая процедура VSTAV_NASH вставляет в таблицу NASH данные (принятые по умолчанию) для нового сотрудника.

·  Управление состоянием триггера.

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

ALTER TRIGGER <trigger name> INACTIVE;

·  Удаление триггеров.

Удаление триггера выполняется с помощью оператора DROP TRIGGER. Синтаксис оператора удаления таков:

DROP TRIGGER name;

Здесь name – имя триггера.

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

//Триггер срабатывает после удаления строки из таблицы DAN и записывает удаленную строку в таблицу UD_DAN.

SET TERM^;

CREATE TRIGGER CHRAN_UD_DAN FOR DAN

ACTIVE AFTER DELETE POSITION 0

AS

BEGIN

INSERT INTO UD_DAN

VALUES(OLD.TAB_NO,OLD.FAM,OLD.IMIA,OLD.OTSH,OLD.G_R,OLD.ADRES,OLD.TEL,OLD. DATA_P, OLD.DATA_U,OLD.OBRAZ,OLD.SEM_POLOJ,OLD.S_N_PASPORT,OLD.K_V,OLD.K_O,OLD.K_P);

END^

SET TERM;^

Выполняя команду

DELETE FROM DAN

WHERE TAB_NO=1000;

произойдет, конечно, удаление строки из таблицы DAN, сработает триггер, и удаленная строка запишется в таблицу UD_DAN, которая собственно и предназначена для хранения удаленных данных.

Что бы отключить триггер следует выполнить команду

ALTER TRIGGER <имя триггера> INACTIVE;

Включить триггер следует выполнить команду

ALTER TRIGGER <имя триггера> ACTIVE;

Для удаления триггера команда

DROP TRIGGER <имя триггера>;

·  Триггеры в MS Access

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

Пожелание разработчика в целом можно сформулировать так: "хочу иметь пользовательскую функцию, которая срабатывает всякий раз, когда данные в таблице меняются (точнее – хотят измениться!), такую, которая может либо запретить изменение, либо что-то в нем скорректировать, и такую, которую никак нельзя обойти". В современных СУБД такая функция называется триггером (trigger). Триггеры есть, в SQL Server.

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

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

В основе Access лежит ядро баз данных Microsoft Jet. Объекты Access, доступные из VBA (в Access 2000 – из VB), являются на самом деле объектами ядра Jet. Те же обработчики событий в формах, например, мы имеем возможность писать благодаря тому, что объект ядра Jet Form поддерживает эти события. Ядро Jet имеет невысокие по сравнению с большими СУБД требования к ресурсам системы, обладая при этом достаточно большим потенциалом. Именно поэтому Access выглядит таким мобильным и годится для решения многих практических задач на достаточно современном уровне с минимальным количеством накладных расходов.

На уровне ядра Jet в Microsoft Access 2000 триггеров нет! Это продиктовано чисто коммерческими соображениями и связано с желанием Microsoft стремительно продвигать на рынок технологии, использующие SQL Server 7.0. В документации по Access триггером иногда называется группа кнопок, выполняющих функции переключателей – так это не те триггеры.

И в то же время, возможность использовать триггеры в Access 2000 есть. Microsoft теперь считает, что время Jet прошло и настала очередь больших корпоративных СУБД. Таких как SQL Server 7.0. Предусмотрено постепенное вытеснение Jet технологиями клиент-сервер. Появилось новое ядро баз данных MSDE (Microsoft Database Engine), очень тесно совместимое с SQL Server 7.0, практически это сильно усеченный SQL Server. Работая через это ядро можно хранить данные в формате SQL Server 7.0. Именно это рекомендуется делать тем, кто хочет иметь триггеры в Access 2000.

Загрузить в Access 2000 БД, которую мы хотим конвертировать в формат SQL Server 7.0, и запустить Upsizing Wizard . Wizard сделает все, что нужно, БД будет храниться далее в формате MS SQL Server 7.0, а не в mdb-файле. В Access вместо mdb мы будем загружать файл проекта adp.

Мы получаем возможность использовать виды (views), триггеры (triggers) и хранимые процедуры (stored procedures) SQL Server, редактируя их непосредственно из Access. Повышается уровень безопасности транзакций, появляются еще кое-какие плюсы, которые разработчика под Access обычно мало волнуют. Поскольку БД становится полностью совместима с SQL Server, можем пользоваться всем. При этом у нас остается весь инструментарий Access по разработке пользовательского интерфейса. Мы можем использовать также защиту данных на уровне SQL Server. Таблица Access в режиме дизайна начинает выглядеть так, как в Enterprise Manager SQL Server 7.0. Исчезает также вкладка Queries, вместо нее появляются вкладки Views и Stored Procedures.

Какие минусы?

Система становится заметно более тормозной. Хорошо для корпоративных решений, но совсем плохо для настольных баз данных. Чтобы писать триггеры, придется освоить язык, на котором они пишутся – Transact-SQL. Чтобы полноценно использовать предоставленные Вам возможности, нужно знать SQL Server 7.0

// MS Access

Отчет 6

Фамилия название профессии

ИВАНОВ начальник караула

пожарный

КУКУШКИНзаместитель по тылу

ПЕТРОВпожарный

СИДОРОВпожарный

СИДОРЧУКначальник части

ФРОЛОВАинспектор

секретарь

Option Compare Database

Sub Form_Current()

On Error GoTo Form_Current_Err

If ChildFormIsOpen() Then FilterChildForm

Form_Current_Exit:

Exit Sub

Form_Current_Err:

MsgBox Error$

Resume Form_Current_Exit

End Sub

Sub ToggleLink_Click()

On Error GoTo ToggleLink_Click_Err

If ChildFormIsOpen() Then

CloseChildForm

Else

OpenChildForm

FilterChildForm

End If

ToggleLink_Click_Exit:

Exit Sub

ToggleLink_Click_Err:

MsgBox Error$

Resume ToggleLink_Click_Exit

End Sub

Private Sub FilterChildForm()

If Me.NewRecord Then

Forms![данные].DataEntry = True

Else

Forms![данные].Filter = "[код профессии] = " & Me.[код профессии]

Forms![данные].FilterOn = True

End If

End Sub

Private Sub OpenChildForm()

DoCmd.OpenForm "данные"

If Not Me.[ToggleLink] Then Me![ToggleLink] = True

End Sub

Private Sub CloseChildForm()

DoCmd.Close acForm, "данные"

If Me![ToggleLink] Then Me![ToggleLink] = False

End Sub

Private Function ChildFormIsOpen()

ChildFormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, "данные") And acObjStateOpen) <> False

End Function

ЗАКЛЮЧЕНИЕ

Мы рассмотрели несколько типов объектов баз данных — хранимые процедуры, триггеры. Мы узнали следующее:

Хранимая процедура — это скомпилированный набор SQL-предложений, сохраненный в базе данных как именованный объект и выполняющийся как единый фрагмент кода. Для создания хранимой процедуры применяется предложение CREATE PROCEDURE, для изменения — ALTER PROCEDURE, а для удаления — DROP PROCEDURE.

Tриггер — это специальный тип хранимой процедуры, которая автоматически вызывается, когда данные в определенной таблице добавляются, удаляются или изменяются с помощью SQL-предложений INSERT, DELETE или UPDATE. Триггеры создаются с помощью предложения CREATE TRIGGER. Для изменения триггера используется предложение ALTER TRIGGER, а для удаления — предложение DROP TRIGGER.

Список использованной литературы

 

1.  С.Д. Кузнецов, информационно-аналитические материалы Центра Информационных Технологий пользованием интерфейса ядра СУБД.

2.  Грубер М. Понимание SQL- МОСКВА 1993 (Под редакцией Булычева В.Н.Перевод Лебедева В.Н.)

3.  Грубер М. SQL. - М.: Лори, 2003.

4.  К.Дейт. Введение в системы баз данных. 6-е издание. «Диалектика» Киев-Москва, 1998.

5.  Т.Каннолли, К.Бегг, А. Страчан -Базы данных: проектирование, реализация и сопровождение.Теория и практика

6.  Пахомов Б.И. Interbase b C++Builder на примерах./Б.И.Пахомов. – СПб.; БХВ- Петербург, 2006

7.  КомпьютерПресс 12'2000. Copyright © КомпьютерПресс 2008

webmaster@compress.ru