Представления. Виды представлений. Обновляемые представления. Триггеры.

CREATE [OR REPLACE][ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)] AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

view_name — имя создаваемого представления;

select_statement — оператор SELECT, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении.

необязательные конструкции:

OR REPLACE — при использовании данной конструкции в случае существования представления с таким именем старое будет удалено, а новое создано.

В противном случае возникнет ошибка, информирующая о существовании представления с таким именем и новое представление создано не будет.

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

Однако конструкция OR REPLACE действует только на представления и замещать таблицу не будет.

column_list — задает имена полей представления.

Имена полей представления должны быть уникальны:

 

CREATE VIEW v (a_id, b_id) AS SELECT a.id, b.id FROM a,b;

       или

CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a,b;

2) вычисляемые поля представлений должны иметь имена:

CREATE VIEW vendor_cost (cost) AS SELECT name, price*quantity

FROM venders natural join incoming natural join magazine_incoming natural join prices; (MySQL)
       или

CREATE VIEW vendor_cost AS SELECT name, price* quantity as cost

FROM venders natural join incoming natural join magazine_incoming natural join prices; (MySQL)

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

Select <> from view_name

Существует два алгоритма, используемых MySQL при обращении к представлению: MERGE и TEMPTABLE.

Алгоритм MERGE: MySQL при обращении к представлению добавляет в использующийся оператор соответствующие части из определения представления и выполняет получившийся оператор над базовой таблицей.

Алгоритм TEMPTABLE: MySQL заносит содержимое представления во временную таблицу, над которой затем выполняется оператор обращенный к представлению.

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

WITH CHECK OPTION (с опцией проверки) — при использовании данной конструкции все добавляемые или изменяемые строки будут проверяться на соответствие определению представления.

В случае несоответствия данное изменение не будет выполнено.

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

 

Представление может стать дополнительным элементом защиты БД

или инструментом создания внешних схем:

Можно скрыть части таблиц БД.

Любые манипуляции с представлениями, доступными только для чтения,

 не способны навредить реальным данным.

Представление может стать инструментарием,

упрощающим доступ к данным,

ускоряющим разработку проекта БД

и снижающим трудозатраты при перестройке структуры БД.

Пример

Клиентские приложения обращаются к определенному срезу данных, формируя

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

При необходимости изменить правила выборки данных достаточно внести изменения

в код представления, расположенном на стороне сервера.

Виды представлений и ограничения при описании запроса SELECT для определения представлений

Горизонтальное п.- подмножество записей из таблицы БД, отвечающее определённым условиям.

Вертикальное п. - подмножество столбцов таблицы БД, требуемое программой.

Объединенное п. - результат соединения (join) нескольких таблиц.

Сгруппированное п. - результат группировки записей в таблице.

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

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

Модифицируемые представления

В проектах БД большинство представлений доступно только для чтения.

Автоматически представление доступно только для чтения,

если оно обладает хотя бы одной из перечисленных ниже характеристик:

представление построено на базе двух и более таблиц или представлений;

при описании представления используется подзапросы;

представление содержит выражения;

используется оператор группировки group by;

используется любая из агрегатных функций;

задействуются квантификаторы DISTINCT;

задействуются спецификаторы UNION или UNION ALL;

множественные ссылки на любой столбец базовой таблицы.

Безусловно допускают редактирование данных представления, отвечающие

следующим критериям:

представление должно обращаться только к одной таблице и возвращать все столбцы,

требующие обязательного ввода значений;

2) представление обращается к другому представлению, допускающему модификацию данных;

3) в представлении отсутствуют столбцы с одинаковыми именами;

4) в представлении нет вычисляемых (искусственных) столбцов;

5) в представлении отсутсвуют все черты немодифицируемых представлений.

Представление называется обновляемым, если к нему могут быть применимы операторы UPDATE и DELETE для изменения данных в таблицах, на которых основано представление.

Для того, чтобы представление было обновляемым должно быть выполнено два условия:

соответствие 1 к 1 между строками представления и таблиц, на которых основано представление,

т.е. каждой строке представления должно соответствовать по одной строке в таблицах-источниках.

2) поля представления должны быть простым перечислением полей таблиц, а не выражениями col1/col2 или col1+2.

 

Триггеры

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

Триггеры могут быть эффективно использованы для поддержки семантической целостности БД, однако приоритет их ниже, чем приоритет правил-ограничений (constraints), задаваемых на уровне описания таблиц и на уровне связей между таблицами. При написании триггеров всегда надо помнить об этом, при нарушении правил целостности по связям (DRI declarative Referential Integrity) триггер просто может никогда не сработать.

Для создания триггеров используется специальная команда:

Имя триггера является идентификатором во встроенном языке программирования СУБД и должно удовлетворять соответствующим требованиям.

 В параметре FOR задается одна или несколько операций модификации, которые запускают данный триггер.

Параметр WITH ENCRIPTING имеет тот же смысл, что и для хранимых процедур, он скрывает исходный текст тела триггера.

Существует несколько правил, которые ограничивают набор операторов, которые могут быть использованы в теле триггера.

Так, в большинстве СУБД действуют следующие ограничения:

- Нельзя использовать в теле триггера операции создания объектов БД (новой БД, новой таблицы, нового индекса, новой хранимой процедуры, нового триггера, новых индексов, новых представлений)

 - Нельзя использовать в триггере команду удаления объектов DROP для всех типов базовых объектов БД

- Нельзя использовать в теле триггера команды изменения базовых объектов. ALTER TABLE, ALTER DATABASE.

 - Нельзя изменять права доступа к объектам БД, то есть выполнять команду GRAND или REVOKE. Q Нельзя создать триггер для представления (VIEW).

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

 

CREATE TABLE users(

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

age INTEGER NOT NULL,

address TEXT NOT NULL,

mydate TEXT NOT NULL

);

 

CREATE TABLE user_log (

Id_u INTEGER NOT NULL,

u_date TEXT NOT NULL

);

 

CREATE TRIGGER my_u_log BEFORE INSERT

ON users

BEGIN

INSERT INTO user_log(id_u, u_date) VALUES (NEW.id, datetime('now'));

END;