Логическая модель данных на основе инфологической модели данных

 

Физическая модель данных

Внимание! Синтаксис может не восприниматься вашей СУБД. В таком случае, читайте справку по синтаксису SQL вашей СУБД.

7.1. Создание базы данных с именем tochprib

CREATE DATABASE tochprib

7.2. Создание таблиц

 

CREATE TABLE poversotr

(pov_tabnom SMALLINT NOT NULL PRIMARY KEY,

pov_fam VARCHAR(15),

pov_ima VARCHAR(10),

pov_otch VARCHAR(15),

pov_datapost DATETIME,

pov_oklad REAL)

 

CREATE TABLE sred

(sred_kod VARCHAR(10) NOT NULL PRIMARY KEY,

sred_naim VARCHAR(60))

 

CREATE TABLE pribor

(prib_nom VARCHAR(20) NOT NULL PRIMARY KEY,

sred_kod VARCHAR(10) NOT NULL REFERENCES sred)

 

CREATE TABLE zakazchik

(zak_kod SMALLINT NOT NULL PRIMARY KEY,

zak_fio VARCHAR(50),

zak_naim VARCHAR(50),

zak_adress VARCHAR(50),

zak_rs VARCHAR(100),

zak_inn CHAR(10),

zak_tel CHAR(10))

 

CREATE TABLE dogovor

(dog_nom VARCHAR(10) NOT NULL PRIMARY KEY,

dog_data DATETIME,

dog_sum REAL,

zak_kod SMALLINT NOT NULL REFERENCES zakazchik)

 

CREATE TABLE zayvka

(zayvka_s INT NOT NULL PRIMARY KEY,

dog_nom VARCHAR(10) NOT NULL REFERENCES dogovor,

prib_nom VARCHAR(20) NOT NULL REFERENCES pribor,

zayvka_srok CHAR(10)DEFAULT 'срочно',

zayvka_прим CHAR(40)DEFAULT 'на территории исполнителя')

 

CREATE TABLE velich

(vel_kod SMALLINT NOT NULL PRIMARY KEY,

vel_naim VARCHAR(50))

 

CREATE TABLE poverka

(poverka_s INT NOT NULL PRIMARY KEY,

poverka_data DATETIME,

zayvka_s INT NOT NULL REFERENCES zayvka,

pov_tabnom SMALLINT NOT NULL REFERENCES poversotr,

vel_kod SMALLINT NOT NULL REFERENCES velich,

poverka_otm VARCHAR(15) DEFAULT 'соответствует')

7.3. Ввод данных в таблицы (создание тестового примера)

Ввод данных начинается с родительских таблиц.

В таблицы необходимо внести данные тестового примера, воспользовавшись оператором INSERT.

 

INSERT INTO sred VALUES ('ЩК120', 'Щитовой электроизмерительный прибор переменного тока')

INSERT INTO pribor VALUES ('ВТ-131-2009', 'ЩК120')

 

INSERT INTO zakazchik VALUES (1,'Иванов Сергей Иванович','ОАО «Электросервис»','г. Ульяновск, Королева, 34','00056789345123 в ГРКЦ НБ Ульяновской области г. Ульяновск', '771703456','33-69-85')

INSERT INTO dogovor VALUES ('123/в','05.25.2012',50000.0,1)

INSERT INTO zayvka VALUES (3, '123/в','ВТ-132-2009','не срочно','на территории ОАО «Электросервис»')

 

 

INSERT INTO velich VALUES (2,'Переменный ток')

 

INSERT INTO poversotr VALUES (11234,'Петров','Иван','Петрович','01.01.2005',12500.00)

INSERT INTO poverka VALUES (1, '05.30.2012', 1, 11234, 1, 'соответствует')

Запросы к базе данных

8.1. Запрос- модификация структуры таблицы sred (добавление столбца)

ALTER TABLE sred ADD sred_firma VARCHAR(50)

8.2. Запрос- изменение данных в таблицеsred

UPDATE sred SET sred_firma = 'ОАО "Электроприбор" г. Чебоксары' WHERE sred_kod='ЩК120'

8.3. Запрос-удаление строк таблицы poversotr

 

DELETE FROM poversotr WHERE pov_tabnom=12346

 

Оператор SELECT имеет следующий формат:

SELECT [ALL | DISTINCT ] {*|[имя_столбца

[AS новое_имя]]} [,...n]

FROM имя_таблицы [[AS] псевдоним] [,...n]

[WHERE <условие_поиска>]

[GROUP BY имя_столбца [,...n]]

[HAVING <критерии выбора групп>]

[ORDER BY имя_столбца [ASC| DESC] [,...n]]

Обработка элементов оператора SELECT выполняется в следующей последовательности:

1. FROM – определяются имена используемых таблиц;

2. WHERE – выполняется фильтрация строк таблицы в соответствии с заданными условиями;

3. GROUP BY – образуются группы строк, имеющих одно и то же значение в указанном столбце;

4. HAVING – фильтруются группы строк таблицы в соответствии с указанным условием;

5. SELECT – устанавливается, какие столбцы должны присутствовать в выходных данных;

6. ORDER BY – определяется упорядоченность результатов выполнения операторов (сортировка).

Порядок предложений и фраз в операторе SELECT не может быть изменен. Только два предложения SELECT и FROM являются обязательными, все остальные могут быть опущены. SELECT – закрытая операция: результат запроса к таблице представляет собой другую таблицу.

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

Сравнение: сравниваются результаты вычисления одного выражения с результатами вычисления другого (операторы сравнения: = – равенство; < – меньше; > – больше; <= – меньше или равно; >= – больше или равно; <> – не равно).

Диапазон: проверяется, попадает ли результат вычисления выражения в заданный диапазон значений (Оператор BETWEEN используется для поиска значения внутри некоторого интервала, определяемого своими минимальным и максимальным значениями. При этом указанные значения включаются в условие поиска, например, WHERE Цена Between 100 And 150).

Принадлежность множеству: проверяется, принадлежит ли результат вычислений выражения заданному множеству значений (Оператор IN используется для сравнения некоторого значения со списком заданных значений, при этом проверяется, соответствует ли результат вычисления выражения одному из значений в предоставленном списке. NOT IN используется для отбора любых значений, кроме тех, которые указаны в представленном списке. Например, WHERE Город IN (‘Москва’, ‘Самара’)).

Соответствие шаблону: проверяется, отвечает ли некоторое строковое значение заданному шаблону (В операторе LIKE используются символы % и _. % - заменяет 0 или n-е количество символов, _ - 0 или 1 символ)  

Значение NULL: проверяется, содержит ли данный столбец определитель NULL (неизвестное значение).

8.4. Запрос, реализующий операцию  естественного соединения таблиц zayvka, poverka, poversotr, velich, с использованием конструкции WHERE

 

SELECT poverka.poverka_data, poversotr.pov_fam, poverka.poverka_otm, velich.vel_naim, zayvka.zayvka_srok, zayvka.prib_nom

FROM zayvka, poverka, poversotr, velich

WHERE zayvka.zayvka_s = poverka.zayvka_s AND poverka.pov_tabnom = poversotr.pov_tabnom AND poverka.vel_kod = velich.vel_kod

                     

8.5. Запрос, реализующий операцию естественного соединения таблиц dogovor и zakazchik, с использованием конструкции INNER JOIN.. ON

SELECT dogovor.dog_nom, dogovor.dog_data, dogovor.dog_sum, zakazchik.zak_fio, zakazchik.zak_naim

FROM dogovor INNER JOIN zakazchik ON dogovor.zak_kod = zakazchik.zak_kod

8.6. Запрос на выборку данных из таблицы poversotr, реализующий операцию вертикального среза

SELECT pov_tabnom, pov_fam, pov_oklad FROM poversotr

8.7. Запрос на выборку данных из таблицы sred, реализующий операцию горизонтального среза

SELECT sred_kod, sred_naim,sred_firma FROM sred

WHERE sred_naim LIKE '%Щитовой%'

 

8.8. Запрос на выборку данных, реализующий операцию произведения таблиц pribor, sred

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

SELECT * FROM pribor, sred

8.9. Запрос на выборку данных, реализующий операцию объединения (velich _1 È velich)

Для выполнения операции объединения необходимо иметь две таблицы совместимые по типу.  Создадим таблицу, совместимую по типу

 

CREATE TABLE velich_1

(vel_kod SMALLINT NOT NULL PRIMARY KEY,

vel_naim VARCHAR(50))

 

Заполним новую таблицу строками.

SELECT vel_kod, vel_naim FROM velich UNION SELECT vel_kod, vel_naim FROM velich_1

 

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

Запросы 8.9 и 8.10 выполнены с использованием техники вложенного запроса

8.9. Запрос на выборку данных, реализующий операцию вычитания (velich _1 – velich)

Для выполнения операции таблицы должны быть совместимые по типу.

 

SELECT vel_kod, vel_naim FROM velich_1

WHERE NOT EXISTS

(SELECT vel_kod, vel_naim

FROM velich WHERE velich_1.vel_kod=velich.vel_kod

AND velich_1.vel_naim=velich.vel_naim)

8.10. Запрос на выборку данных, реализующий операцию пересечения (velich _1 Ç velich)

Для выполнения операции таблицы должны быть совместимые по типу.

 

SELECT vel_kod, vel_naim FROM velich_1

WHERE EXISTS

(SELECT vel_kod, vel_naim

FROM velich WHERE velich_1.vel_kod=velich.vel_kod

AND velich_1.vel_naim=velich.vel_naim)

 

С помощью итоговых (агрегатных) функций в рамках SQL-запроса можно получить ряд обобщающих статистических сведений о множестве отобранных значений выходного набора. Итоговые функции могут использоваться только в списке предложения SELECT и в составе предложения HAVING. Во всех других случаях это недопустимо. Если список в предложении SELECT содержит итоговые функции, а в тексте запроса отсутствует фраза GROUP BY, обеспечивающая объединение данных в группы, то ни один из элементов списка предложения SELECT не может включать каких-либо ссылок на поля, за исключением ситуации, когда поля выступают в качестве аргументов итоговых функций.

Пользователю доступны следующие основные итоговые (агрегатные) функции:

Count (Выражение) - определяет количество записей в выходном наборе SQL-запроса, часто вместо (Выражение) используют символ * или имя столбца таблицы;

Min/Max (Выражение) - определяют наименьшее и наибольшее из множества значений в некотором поле запроса;

Avg (Выражение) - эта функция позволяет рассчитать среднее значение множества значений, хранящихся в определенном поле отобранных запросом записей. Оно является арифметическим средним значением, т.е. суммой значений, деленной на их количество.

Sum (Выражение) - вычисляет сумму множества значений, содержащихся в определенном поле отобранных запросом записей.

Чаще всего в качестве выражения выступают имена столбцов. Выражение может вычисляться и по значениям нескольких таблиц.

Все эти функции оперируют со значениями в единственном столбце таблицы или с арифметическим выражением и возвращают единственное значение. Функции COUNT, MIN и MAX применимы как к числовым, так и к нечисловым полям, тогда как функции SUM и AVG могут использоваться только в случае числовых полей, за исключением COUNT(*).

8.11. Запросы  с использованием агрегации данных и группировки

1. для определения количества поверок в разрезе дат поверок

SELECT COUNT(*) AS 'количество поверок', poverka_data AS 'дата поверки' FROM poverka GROUP BY poverka_data

 

2. для определния дат, в которые проводилось более 1-ой поверки

SELECT COUNT(*) AS 'количество поверок', poverka_data AS 'дата поверки' FROM poverka GROUP BY poverka_data HAVING COUNT(*)>1

 

3. для определения наибольшего оклада поверщиков

SELECT MAX(pov_oklad) AS 'наибольший оклад' FROM poversotr

 

8.12. Запросы с использованием функций

1. с использованием функции для работы с данными типа DATETIME для определения номера месяца поступления поверщика на работу

 

SELECT pov_fam AS 'фамилия', MONTH(pov_datapost)AS 'номер месяца поступления' FROM poversotr

2. с использованием строковой функции LEN для определения количества символов в наименовании СИ

 

SELECT sred_naim AS 'нименование СИ ', LEN(sred_naim) AS 'количество букв в наименовании СИ' FROM sred

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

SELECT pov_fam, pov_ima, pov_oklad FROM poversotr WHERE pov_oklad=(SELECT MAX(pov_oklad) FROM poversotr)

8.14. Запросы для создания представлений

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

Создание и изменение представлений:

CREATE| ALTER VIEW имя _ представления

 AS SELECT_оператор

 

CREATE VIEW dogzak AS SELECT dogovor.dog_nom, zakazchik.zak_fio FROM dogovor, zakazchik WHERE dogovor.zak_kod=zakazchik.zak_kod

 

Процедуры

 

Группировка двух и более команд в единый блок осуществляется с использованием ключевых слов BEGIN и END. БлокиBEGIN...END могут быть вложенными.

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

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

CREATE | ALTER PROC[EDURE] имя_процедуры

[{@имя_параметра тип_данных } ]

AS

[BEGIN]

sql_оператор [...n]

[END]

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

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

EXEC[UTE] имя_процедуры [@имя_параметра={значение| @имя_переменной}] [,...n]

В процедурах могут использоваться управляющие конструкции (циклы, ветвления).

Синтаксис условного оператора показан ниже:

IF лог_выражение

{ sql_оператор | блок_ sql_операторов }

[ ELSE

{sql_оператор | блок_  sql _ операторов } ]

Циклы организуются с помощью следующей конструкции:

WHILE лог_выражение

{ sql_оператор | sql_блок_операторов }

[ BREAK ]

{ sql_оператор | sql_блок_операторов }

[ CONTINUE ]

Цикл можно принудительно остановить, если в его теле выполнить команду BREAK. Если же нужно начать цикл заново, не дожидаясь выполнения всех команд в теле, необходимо выполнить команду CONTINUE.

Для замены множества одиночных или вложенных условных операторов используется следующая конструкция:

CASE входное_значение

WHEN {значение_для_сравнения |

лог_выражение } THEN

вых_выражение [,...n]

[ ELSE иначе_вых_выражение ]

END

Если входное значение и значение для сравнения совпадают, то конструкция возвращает выходное значение. Если же значение входного параметра не найдено ни в одной из строк WHEN...THEN, то тогда будет возвращено значение, указанное после ключевого слова ELSE

9.1. Процедуры без параметров

1. процедура для просмотра общей суммы договоров в разрезе дат

CREATE PROCEDURE primer1

AS

BEGIN

SELECT SUM(dog_sum) AS 'общая сумма по договорам, руб', dog_data AS 'дата договора' FROM dogovor GROUP BY dog_data

END

EXECUTE primer1

 

2. процедура для индексации окладов с возможностью просмотра окладов до и после индексации

CREATE PROCEDURE primer2

AS

BEGIN

SELECT pov_fam AS 'фамилия поверщика', pov_oklad AS 'оклад'FROM poversotr

UPDATE poversotr SET pov_oklad=pov_oklad*0.01+pov_oklad

SELECT pov_fam AS 'фамилия поверщика', pov_oklad AS 'оклад после индексации' FROM poversotr

END

 

EXECUTE primer2

9.2. Процедуры с входным параметром

1. процедура для просмотра даты и суииы договора, при вводе номера договора

CREATE PROCEDURE primer3 (@param varchar(10))

AS

BEGIN

SELECT dog_data as 'дата договора', dog_sum as сумма договора' FROM dogovor WHERE dog_nom=@param

END

 

EXECUTE primer3 @param='124/а'

2.   процедура для изменения срочности заявки на поверку СИ

CREATE PROCEDURE primer4 (@param varchar(20))

AS

BEGIN

UPDATE zayvka SET zayvka_srok=не срочно' WHERE prib_nom=@param2

SELECT * FROM zayvka

END

 

EXECUTE primer4 @param='ВТ-164-2008'

9.3. Процедуры с использованием  курсора и управляющих конструкций (цикла и ветвления)

 

Существует несколько способов передачи данных между командами. Один из них – передача данных через локальные переменные. Прежде чем использовать какую-либо переменную, ее следует объявить. Объявление переменной выполняется командой DECLARE, имеющей следующий формат:

DECLARE {@имя_переменной  {тип_данных} } [,...n]

Значения переменной можно присвоить посредством команд SET и SELECT. С помощью команды SELECT переменной можно присвоить не только конкретное значение, но и результат вычисления выражения. Например,

DECLARE @a INT,

SET @a=10,

SELECT @k=MIN(Цена) FROM Товар

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

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

- создание или объявление курсора;

- открытие курсора, т.е. наполнение его данными, которые сохраняются в многоуровневой памяти;

- выборка из курсора и изменение с его помощью строк данных;

- закрытие курсора, после чего он становится недоступным для пользовательских программ;

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

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

Курсоры делятся на две категории: последовательные и прокручиваемые. Последовательные позволяют выбирать данные только в одном направлении – от начала к концу. Прокручиваемые же курсоры допускают перемещение в обоих направлениях и переход к произвольной строке результирующего набора курсора.

SQL Server поддерживает курсоры статические, динамические.

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

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

В статический курсор внести изменения невозможно, поэтому он всегда открывается в режиме «только для чтения».

Динамический курсор поддерживает данные в «живом» состоянии, но это требует затрат сетевых и программных ресурсов. При использовании динамических курсоров не создается полная копия исходных данных, а выполняется динамическая выборка из исходных таблиц только при обращении пользователя к тем или иным данным. На время выборки сервер блокирует строки, а все изменения, вносимые пользователем в полный результирующий набор курсора, будут видны в курсоре. Однако если другой пользователь внес изменения уже после выборки данных курсором, то они не отразятся в курсоре.

Управление курсором реализуется путем выполнения следующих команд:

DECLARE – создание или объявление курсора;

OPEN – открытие курсора, т.е. наполнение его данными;

FETCH – выборка из курсора и изменение строк данных с помощью курсора;

CLOSE – закрытие курсора;

DEALLOCATE – освобождение курсора, т.е. удаление курсора как объекта.

Создание курсора (упрощенный вариант) выполняется при помощи командыDECLARE @имя_переменной курсора [SCROLL] [STATIC | DYNAMIC] CURSORFOR SELECT_оператор

При указании ключевого слова SCROLL созданный курсор можно прокручивать в любом направлении, что позволяет применять любые команды выборки. Если этот аргумент опускается, то курсор окажется последовательным, т.е. его просмотр будет возможен только в одном направлении – от начала к концу.

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

Для открытия курсора и наполнения его данными из указанного при создании курсора запроса SELECT используется следующая команда:

OPEN @имя_переменной_курсора

Сразу после открытия курсора можно выбрать его содержимое (результат выполнения соответствующего запроса) посредством следующей команды:

FETCH [NEXT | PRIOR | FIRST | LAST

| ABSOLUTE {номер_строки

| @переменная_номера_строки}

| RELATIVE {номер_строки |

@переменная_номера_строки}]

FROM имя_курсора  [INTO @имя_переменной [,...n]]

Для работы с курсором используются специальные функции.

Функция @@FETCH_STATUS возвращает состояние последней инструкции FETCH курсора, запущенной в отношении любого курсора, который в настоящий момент открыт соединением. Тип возвращаемого значения - INT. Значение возвращаемого значения:

Возвращаемое значение	Описание
0	Инструкция FETCH была выполнена успешно.
-1	Выполнение инструкции FETCH завершилось неудачно или строка оказалась вне пределов результирующего набора.
-2	Выбранная строка отсутствует.

 

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

 

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

CREATE PROCEDURE spisok

AS

-- объявление курсора s

DECLARE s CURSOR FOR SELECT pov_tabnom, COUNT(pov_tabnom) FROM poverka GROUP BY pov_tabnom

-- открытие курсора s

OPEN s

-- объявление пременных @y и @x

DECLARE @y SMALLINT, @x INT

-- загрузка данных из первой строки курсора в пременные @y и @x

FETCH next FROM s INTO @y, @x

PRINT 'табельный номер '+'количество поверок'

-- цикл «ПОКА» не просмотрятся все строки курсора

WHILE @@FETCH_STATUS=0

BEGIN

IF @x>1

PRINT STR(@y)+' '+STR(@x)

-- загрузка данных из следующей строки курсора в пременные @y и @x

FETCH next FROM s INTO @y, @x

END

-- закрытие курсора

CLOSE s

-- освобождение (очистка от данных) курсора

DEALLOCATE s

 

EXECUTE spisok

 

2. Определение списка сотрудников, которые произвели более одной поверки (без использования курсора)

 CREATE VIEW primer AS SELECT pov_tabnom, COUNT(pov_tabnom) AS 'kol_poverok' FROM poverka GROUP BY pov_tabnom

 

SELECT pov_tabnom FROM primer WHERE kol_poverok>1

 

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

 

CREATE PROCEDURE maxstoim

AS

DECLARE p CURSOR FOR SELECT zak_kod, AVG(dog_sum) FROM dogovor GROUP BY zak_kod

OPEN p

DECLARE @y SMALLINT, @x REAL, @max REAL, @kod SMALLINT

FETCH next FROM p INTO @y, @x

SET @max=@x

SET @kod=@y

PRINT 'код заказчика '+'максимальная средняя стоимость договоров'

WHILE @@FETCH_STATUS=0

BEGIN

IF @x>=@max

BEGIN

SET @max=@x

SET @kod=@y

END

FETCH next FROM p INTO @y, @x

END

PRINT STR(@kod)+' '+STR(@max)

CLOSE p

DEALLOCATE p

 

EXECUTE maxstoim

 

Содержание таблицы dogovor

 

Результат выполнения процедуры maxstoim

Триггеры

 

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

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

Основной формат команды создания или изменения триггера:

CREATE | ALTER} TRIGGER имя_триггера

ON {имя_таблицы | имя_представления }

{ { FOR | AFTER | INSTEAD OF }

{ [ DELETE] [,] [ INSERT] [,] [ UPDATE] }

AS

sql_оператор [...n] }

Триггерные события состоят из вставки (INSERT), удаления (DELETE) и обновления (UPDATE) строк в таблице.

Параметры AFTER  и INSTEAD OF, определяют поведение триггеров:

AFTER - триггер выполняется после успешного выполнения вызвавших его команд.

INSTEAD OF -  Триггер вызывается вместо выполнения команд.

При выполнении команд добавления, изменения и удаления записей сервер создает две специальные таблицы: inserted и deleted. В них содержатся списки строк, которые будут вставлены или удалены по завершении транзакции. Структура таблиц inserted и deleted идентична структуре таблиц, для которых определяется триггер. Для каждого триггера создается свой комплект таблиц inserted и deleted, поэтому никакой другой триггер не сможет получить к ним доступ. В зависимости от типа операции, вызвавшей выполнение триггера, содержимое таблиц inserted и deleted может быть разным:

команда INSERT – в таблице inserted содержатся все строки, которые пользователь пытается вставить в таблицу; в таблице deleted не будет ни одной строки; после завершения триггера все строки из таблицы inserted переместятся в исходную таблицу;

команда DELETE – в таблице deleted будут содержаться все строки, которые пользователь попытается удалить; триггер может проверить каждую строку и определить, разрешено ли ее удаление; в таблице inserted не окажется ни одной строки;

команда UPDATE – при ее выполнении в таблице deleted находятся старые значения строк, которые будут удалены при успешном завершении триггера. Новые значения строк содержатся в таблице inserted. Эти строки добавятся в исходную таблицу после успешного выполнения триггера.

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

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

 

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

 

CREATE TRIGGER izmenzak ON zakazchik

INSTEAD OF UPDATE

AS

IF @@ROWCOUNT=1

BEGIN

DECLARE @x SMALLINT

SELECT @x=d.zak_kod FROM zakazchik a, deleted d WHERE a.zak_kod=d.zak_kod

IF EXISTS (SELECT dog_data FROM dogovor WHERE zak_kod=@x)

BEGIN

PRINT 'Внимание имеются договоры с этим заказчиком!'

SELECT dog_data AS

'дата договора', dog_nom AS

'номер договора' FROM dogovor WHERE zak_kod=@x

END

END