Создание БД в Microsoft SQL Server

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

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

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

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

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

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

Основной формат команды CREATE TRIGGER показан  ниже:

  CREATE TRIGGER имя_триггера

  BEFORE | AFTER <триггерное_событие>

  ON <имя_таблицы>

  [REFERENCING

    <список_старых_или_новых_псевдонимов>]

  [FOR EACH { ROW | STATEMENT}]

  [WHEN(условие_триггера)]

  <тело_триггера>

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

Выполняемые триггером действия задаются для каждой строки (FOR EACH ROW), охваченной данным событием, или только один раз  для каждого события (FOR EACH STATEMENT).

Обозначение <список_старых_или_новых_псевдонимов> относится к таким компонентам, как старая или новая строка (OLD / NEW) либо старая или новая таблица (OLD TABLE / NEW TABLE). Ясно, что старые значения не применимы для событий вставки, а новые – для событий удаления.

create trigger tr2

on klient for insert

as

if (select nac_dog from inserted ) >=getdate()

 begin

         raiserror('Дата начала договора не должна быть больше текущей даты!',1,11)

      rollback tran

     end

if (select prod_dog from inserted ) >=getdate()

 begin

         raiserror('Дата продления договора не должна быть больше текущей даты!',1,11)

      rollback tran

     end

6.5 Запросы

В качестве примера рассмотрим создание трех запросов на выборку на языке SQL.

1.  Вывести список всех клиентов

select klient.FIO_klient,klient.Adr_kl,klient.Nom_Dog

from klient

Результаты показаны на рис. 23

Рис. 23 Результаты запроса

2. Вывести список всех объектов 

select klient.FIO_klient, klient.Nom_Dog, t_objecct.Adr_kv

from klient, t_objecct

where klient.ID_klient=t_objecct.ID_klient

group by  klient.FIO_klient, klient.Nom_Dog, t_objecct.Adr_kv

Результаты показаны на рис. 24

Рис. 24 Результаты запроса

3. Вывести сколько объектов есть у каждого клиента

select klient.FIO_klient, klient.Nom_Dog, count(t_objecct.ID_Object)

from klient, t_objecct

where klient.ID_klient=t_objecct.ID_klient

group by  klient.FIO_klient, klient.Nom_Dog

Результаты показаны на рис. 25

Рис. 25 Результаты запроса

6.6 Разработка хранимых процедур в MS SQL SERVER

Кроме триггеров в ИС «Охрана» необходимо создать хранимые процедуры в которых будут выполняться запросы.

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

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

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

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

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

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

В SQL Server имеется несколько типов хранимых процедур.

1. Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.
2. Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.
3. Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

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

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

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

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

    [;номер]

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

   [=default][OUTPUT] ][,...n]

[WITH { RECOMPILE | ENCRYPTION | RECOMPILE,

   ENCRYPTION }]

[FOR REPLICATION]

AS

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

Рассмотрим параметры данной команды.

Используя префиксы sp_, #, ##, создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

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

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

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

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

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

Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию.

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

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

Создадим хранимые процедуры для  описанных ранее запросов.

Разрабатываемая ИС «Охрана» будет  иметь шесть запросов для выборки  данных и один запрос на удаление данных.

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

CREATE PROC kol_dog

@k int

AS

SELECT  Klient.FIO_Klient,k=count(Klient.Nom_klient)

FROM Klient

group by    Klient.FIO_Klient

having count(Klient.Nom_klient)>@k

Вызов процедуры

Exec kol_dog 1

Результат

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

CREATE PROC vr_dog

@dn datetime,

@dk datetime

AS

SELECT   Klient.FIO_Klient, Klient.adr_kl,klient. Nom_Dog,klient. Nac_dog,klient. Kon_dog

FROM Klient

where klient.nac_dog>@dn  and klient.Kon_dog<@dk

group by Klient.FIO_Klient,Klient.adr_kl,klient.Nom_Dog,klient.Nac_dog,klient. Kon_dog