Генерация схемы базы данных в MS SQL SERVER.

Цель: научится создавать базу данных MS SQL SERVER путем генерации её схемы из Erwin 4.1.

Для генерации схемы БД из Erwin необходимо её открыть.

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

Рисунок 1. – Переход к физической модели.

В меню Database выбрать пункт Choose Database (рис.2).

Рисунок 2. – Меню Database.

В открывшемся диалоговом окне выбрать в разделе Target SQL DBMS выбрать SQL Server, в разделе SQL Server Version из выпадающего списка выбрать 2000 (рис. 3). Нажать ОК. На вопрос « Would you like to convert databases type from Access 2000 to SQL Server 2000? This is not undoable» ответить Yes и не снимать метку Convert Domain Datatypes.

Рисунок 3. – Диалоговое окно Target Server.

На сервере с помощью преподавателя создать базу данных.

В меню Tools выбрать пункт Forward Engineer/Schema Generation… (рис.4).

Рисунок 4. – Меню Tools.

В открывшемся диалоговом окне SQL Server Schema Generation нажать Generate (рис. 5). (Если необходимо сохранить скрипт создания базы данных, то до нажатия кнопки Connect, следует нажать Report. Erwin предложит выбрать файл для сохранения скрипта, после указания имени файла и расширения, нажать ОК.)

Рисунок 5. – Диалоговое окно SQL Server Schema Generation.

В открывшемся окне SQL Server Connection ввести:

в поле User Name – имя пользователя;

в поле Password – свой пароль;

в поле Database – имя базы данных;

в поле Server Name– имя сервера.

Нажать Connect (рис. 6).

Рисунок 6. – Диалоговое окно SQL Server Connection.

Появится диалоговое окно Generate Database Schema, в котором отражаются результаты. При возникновении ошибки генерация приостанавливается. Для продолжения необходимо выбрать Continue. Чтобы при возникновении ошибки процесс генерации не останавливался, необходимо снять метку Stop If Failure (рис. 7). Нажать ОК.

Рисунок 7. – Диалоговое окно Generate Database Schema.

Вопросы:

1. На каком уровне производится генерация схемы данных?

2. Как установить связь с базой данных?

3. Опишите процесс генерации схемы базы данных?

4. Какая команда выполняет генерацию базы данных?

5. Как получить скрипт создания базы данных?

6. Какая информация хранится в скрипте?

7. Как отключить остановку генерации при возникновении ошибок?

8. Как изменить текущую базу данных?

9. Почему генерацию нельзя проводить на логическом уровне?

10.

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

Чтобы получить доступ к базе данных SQL Server необходимо определить источник данных. Источник данных ODBC – это ссылка на внешнюю базу данных. Для каждой базы данных сервера, с которой работает Access, должен быть создан источник данных. К источника данных обращаются по именам. Создать источник данных позволяет программа – администратор ODBC.

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

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

Системный источник данных SYSTEM DSN - Предназначен для всех пользователей и системных служб на данном компьютере.

Файловый источник данных FILE DSN – предназначен для получения доступа к базе данных многими пользователями разных компьютеров сети, на которых установлены одинаковые драйверы.

Создание User DSN

1. Выполните команду Пуск – Настройка – Панель управления –Администрирование – Источники данных (ODBC).

В открывшемся окне Администратора источников данных ODBC на вкладке Драйверы будут представлены все установленные драйверы ODBC. Убедитесь, что на вашем компьютере установлен ODBC драйвер SQL Server.

Рисунок 8. – Окно Администратора источников данных ODBC вкладка Драйверы

2. Перейдите на вкладку Пользовательский DSN. Первоначально на ней нет источников данных для SQL Server. Нажмите кнопку Добавить. В открывшемся окне Создание нового источника данных выберите драйвер SQL Server, для которого создается источник, и нажмите кнопку Готово.

Рисунок 10. – Создание нового источника данных

3. Включается мастер создания нового источника данных.

ü В первом окне мастера введите:

· В поле Имя - имя источника данных, например Istok, которое будет использоваться при определении источника данных во время подключения приложения к базе данных сервера;

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

· В списке Сервер укажите сервер A518-1 , с которым нужно соединиться для получения доступа к базе данных.

Рисунок 11. – Образец заполнения.

ü Во втором окне мастера выберите способ проверки прав доступа пользователя к SQL Server при подключении к нему. К SQL Server можно подключаться по доверительному соединению, при котором пользователь, зарегистрировавшийся в сети Windows NT и представленный в списке пользователей сервера, не проверяется дополнительно. Этот режим задается при выборе переключателя Проверка подлинности учетной записи SQL Server. Включите переключатель «Получить данные, используемые по умолчанию, от SQL Server». В поле «Пользователь» введите свою фамилию, в поле «Пользователь» введите свой пароль.

Рисунок 12. – Проверка подлинности пользователя.

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

ü Значения остальных параметров в окне Мастера задаются по умолчанию.

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

5. После выполнения перечисленных действий в окне Администратора источников данных ODBC на вкладке Пользовательский DSN в списке появится строка с именем нового источника данных Istok.

Создание связи с таблицами баз данных SQL Server

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

Для создания связи с внешними таблицами в новой пустой базе данных Access выполните команду Файл – Внешние данные – Связь с таблицами. Для создания связи с таблицами из внешней базы данных ODBC, размещенной на SQL Server, в открывшемся окне Связь, выберите в списке Тип файлов строку ODBC Databases ().

Рисунок 13. – Выбор типа связи

В следующем окне Выбор источника данных выберите источник данных ODBC Istok, обеспечивающий подключение к нужному серверу и базе данных (Рис. 14).

В окне регистрации пользователя включите флажок «Использовать доверительное соединение».

Рисунок 14. – Выбор источника данных

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

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

Вопросы:

1. Какие существуют типы источников данных ODBC?

2. Как создается пользовательский DNS?

3. Для чего создается системный DNS?

4. Какой драйвер должен быть подключен на компьютере для создания пользовательского DNS?

5. Какие виды идентификации можно использовать?

6. Как протестировать созданный источник данных?

7. Как установить связь с таблицами?

8. Что необходимо сделать, чтобы в связанную таблицу можно было добавлять данные?

9. Обязательно ли все таблицы с базы данных попадут в текущую базу?

10. Можно ли делать изменения в структуре таблиц с помощью Access?

Создание хранимых процедур

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

CREATE PROCEDURE] procedure_name [; number]

[ {^parameter data_type} [VARYING] [= default] [OUTPUT] ] [,...n]

[WITH { RECOMPILE

| ENCRYPTION

| RECOMPILE. ENCRYPTION } ]

[FOR REPLICATION]

AS sql_statement [...n]

Синтаксис имеет следующее назначение:

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

number — параметр определяет идентификационный номер хранимой процедуры, однозначно определяющий ее в группе процедур;

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

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

OUTPUT — использование этого ключевого слова определяет указанный параметр как выходной;

VARYING — ключевое слово, которое используется совместно с параметром, относящимся к типу данных cursor. Определяет, что в качестве выходного параметра будет представлено результирующее множество;

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

RECOMPILE — ключевое слово, предписывающее системе создавать план выполнения процедуры при каждом ее вызове;

FOR REPLICATION — процедура, определенная с использованием данного ключевого слова, предназначена исключительно для осуществления процесса репликации. Вы не можете сочетать это ключевое слово с ключевым словом WITH RECOMPILE;

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

AS — ключевое слово, определяющее начало кода хранимой процедуры. После этого ключевого слова следуют команды Transact-SQL, которые и составляют непосредственно тело процедуры (sql statement). Здесь можно использовать любые команды, включая вызов других хранимых процедур, за исключением команд, начинающихся с ключевого слова CREATE.

Примеры

1. Список выдач книг за текущий день.

CREATE PROCEDURE CpicokVidach

SELECT /*Перечисляем поля, которые будут выведены в результате запроса */

Пользование_библиотекой2.Табельный_номер, COUNT(Пользование_библиотекой2.Дата_выдачи)

FROM /*указываем имя таблицы из которых выбираются записи*/

Пользование_библиотекой2

WHERE /*задаем условие отбора*/

Пользование_библиотекой2.Дата_выдачи=(SELECT GETDATE())

GROUP BY /*производится группировка по указанному полю*/

Табельный_номер

/*SELECT GETDATE() позволяет получить текущую дату (год, месяц, число)

COUNT (< поле> ) возвращает количество записей какого-либо поля*/

2. Количество экземпляров какой-либо книги.

CREATE PROCEDURE KolExzemplarov

/*Объявляем необходимые переменные*/

@ISBN varchar(20)

/* Следующая конструкция проверяет, существуют ли записи в таблице «Книги» с заданным ISBN*/

IF not EXISTS (SELECT * FROM Книга WHERE ISBN = @ISBN)

RETURN 0 /*Вызывает конец процедуры KolExzemplarov */

SELECT Экземпляр.ISBN

INTO TEMP1 /*Сохраняет выбранные поля во временной таблице Temp1*/

FROM Экземпляр

WHERE ISBN = @ISBN

SELECT COUNT(ISBN) /*Count подсчитывает количество неповторяющихся записей поля ISBN*/

FROM TEMP1

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

CREATE PROCEDURE CpicokKnigCtudenta

@Chit_nom int /*Объявляем необходимые переменные*/

AS SELECT Студенты.Имя, Студенты.Фамилия, Пользование_библиотекой2.Шифр, Книга.Автор, Книга.Название

FROM /*указываем имена таблиц, из которых выбираются записи*/

Книга, Экземпляр, Студенты, Пользование_библиотекой2

WHERE (Студенты.Читательский_номер = Пользование_библиотекой2.Читательский_номер) AND (Экземпляр.Шифр = Пользование_библиотекой2.Шифр) AND (Экземпляр.ISBN =Книга.ISBN)

/* AND позволяет задать в операторе WHERE несколько условий, которые должны выполняться одновременно*/

4. Удаление из таблицы «Студенты». Допустимо, если в таблице «Пользование библиотекой2» нет ссылающихся записей.

CREATE PROCEDURE DeleteStudent

@Chit_nom int /*Объявляем необходимые переменные*/

AS /*Проверяем, если ссылающиеся записи, если записей нет, разрешается удаление.*/

IF not EXISTS (SELECT * FROM Пользование_библиотекой2 WHERE Читательский_номер=@Chit_nom)

DELETE /*Оператор удаления*/

FROM Студенты /*Имя таблицы, откуда нужно удалить*/

WHERE /*Условие удаления – удаляем строку, для которой значение поля Читательский_номер совпадает с нужным*/

Читательский_номер=@Chit_nom

5. Вставка в таблицу «Заказы». Разрешена, если в таблицах «Книга» и «Преподаватели» есть записи, на которые будет ссылаться новая запись.

PROCEDURE NewZakaz

@Kolvo int, /*Объявляем необходимые переменные*/

@data_zakaza datetime,

@Chit_nomer int,

@ISBN varchar(20)

AS /*Проверяем, есть ли запись в таблице «Заказы» с такими же значениями ключевых полей, как у новой записи*/

IF EXISTS (SELECT * FROM Заказы WHERE ISBN = @ISBN AND Читательский_номер=@Chit_nomer)

RETURN 0 /*Если есть, завершаем выполнение процедуры*/

IF EXISTS (SELECT * FROM Преподаватели WHERE Читательский_номер = @Chit_nomer)

/*Проверили, есть ли в «Преподаватели» соответствующая запись*/

IF EXISTS (SELECT * FROM Книга WHERE ISBN = @ISBN)

/*Проверили, есть ли в «Книга» соответствующая запись*/

INSERT INTO Заказы /*Указываем таблицу, куда вставляем запись*/

VALUES ( @Kolvo, @data_zakaza, @Chit_nomer, @ISBN) /*Указываем какие значения*/

6. Обновление таблицы «Студенты». Изменение фамилии студента.

CREATE PROCEDURE UpdateStudent

@Chit_nom int, /*Объявляем необходимые переменные*/

@Fam varchar(20)

IF EXISTS (SELECT * FROM Студенты /*Проверяем, существуют ли студенты, */

WHERE Читательский_номер = @Chit_nom) /*читательский номер которых равен искомому*/

UPDATE Студенты /*Если такие есть обновляем «Студенты»

SET Фамилия=@Fam /*полю фамилия присваиваем новое значение*/

WHERE Читательский_номер = @Chit_nom /*если читательский номер записи равен искомому*/

7. Вставка в таблицу «Пользование библиотекой2». Разрешается, если есть в таблицах «Студенты», «Сотрудники_библиотеки» и «Экземпляр» соответствующие записи.

ALTER PROCEDURE NewPolzovanieStydentov

@Chit_nomer int, /*Объявляем необходимые переменные*/

@data_vidachi datetime,

@data_priema datetime,

@tab_nomer int,

@Shifr varchar(20)

IF EXISTS (SELECT * FROM Пользование_библиотекой2 /*Проверяем, нет ли уже в таблице */

WHERE Шифр = @Shifr AND /*записи с таким же значением первичного ключа*/

Читательский_номер=@Chit_nomer)

RETURN 0 /*если есть, завершается процедура*/

IF EXISTS (SELECT * FROM Студенты /*проверяем наличие соответствующей записи в*/

WHERE Читательский_номер = @Chit_nomer) /*таблице «Студенты»*/

IF EXISTS (SELECT * FROM Экземпляр /*проверяем наличие соответствующей записи в*/

WHERE Шифр = @Shifr) /*таблице «Экземпляр»*/

IF EXISTS (SELECT * FROM Сотрудники_библиотеки /*проверяем наличие соответствующей */

WHERE табельный_номер = @tab_nomer) /* записи в таблице «Сотрудники_библиотеки»*/

INSERT /*если условия выполняются, добавляем*/

INTO Пользование_библиотекой2 /*в таблицу новые значения*/

VALUES ( @Chit_nomer, @data_vidachi, @data_priema, @tab_nomer, @Shifr)

8. Вставка в таблицу «Сотрудники_библиотеки». Проверяется, наличие соответствующей записи в поле номер-отдела в таблице «Отделы».

CREATE PROCEDURE NewSotrudnik

@Tab_nom int, /*Объявляем необходимые переменные*/

@Fam varchar(20),

@Name varchar(20),

@Sec_name varchar(20),

@data_rogd datetime,

@Dolgn varchar(20),

@Nom_otd int

IF EXISTS (SELECT * FROM Сотрудники_библиотеки /*Проверяем, нет ли уже в таблице */

WHERE Табельный_номер = @Tab_nom) /*записи с таким же значением первичного ключа*/

RETURN 0 /*если есть, завершается процедура*/

IF EXISTS (SELECT * FROM Отделы /*Проверяем, есть ли уже в таблице «Отделы» */

WHERE Номер_отдела = @Nom_otd) /*записи с таким же значением поля номер_отдела*/

INSERT /*если условие выполняется, добавляем*/

INTO Сотрудники_библиотеки /*в таблицу новые значения*/

VALUES ( @Tab_nom, @Fam, @Name, @Sec_name, @data_rogd, @Dolgn, @Nom_otd)

9. Сколько существует должностей в библиотеке.

CREATE PROCEDURE KolDolgn

AS SELECT COUNT (DISTINCT Сотрудники_библиотеки.должность)

FROM Сотрудники_библиотеки /*COUNT (DISTINCT < поле> ) подсчитывает количество разноименных значений какого-либо поля в таблице*/

10. Обновление таблицы «Отделы». Изменился начальник отдела.

ALTER PROCEDURE UpdateOtdel

@Nom_otdela int,

@Fam_New_Nach_otd varchar(20),

@Tab_Nom_New_Nach_otd int

IF not EXISTS (SELECT * FROM Сотрудники_библиотеки /*Условие проверяет, есть ли в */

WHERE табельный_номер = @Tab_Nom_New_Nach_otd /* библиотеке сотрудник с искомыми*/

AND фамилия=@Fam_New_Nach_otd) /*фамилией и табельным номером*/

RETURN 0 /*если нет, завершается процедура*/

IF EXISTS (SELECT * FROM Отделы /*Условие проверяет, есть ли в библиотеке*/

WHERE Номер_отдела = @Nom_otdela) /*искомый номер отдела*/

UPDATE Отделы

SET фамилия_начальника_отдела=@Fam_New_Nach_otd /*меняем значение поля «фамилия начальника отдела» на новое*/

UPDATE Отделы

SET табельный_номер_начальника=@Tab_Nom_New_Nach_otd /*меняем значение поля «табельный номер начальника отдела» на новое*/

WHERE Номер_отдела = @Nom_otdela /*меняем только для записи, у которой значение «номер_отдела» равно искомому*/

Вопросы:

1. Что такое хранимая процедура?

2. Чем хранимая процедура отличается от обычной процедуры?

3. С помощью какой команды можно создать хранимую процедуру?

4. С помощью какой команды можно изменять хранимую процедуру?

5. Какие стандартные виды хранимых процедур существуют?

6. Опишите структуру хранимой процедуры.

7. Для чего используется функция COUNT?

8. Как описываются переменные, используемые в процедуре?

9. Как вызвать хранимую процедуру?

10. Для чего необходим оператор AS?

Триггеры

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

Существует три типа триггеров в зависимости от команд, на которые они реагируют:

1) Триггеры на вставку.

2) Триггеры на обновление.

3) Триггеры на удаление.

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

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

CREATE TRIGGER trigger_name

ON TABLE

[WITH ENCRYPTION]

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

[WITH APPEND]

[NOT FOR REPLICATION]

sql_statement [...n]

}

Trigger_name – задает имя триггера, с помощью которого он будет распознаваться хранимыми процедурами и командами Transact SQL. Имя триггера должно быть уникальным в пределах БД.

TABLE – имя таблицы БД, к которой будет привязан триггер.

WITH ENCRYPTION – при указании этой опции сервер выполняет шифрование кода триггера.

[DELETE] [, ] [INSERT] [, ] [UPDATE] – эта конструкция определяет, на какие автоматы будет реагировать триггер. При создании триггера должно быть указано хотя бы одно из этих ключевых слов, допускается создание триггера, реагирующего на две или три команды.

WITH APPEND – указание этого ключевого слова требуется для обеспечения совместимости с более ранними версиями SQL-сервер.

NOT FOR REPLICATION – запрещает запуск триггера при модификации таблиц с помощью репликации.

sql_statement – определяет набор команд, которые будут выполняться при запуске триггера.

Второй вариант данной команды:

CREATE TRIGGER trigger_name ON TABLE

[WITH ENCRYPTION]

{FOR { [[INSERT] [, ] [UPDATE]}

[WITH APPEND]

[NOT FOR REPLICATION]

{ IF UPDATE (column)

[ { AND/OR }

UPDATE(column) ]

[..n]}

sql_statement [...n]}}

FOR { [[INSERT] [, ] [UPDATE] – эта инструкция определяет, при выполнении какой команды будет запускаться триггер.

IF UPDATE (column) – использование этого параметра позволяет выполнить триггер при модификации конкретной колонки таблицы.

AND/OR UPDATE(column) – применение совпадает с предыдущим параметром, если необходимо выполнить запуск триггера при модификации нескольких колонок. Аргумент column задает имя колонки, при модификации которой будет производиться запуск триггера. Ключевое слово AND предписывает запускать триггер только в том случае, если были модифицированы обе колонки, указанные в этой и в предыдущей конструкции. При использовании ключевого слова OR триггер будет выполнен при выполнении изменений в любой из колонок. Допускается использование нескольких конструкций AND/OR UPDATE(column).

Для изменения триггера используется команда ALTER TRIGGER.

Примеры

1. Триггер, который будет запрещать удаление записей таблицы «Пользование библиотекой», если текущий пользователь не владелец базы данных и если поле «дата выдачи» содержит какое-либо значение.

CREATE TRIGGER udalenie /*Обьявляем имя триггера*/

ON Пользование_библиотекой2 /*Указываем имя таблицы, с которой будет связан триггер*/

FOR DELETE /*Указываем операцию, на кот. будет срабатывать триггер (здесь на удаление)*/

IF ( SELECT count(*) /*проверяет*/

from Пользование_библиотекой2 /*записи из таблицы «Пользование библиотекой»*/

where Пользование_библиотекой2.дата_выдачи is not null)> 0 /*условие проверяет наличие записи в поле «дата выдачи». Если count возвращает значение отличное от нуля (означает, что запись есть) то первое условие IF не выполнено*/

AND (CURRENT_USER < > 'dbo') /*вызывается функция определения имени текущего пользователя и проверяется, владелец ли он*/

BEGIN

PRINT 'у вас нет прав на удаление этой записи' /*выдача сообщения о неудаче операции*/

ROLLBACK TRANSACTION /*откат (отмена) транзакции*/

END

2. Проверяет правильность номера отдела. Существует лишь 5 отделов, которым присваиваются номера от 10 до 14 включительно.

CREATE TRIGGER dobavlenie

ON Отделы

FOR INSERT

DECLARE @@f int /*Объявляем переменную*/

Set @@f=10 /*Присваиваем ей значение*/

IF NOT EXISTS (SELECT * FROM Отделы, inserted

WHERE Отделы.Номер_отдела = inserted.Номер_отдела)

Set @@f=0

IF EXISTS (SELECT * FROM Отделы, inserted

WHERE inserted.Номер_отдела> 14 OR inserted.Номер_отдела< 10) /*Если номер отдела не > 14 или < 10, то такой отдел не существует*/

Set @@f=0 /*Меняем значение переменной*/

If @@f=0 /*если f=0, значит были ошибки*/

BEGIN

PRINT 'Неверно введены данные' /*выдача сообщения о неудаче операции*/

ROLLBACK TRANSACTION /*откат (отмена) транзакции*/

END

/*inserted – временная таблица, куда заносятся добавляемые данные*/

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

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

CREATE TABLE DeletedItem (

[Читательский_номер] [int] NOT NULL, /*объявляем поля таблицы*/

[имя] [varchar] (25) NULL,

[отчество] [varchar] (75) NULL,

[фамилия] [varchar] (25) NULL,

[должность] [varchar] (35) NULL,

[Имя_пользователя] [varchar] (50) NULL,

[Дата_удаления] [datetime] NULL

) ON [PRIMARY]

Сам триггер выглядит следующим образом;

CREATE TRIGGER deletedby

ON Преподаватели /*Связываем триггер с таблицей Преподаватели*/

FOR DELETE

INSERT INTO DeletedItem

(Читательский_номер, имя, отчество, /*указываем, какие поля нужно вставить*/

фамилия, должность, Имя_пользователя, Дата_удаления)

SELECT Читательский_номер, имя, отчество, фамилия, должность,

SYSTEM_USER, /*функция определяет текущего пользователя*/

getdate() /*функция возвращает текущую дату*/

FROM deleted /*deleted – временная таблица, куда заносятся удаляемые данные*/

4. Триггер, который записывает при добавлении записей в таблицы «Сотрудники_Библиотеки» в отдельную таблицу информацию о дате удаления, пользователе.

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

CREATE TABLE InsertedItemSotrydniki

(

[табельный_номер] [int] NOT NULL, /*объявляем поля таблицы*/

[фамилия] [varchar] (25) NULL,

[имя] [varchar] (25) NULL,

[отчество] [varchar] (75) NULL,

[Дата_рождения] [datetime] NULL,

[должность] [varchar] (35) NULL,

[Имя_пользователя] [varchar] (50) NULL,

[Дата_добавления] [datetime] NULL

) ON [PRIMARY]

Сам триггер выглядит следующим образом;

CREATE TRIGGER infoInsertSotrud

ON Сотрудники_библиотеки /*Связываем триггер с таблицей Сотрудники_библиотеки */

FOR INSERT

INSERT INTO InsertedItemSotrydniki (Табельный_номер, /*указываем, какие поля нужно вставить*/

фамилия, имя, отчество, Дата_рождения, должность, Имя_пользователя, Дата_добавления)

SELECT Табельный_номер, фамилия, имя, отчество, Дата_рождения, должность,

SYSTEM_USER, /*функция определяет текущего пользователя*/

getdate() /*функция возвращает текущую дату*/

FROM inserted /*inserted – временная таблица, куда заносятся добавляемые данные*/

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

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

CREATE TABLE UpdatedStudents

(

[читательский_номер] [int] NOT NULL,

[фамилия] [varchar] (25) NULL,

[Имя_пользователя] [varchar] (50) NULL,

[Дата_добавления] [datetime] NULL

) ON [PRIMARY]

Триггер выглядит следующем образом:

CREATE TRIGGER infoUpdateStudent

ON Студенты /*Связываем с таблицей*/

FOR UPDATE

INSERT INTO UpdatedStudents (Читательский_номер, /*указываем, какие поля нужно вставить*/

фамилия, Имя_пользователя, Дата_добавления)

SELECT Читательский_номер, фамилия, /*указываем откуда*/

SYSTEM_USER, /*функция определяет текущего пользователя*/

getdate() /*функция возвращает текущую дату*/

FROM updated /*updated – временная таблица, куда заносятся данные*/

6. Триггер запрещает изменять в таблице «Пользование_библиотекой2» запись полей «дата_приема» и «дата выдачи» в воскресенье.

ALTER TRIGGER proverka ON Пользование_библиотекой2

FOR UPDATE

IF UPDATE(дата_приема) /*Проверяет, какое поле обновляется*/

AND datename(dw, GETDATE()) in ('Sunday') /*Проверяет, является ли день воскресеньем*/

BEGIN

PRINT 'В воскресение нельзя принимать книги'

ROLLBACK TRANSACTION

END

IF UPDATE(дата_выдачи) /*Проверяет, какое поле обновляется*/

AND datename(dw, GETDATE()) in ('Sunday') /*Проверяет, является ли день воскресеньем*/

BEGIN

PRINT 'В воскресение нельзя принимать книги'

ROLLBACK TRANSACTION

END

/* datename(dw, GETDATE()) – GETDATE()получает текущую дату, datename(dw, < дата> ) получает название дня недели*/

7. Триггер, который записывает при удалении записей из таблицы «Сотрудники_библиотеки» в отдельную таблицу информацию о дате удаления, пользователе,

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

CREATE TABLE DeletedSotrudLibrary (

[Табельный_номер] [int] NOT NULL, /*объявляем поля таблицы*/

[имя] [varchar] (25) NULL,

[фамилия] [varchar] (25) NULL,

[отчество] [varchar] (75) NULL,

[дата_рождения] [datetime] NULL,

[должность] [varchar] (35) NULL,

[номер_отдела] [int] NOT NULL,

[Имя_пользователя] [varchar] (50) NULL,

[Дата_удаления] [datetime] NULL

) ON [PRIMARY]

Триггер:

CREATE TRIGGER deletesotrud

ON Сотрудники_библиотеки /*Связываем с таблицей*/

FOR DELETE

INSERT INTO DeletedSotrudLibrary /*Указываем таблицу, куда будет производиться запись*/

(Табельный_номер, имя, фамилия, отчество, дата_рождения, /*Пречисляем поля таблицы*/

должность, номер_отдела, Имя_пользователя, Дата_удаления)

SELECT /*Причисляем вносимые значения*/

Табельный_номер, имя, фамилия, отчество, дата_рождения, должность, номер_отдела,

SYSTEM_USER, getdate()

FROM deleted /*deleted – временная таблица, куда заносятся удаляемые данные*/

8. Триггер, который записывает при добавлении записей в таблицы «Преподаватели» в отдельную таблицу информацию о дате удаления, пользователе.

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

CREATE TABLE InsertedItemPrepdavateli

(

[Читательский_номер] [int] NOT NULL, /*объявляем поля таблицы*/

[имя] [varchar] (25) NULL,

[отчество] [varchar] (75) NULL,

[фамилия] [varchar] (25) NULL,

[должность] [varchar] (35) NULL,

[Имя_пользователя] [varchar] (50) NULL,

[Дата_удаления] [datetime] NULL

) ON [PRIMARY]

Триггер:

CREATE TRIGGER infoInsertPred

ON Преподаватели

FOR INSERT

INSERT INTO InsertedItemPrepdavateli

(Читательский_номер, имя, отчество, фамилия, должность, Имя_пользователя, Дата_удаления)

SELECT Читательский_номер, имя, отчество, фамилия, должность,

SYSTEM_USER, getdate()

FROM inserted

9. Триггер, запрещающий ввод значения в поле «год_поступления», если оно превышает номер текущего года.

CREATE TRIGGER proverka_Studentov ON Студенты FOR INSERT

DECLARE @@t int /*Объявляем переменную*/

Set @@t=5 /*Присваиваем ей значение*/

IF NOT EXISTS (SELECT * FROM Студенты, inserted

WHERE Студенты.год_поступления = inserted.год_поступления)

Set @@t=0

IF EXISTS (SELECT * FROM Студенты, inserted

WHERE inserted.год_поступления> YEAR(GETDATE())) /*Проверяем, больше ли значения вводимого года, чем у текущего*/

Set @@t=0 /*Если да, то меняем значение переменной*/

If @@t=0 /*если f=0, значит были ошибки*/

BEGIN

PRINT 'Неверно введен год поступления' /*выдача сообщения о неудаче операции*/

ROLLBACK TRANSACTION /*откат (отмена) транзакции*/

END

10. Триггер, запрещающий обновление значения в поле «год_отчисления», если оно превышает номер текущего года.

CREATE TRIGGER proverka_otchislenia_Studentov

ON Студенты

FOR UPDATE

DECLARE @@t int /*Объявляем переменную*/

Set @@t=5 /*Присваиваем ей значение*/

IF NOT EXISTS (SELECT * FROM Студенты, inserted

WHERE Студенты.год_окончания = inserted.год_окончания)

Set @@t=0

IF EXISTS (SELECT * FROM Студенты, inserted

WHERE inserted.год_окончания> YEAR(GETDATE())) /*Проверяем, больше ли значения вводимого года, чем у текущего*/

Set @@t=0 /*Меняем значение переменной*/

If @@t=0 /*если f=0, значит были ошибки*/

BEGIN

PRINT 'Неверно введен год отчисления' /*выдача сообщения о неудаче операции*/

ROLLBACK TRANSACTION /*откат (отмена) транзакции*/

END

Вопросы:

1. Что такое триггер?

2. Чем хранимая процедура отличается от триггера?

3. С помощью какой команды можно создать триггер?

4. С помощью какой команды можно изменять триггер?

5. Какие стандартные виды триггеров существуют?

6. Опишите структуру триггера.

7. Для чего используется функция ROLLBACK TRANSACTION?

8. Как описываются переменные, используемые в триггером?

9. Как вызвать триггер?

10. Для чего предназначен триггер с оператором FOR INSERT?

Представления

Представление (View) для конечных пользователей выглядит как таблица, но при этом само не содержит данных, а лишь представляет данные, расположенные в таблице. Физически представление реализовано в виде SQL-запроса, на основе которого производится выборка данных из одной или нескольких таблиц или представлений.

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

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

Создание представлений с использованием Transact-SQL

CREATE VIEW view_name [(column [,...n])]

[WITH ENCRYPTION]

select_statement

[WITH CHECK OPTION]

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

column — имя колонки, которое будет использоваться в представлении. Максимальная длина имени составляет 128 символов. По умолчанию имена колонок в представлении соответствуют именам колонок в исходных таблицах. Явное указание имени колонки требуется при использовании вычисляемых колонок или при объединении нескольких таблиц, имеющих колонки с одинаковыми именами. Имена колонок перечисляются через запятую в соответствии с их порядком в представлении. Имена колонок можно указывать в команде SELECT, определяющей представление.

WITH ENCRYPTION - использование этого параметра предписывает серверу шифровать код SQL-запроса. Это гарантирует, что пользователи не смогут просмотреть код запроса и использовать его. Если при определении представления необходимо скрыть имена исходных таблиц и колонок, а также алгоритм объединения данных, то необходимо использовать эту опцию.

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

1) Нельзя создавать новую таблицу на основе результатов, полученных в ходе выполнения запроса, то есть запрещается использование параметра INTO.2) Нельзя проводить выборку данных из временных таблиц, то есть нельзя использовать имена таблиц, начинающихся на # или ##.

В запрос нельзя включать операции вычисления и группировки, то есть запрещается указание параметров ORDER BY, COMPUTE и COMPUTE BY.

Для удаления представления используется команда Transact SQL DROP VIEW{view […n]}. За один раз можно удалить несколько представлений.

Примеры

1.Вывод ограниченной информации о студентах с фамилией, оканчивающейся на «ов».

CREATE VIEW infoStudent /*Указываем имя представления*/

SELECT /*Указываем, какие поля будут выведены*/

Студенты.Имя, Студенты.Фамилия, Студенты.факультет, Студенты.специальность

FROM Студенты /*Из какой таблицы*/

WHERE Студенты.Фамилия LIKE ‘[]ов’

2. Информация о замене экземпляров по программированию на С++.

CREATE VIEW infoZamena

SELECT /*Указываем, какие поля, из каких таблиц будут выведены*/

Книга.Автор, Книга.Название, Экземпляр.Шифр, Замена_экземпляров.Номер_акта_замены

⇐ Предыдущая 1 2 3 4 5 678 Следующая ⇒