Создание и использование триггеров

Триггер - это механизм (специальный тип хранимой процедуры), который вызывается, когда в указанной таблице происходит определенное действие. Каждый триггер имеет следующие основные составляющие: имя, действие и исполнение. Имя триггера может содержать максимум 128 символов. Действием триггера может быть или инструкция DML (INSERT, UPDATE или DELETE), или инструкция DDL. Таким образом, существует два типа триггеров: триггеры DML и триггеры DDL. Исполнительная составляющая триггера обычно состоит из хранимой процедуры или пакета.

Язык Transact-SQL также поддерживает инструкцию ALTER TRIGGER, которая модифицирует структуру триггера. Эта инструкция обычно применяется для изменения тела триггера. Все предложения и параметры инструкции ALTER TRIGGER имеют такое же значение, как и одноименные предложения и параметры инструкции CREATE TRIGGER.

Для удаления триггеров в текущей базе данных применяется инструкция DROP TRIGGER.

 

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

— создания журнала логов действий в таблицах базы данных;

— реализации бизнес-правил;

— принудительного обеспечения ссылочной целостности.

Используется в примерах на Insert и Update.

 

Триггер с предложением INSTEAD OF заменяет соответствующее действие, которое запустило его.

Триггер типа INSTEAD OF пишется только для одной из указанных операций в рамках одного скрипта: INSERT, DELETE, UPDATE.

Используется в примерах на Delete.

 

Создание триггера типа DELETE. Пример.

 

 

create trigger clientInsteadof

on client

instead of delete

as

print 'Удаление клиентских данных запрещено';

delete from client;

--///Триггер, выводящий сообщение о запрете удаления данных из таблицы client.

 

 

Создание триггера типа INSERT. Пример.

 

GO

CREATE TABLE AuditClient(

UserName CHAR(16) NULL,

Date DATETIME NULL,

ClientNew FLOAT NULL

);

 

GO

CREATE TRIGGER trigger_ModifyClient

ON client AFTER INSERT

AS

BEGIN   

DECLARE @clientNew INT

SELECT @clientNew = (SELECT id_client FROM inserted)

INSERT INTO AuditClient VALUES

   (USER_NAME(), GETDATE(), @clientNew)

END

--///Триггер, контролирующий добавление записей в таблицу "Клиент" с фиксацией пользователя, внесшего

запись, даты внесения и идентификатора нового клиента.

 

INSERT INTO client(surname, name, mid_name,mob_telefone)

values ('Денисенко', 'Григорий', 'Алексеевич', '79981521310');

 

select * from AuditClient;

 

 

Создание триггера типа UPDATE. Пример.

 

GO

CREATE TABLE AuditStatusOrder (

   

UserName CHAR(16) NULL,

Date DATETIME NULL,

statusOld INT NULL,

statusNew INT NULL

);

 

GO

CREATE TRIGGER trigger_ModifyStatusOrder

ON zakaz AFTER UPDATE

AS IF UPDATE(id_status)

BEGIN

DECLARE @statusOld INT

DECLARE @statusNew INT

 

SELECT @statusOld = (SELECT id_status FROM deleted)

SELECT @statusNew = (SELECT id_status FROM inserted)

  

 

INSERT INTO AuditStatusOrder VALUES

   (USER_NAME(), GETDATE(), @statusOld, @statusNew)

END

 

update zakaz set id_status = 3, date_issues='2020-12-13', price_repair = 1750.0 where id_order = 8;

select * from AuditStatusOrder;

 

--///Триггер, контролирующий изменение статуса заказа в таблице "Заказы"

 

 

Методы поддержки распределенных данных.

Фрагментация.

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

Схема фрагментации отношения должна удовлетворять трем условиям:

 

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

2) Восстановилось. Должна существовать операция реляционной алгебры, позволяющая восстановить отношение из его фрагментов. Это правило гарантирует сохранение функциональной зависимости.

3) Непересекаемость. Если элемент данных присутствует во фрагменте отношения, то он не должен одновременно присутствовать в каком-либо ином фрагменте. Исключением из этого правила является операция вертикальной фрагментации, поскольку в этом случае в каждом фрагменте должны присутствовать атрибуты первичного ключа, необходимые для восстановления исходного отношения. Данное правило гарантирует минимальную избыточность данных во фрагментах. В случае горизонтальной фрагментации элементом данных является кортеж, а в случае вертикальной фрагментации – атрибут.

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

Горизонтальная фрагментация: горизонтальный фрагмент является результатом селекции. Данный тип фрагмента определяется с помощью операции выборки реляционной алгебры (запросы)

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

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

 

Причины, вызывающие необходимость фрагментации отношений.

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

2) Эффективность. Данные хранятся в тех местах, в которых они чаще всего используются. Кроме того, исключается хранение данных, которые не используются локальными приложениями.

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

4) Защищенность. Данные, не используемые локальными приложениями, не хранятся на сайтах, а значит, неавторизированные пользователи не смогут получить к ним доступ.

Механизму фрагментации свойственны два основных недостатка:

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

2)Целостность данных. Поддержка целостности данных может существенно осложняться, поскольку функционально зависимые данные могут оказаться фрагментированными и размещаться на различных сайтах.

 

Репликация.

 

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

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

Служба тиражирования должна выполнять следующие функции:

1. Обеспечение масштабируемости, т.е. эффективная обработки больших и малых объемов данных.

2. Преобразование типов и моделей данных.

3. Репликация объектов БД, например, индексов, триггеров и т.п.

4. Инициализация вновь создаваемой реплики.

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

Существуют различные подходы к организации репликации:

Репликация с основной копией. Существуют следующие варианты:

1 Классический подход заключается в наличии одной основной копии, в которую можно вносить изменения; остальные копии создаются с определением read only.

2 Асимметричная репликация: основная копия фрагментирована и распределена по разным узлам РБД, и другие узлы могут являться подписчиками отдельных фрагментов (read only).

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

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

Два основных его механизма распространения изменений:

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

2 асинхронный: подразумевает отложенный характер внесения изменений в удаленные копии.

 

Могут возникать конфликты обновлений такие как:

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

2 Конфликты удаления: одна транзакция пытается удалить запись, которая в другой копии уже удалена другой транзакцией. Если такая ситуация считается конфликтом, то она разрешаются вручную.

3 Конфликты обновления: две транзакции в разных копиях обновили одну и ту же запись, возможно, по-разному, и пытаются распространить свои изменения. Для идентификации конфликтов обновления необходимо передавать с транзакцией дополнительную информацию: старое и новое содержимое записи. Если старая запись не может быть обнаружена, налицо конфликт обновления.

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

Существуют различные варианты реализации распространения изменений. Один из них заключается в использовании триггеров.

Другой способ реализации механизма распространения изменений – поддержка журналов изменений для реплицируемых данных. Рассылка этих изменений входит в задачу сервера СУБД или сервера тиражирования (входящего в состав СУБД).

 Основные принципы, которых необходимо придерживаться при этом:

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

2 Информация об изменениях должна сохраняться в журнале до тех пор, пока не будут обновлены все копии этих данных.

Распределенные запросы.

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

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

 

Распределенные транзакции.

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