CREATE TRIGGER proverka_komandirovka

ON Командировки

FOR INSERT // Триггер будет срабатывать на вставку в таблице «Командировки»

AS

IF EXISTS (SELECT * FROM Командировки, inserted

WHERE inserted.Дата_с>YEAR(DATEADD(YEAR, +1, GETDATE())))

// Проверка при вводе строки в таблицу, чтобы значение года в колонке «Дата_с» не было равно год+1 от текущей даты, установленной на данном сервере

BEGIN

PRINT 'Неверно введена дата командировки!' // Предупреждающее сообщение

ROLLBACK TRANSACTION // Откат транзакции

END

4. Будет срабатывать при обновлении тарифной ставки в таблице «Отделы» и выдавать изменённую среднюю тарифную ставку по отделам

CREATE TRIGGER Upd_Tarif

ON Отделы

FOR UPDATE // Триггер будет срабатывать на обновление в таблице «Отделы»

AS

IF UPDATE(Тарифная_ставка)

BEGIN

PRINT 'Тарифная ставка повышена!' // Предупреждающее сообщение

SELECT AVG(Тарифная_ставка) AS 'Изменённая средняя тарифная ставка' // AVG - функция, которая возвращает среднее значение от всех значений в колонке «Тарифная_ставка»

FROM Отделы

END

5. Триггер, который будет проверять, чтобы в таблицу «Табель_отпусков» не вносились увольнения, т.к. для этого существует отдельная таблица

CREATE TRIGGER Ins_Otpusk

ON Табель_отпусков

FOR INSERT // Триггер будет срабатывать на вставку данных в таблицу «Табель_отпусков»

AS

IF EXISTS (SELECT * FROM Табель_отпусков, inserted

WHERE inserted.Тип_отпуска = 'увольнение') // Проверка при вводе строки в таблицу, чтобы значение в колонке «Типа_отпуска» не было «Увольнение»

BEGIN

PRINT 'Ошибка! Проверьте тип отпуска.' // Предупреждающее сообщение

SELECT CURRENT_TIMESTAMP // CURRENT_TIMESTAMP - функция возвращающая значение текущей даты, установленной на данном сервере

ROLLBACK TRANSACTION // Откат транзакции

END

6. Триггер проверяет, чтобы при добавлении нового отдела в таблицу «Отделы» в поле «Количество_штатных_единиц» не было нуля. В противном случае появляется сообщение с предупреждением

CREATE TRIGGER Ins_Otdel

ON Отделы

FOR INSERT // Триггер будет срабатывать на вставку в таблицу «Отделы»

AS

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

WHERE inserted.Количество_штатных_единиц = 0) // Проверка при вводе строки в таблицу, чтобы значение в колонке «Количество_штатных_единиц» не было равно 0

BEGIN

PRINT 'Ошибка! В отделе не может работать 0 человек.'

// Предупреждающее сообщение

SELECT GETUTCDATE() AS 'Дата:' // GETUTCDATE - функция, возвращающая значение текущей даты, установленной на данном сервере

ROLLBACK TRANSACTION // Откат транзакции

END

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

CREATE TABLE DeletedWorker (

[Личный_номер] [int] NOT NULL ,

[Фамилия] [varchar] (50) NULL ,

[Имя] [varchar] (50) NULL ,

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

[Образование][varchar] (100) NULL,

[Должность] [varchar] (100) NULL ,

[Звание] [varchar](100) NULL ,

[Адрес_город][varchar] (50) NULL,

[Адрес_улица][varchar] (50) NULL,

[Адрес_дом][int] NOT NULL,

[Адрес_квартира][varchar] (20) NULL,

[Название_отдела][varchar] (100) NOT NULL,

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

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

ON [PRIMARY]

CREATE TRIGGER DelWorker

ON Сотрудники

FOR DELETE // Триггер будет срабатывать на удаление данных из таблицы «Сотрудники»

AS

INSERT INTO DeletedWorker // Вставить удаляемые данные в таблицу DeletedWorker

(Личный_номер , Фамилия , Имя, Отчество, Образование, Должность, Звание, Адрес_город, Адрес_улица, Адрес_дом,

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

SELECT Личный_номер , Фамилия , Имя, Отчество, Образование, Должность, Звание, Адрес_город, Адрес_улица,

Адрес_дом, Адрес_квартира, Название_отдела, SYSTEM_USER, GETDATE()

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

SELECT @@ROWCOUNT // ROWCOUNT - функция, которая возвращает число затронутых при выполнении транзакции строк

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

CREATE TRIGGER Ins_Komandirovka

ON Командировки

FOR INSERT // Триггер будет срабатывать на вставку данных в таблицу «Командировки»

AS

IF EXISTS (SELECT * FROM Командировки, inserted

WHERE inserted.Место = '') // Проверка при вводе строки в таблицу, чтобы значение в колонке «Место» не было пустое

BEGIN

PRINT 'Ошибка! Необходимо указать место командировки.'

// Предупреждающее сообщение

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name' // SPID - функция, которая возвращает идентификатор сеанса для текущего процесса, SYSTEM_USER - логин текущего пользователя, USER - имя текущего пользователя

ROLLBACK TRANSACTION // Откат транзакции

END

9. Триггер, который проверяет, чтобы при добавлении записи втаблицу «Спецзвания» было заполнено поле «Служба_в_ВС»

CREATE TRIGGER proverka_speczvania

ON Спецзвания

FOR INSERT // Триггер будет срабатывать на вставку данных в таблицу «Спецзвания»

AS

IF EXISTS (SELECT * FROM Спецзвания, inserted

WHERE inserted.Служба_в_ВС = '') // Проверка при вводе строки в таблицу, чтобы значение в колонке «Служба_в_ВС» не было пустое

BEGIN

PRINT 'Отметьте выслугу!' // Предупреждающее сообщение

SELECT SESSION_USER AS 'Session User' // SESSION_USER - функция, которая возвращает имя пользователя текущей сессии

ROLLBACK TRANSACTION // Откат транзакции

END

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

CREATE TRIGGER Worker

ON Сотрудники

FOR INSERT // Триггер будет срабатывать на вставку данных в таблицу «Сотрудники»

AS

IF EXISTS (SELECT * FROM Сотрудники, inserted

WHERE inserted.Фамилия = '' OR inserted.Имя = '' OR inserted.Отчество = ''

OR inserted.Образование = '' OR inserted.Адрес_город = '' OR inserted.Адрес_улица = '')

// Проверка при вводе строки в таблицу, чтобы значения в колонках» не было пустыми

BEGIN

PRINT 'Заполнены не все поля!' // Предупреждающее сообщение

SELECT @@SERVERNAME AS 'Server Name' // SERVERNAME - функция, возвращающая информацию об имени локального сервера

ROLLBACK TRANSACTION // Откат транзакции

END

3.3 T-SQL-определения хранимых процедур

1. Повышение тарифной ставки в отделе

CREATE PROC BasicWageRateUp

@dept varchar(100)

AS

UPDATE Отделы // Обновление таблицы «Отделы»

SET Тарифная_ставка = Тарифная_ставка * 1.9

WHERE Название_отдела = @dept // Выборка строк таблицы, где значение колонки «Название_отдела» соответствует введенному значению @dept

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

CREATE PROC BDay

@id int

AS

SELECT Сотрудники.Личный_номер, Сотрудники.Название_отдела, Сотрудники.Фамилия, Сотрудники.Имя,

Сотрудники.Отчество, Сотрудники.Должность, День_рождения.Дата_рождения,

DATEDIFF(year,dbo.День_рождения.Дата_рождения,GETDATE()) AS 'Полных лет'

// DATEDIFF - функция, которая возвращает интервал времени year, прошедшего от указанной даты Дата_рождения до текущей даты, установленной на данном сервере, GETDATE - возвращает текущую дату, установленную на данном сервере

FROM День_рождения, Сотрудники

WHERE День_рождения.Личный_номер = @id AND День_рождения.Личный_номер = Сотрудники.Личный_номер

3. Просмотр командировки определенного сотрудника по личному номеру

CREATE PROC BusinessTrip

@id int

AS

SELECT Сотрудники.Личный_номер, Сотрудники.Название_отдела, Сотрудники.Фамилия, Сотрудники.Имя,

Сотрудники.Отчество, Сотрудники.Должность, Командировки.Номер_командировки, Командировки.Место,

Командировки.Дата_с, Командировки.Срок

FROM Командировки, Сотрудники

WHERE Командировки.Личный_номер = @id AND Командировки.Личный_номер = Сотрудники.Личный_номер

4. Поиск всех командировок в определенном городе

CREATE PROC City

@city varchar(100)

AS

SELECT Сотрудники.Название_отдела, Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя,

Сотрудники.Отчество, Сотрудники.Должность,Сотрудники.Звание, Командировки.Номер_командировки,

Командировки.Дата_с

FROM Сотрудники, Командировки

WHERE Сотрудники.Личный_номер = Командировки.Личный_номер AND Командировки.Место = @city // Выборка строк таблицы, где значение колонки «Место» соответствует введенному значению @city

5. Удаление данных о сотруднике из таблицы «Сотрудники»

CREATE PROC DeleteWorker

@id int

AS

IF EXISTS (SELECT * FROM Сотрудники WHERE Личный_номер = @id)

DELETE Сотрудники // Проверка на наличие нужной строки в таблице

WHERE Личный_номер = @id // Выборка строк таблицы, где значение колонки «Личный_номер» соответствует введенному значению @id

6. Поиск уволенного сотрудника по личному номеру

CREATE PROC Dismissal

@id int

AS

SELECT Сотрудники.Личный_номер, Сотрудники.Название_отдела, Сотрудники.Фамилия, Сотрудники.Имя,

Сотрудники.Отчество, Сотрудники.Должность, Сотрудники.Звание, Увольнения.Номер_документа,

Увольнения.Дата_документа, Увольнения.Причина

FROM Увольнения, Сотрудники

WHERE Увольнения.Личный_номер = @id AND Увольнения.Личный_номер = Сотрудники.Личный_номер // Выборка строк таблицы, где значение колонки «Личный_номер» соответствует введенному значению @id

7. Поиск всех нормативных документов отдела

CREATE PROC Documents

@dept varchar(100)

AS

SELECT Нормативные_документы.Номер_нормативного_документа, Нормативные_документы.Тип,

Нормативные_документы.Дата_документа

FROM Нормативные_документы, Нормативные_документы_отделов

WHERE Нормативные_документы.Номер_нормативного_документа = Нормативные_документы_отделов.Номер_нормативного_документа

AND Название_отдела = @dept // Выборка строк таблицы, где значение колонки «Название_отдела» соответствует введенному значению @dept

8. Просмотр личного дела сотрудника по его личному номеру

CREATE PROC GetWorkerInfo

@id int

AS

SELECT * FROM Сотрудники WHERE Личный_номер = @id

// Выборка строк таблицы, где значение колонки «Личный_номер» соответствует введенному значению @id

9. Просмотр штатной расстановки отдела

CREATE PROC Lists

@dept varchar(100)

AS

SELECT Сотрудники.Название_отдела, Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Отчество, Сотрудники.Должность, Сотрудники.Звание

FROM Сотрудники

WHERE Сотрудники.Название_отдела = @dept // Выборка строк таблицы, где значение колонки «Название_отдела» соответствует введенному значению @dept

ORDER BY Фамилия // Сортирует данные, возвращаемые запросом, по фамилии

10. Добавление нового отдела в таблицу «Отделы»

CREATE PROC NewDepartment

@id int,

@dept varchar(100),

@name varchar(100),

@oName varchar(100),

@lastName varchar(100),

@kol int,

@tarif bigint,

@prim varchar(200),

@tel bigint,

@fax bigint

AS

INSERT INTO Отделы (Название_отдела, Номер_штатного_расписания, Фамилия_начальника, Имя_начальника,

Отчество_начальника, Количество_штатных_единиц, Тарифная_ставка, Примечание, Телефон, Факс) // Добавление новой строки в таблицу «Отделы»

VALUES (@id, @dept, @name, @oName, @lastName, @kol, @tarif, @prim, @tel, @fax) // Задает набор выражений значений строки

11. Добавление нового сотрудника

CREATE PROC NewWorker

@id int,

@name varchar(100),

@oName varchar(100),

@lastName varchar(100),

@education varchar(100),

@position varchar(100),

@rank varchar(100),

@town varchar(100),

@street varchar(100),

@d int,

@kv int,

@dept varchar(100)

AS

INSERT INTO Сотрудники (Личный_номер, Фамилия, Имя, Отчество, Образование, Должность, Звание, Адрес_город, Адрес_улица, Адрес_дом, Адрес_квартира, Название_отдела) // Добавление новой строки в таблицу «Сотрудники»

VALUES (@id, @name, @oName, @lastName, @education, @position, @rank, @town, @street, @d, @kv,@dept) ) // Задает набор выражений значений строки

12. Поиск приказа по его номеру

CREATE PROC Orders

@id int

AS

SELECT Приказы.Номер_приказа, Приказы_сотрудников.Название_отдела, Приказы.Ответственный, Приказы.Дата_приказа

FROM Приказы, Приказы_сотрудников

WHERE Приказы.Номер_приказа = Приказы_сотрудников.Номер_приказа AND Приказы.Номер_приказа = @id

// Выборка строк таблицы, где значение колонки «Номер_приказа» соответствует введенному значению @id

13. Поиск сотрудников, занимающий определенную должность

CREATE PROC Position

@position varchar(100)

AS

SELECT Сотрудники.Название_отдела, Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя,

Сотрудники.Отчество, Сотрудники.Должность,Сотрудники.Звание

FROM Сотрудники

WHERE Должность = @position // Выборка строк таблицы, где значение колонки «Должность» соответствует введенному значению @position

14. Поиск отдела по номеру его штатного расписания

CREATE PROC Schedule

@id int

AS

SELECT Отделы.Номер_штатного_расписания, Отделы.Название_отдела, Отделы.Фамилия_начальника, Отделы.Имя_начальника, Отделы.Отчество_начальника, Отделы.Количество_штатных_единиц, Отделы.Тарифная_ставка, Отделы.Примечание

FROM Отделы

WHERE Номер_штатного_расписания = @id // Выборка строк таблицы, где значение колонки «Номер_штатного_расписания» соответствует введенному значению @id

15. Поиск сотрудников по возрасту

CREATE PROC Search

@i int

AS

SELECT Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Отчество, Сотрудники.Должность,

День_рождения.Дата_рождения, День_рождения.Количество_полных_лет

FROM Сотрудники, День_рождения

WHERE Сотрудники.Личный_номер = День_рождения.Личный_номер

AND ISNUMERIC(День_рождения.Количество_полных_лет)<>0

AND День_рождения.Количество_полных_лет >= @i; // Выборка строк таблицы, где значение колонки «Количество_полных_лет» больше или равно введенному значению @i; ISNUMERIC проверяет, чтобы значение «Количество_полных_лет» не было равно нулю

16. Поиск сотрудника по фамилии или началу фамилии

CREATE PROC SearchWorker

@name varchar(100)

AS

SELECT * FROM Сотрудники

WHERE Фамилия LIKE @name // Выборка строк таблицы, где значение колонки «Фамилия» соответствует введенному значению @name

17. Просмотра сотрудников, которые служили в ВС

CREATE PROC SpecZvanie

@vs varchar (20)

AS

SET @vs = 'да' // Установить значение переменной, равной значению «да»

SELECT Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Отчество, Сотрудники.Должность

FROM Спецзвания, Сотрудники

WHERE Спецзвания.Личный_номер=Сотрудники.Личный_номер AND Служба_в_ВС = @vs // Выборка строк таблицы, где значение колонки «Служба_в_ВС» соответствует значению переменной @vs

18. Обновление данных о сотруднике

CREATE PROC UpdateWorker

@id int,

@position varchar (100),

@rank varchar (100)

AS

IF EXISTS (SELECT * FROM Сотрудники WHERE Личный_номер = @id ) // Проверка на наличие нужной строки в таблице «Сотрудники»

UPDATE Сотрудники // Обновление таблицы «Сотрудники»

SET Должность = @position // Установить значение в колонке «Должность», равное введенному значению переменной @position

WHERE Личный_номер = @id // Выборка строк таблицы, где значение колонки «Личный_номер» соответствует введенному значению @id

UPDATE Сотрудники

SET Звание = @rank // Установить значение в колонке «Звание», равное введенному значению переменной @ rank

WHERE Личный_номер = @id

19. Просмотр отпусков сотрудника

CREATE PROC Vacation

@id int

AS

SELECT Сотрудники.Личный_номер, Сотрудники.Название_отдела, Сотрудники.Фамилия, Сотрудники.Имя,

Сотрудники.Отчество, Сотрудники.Должность, Табель_отпусков.Номер_табеля, Табель_отпусков.Тип_отпуска,

Табель_отпусков.Дата_с, Табель_отпусков.Количество_дней

FROM Табель_отпусков, Сотрудники

WHERE Табель_отпусков.Личный_номер = @id AND Табель_отпусков.Личный_номер = Сотрудники.Личный_номер // Выборка строк таблицы, где значение колонки «Личный_номер» соответствует введенному значению @id

20. Поиск сотрудника по трудовому стажу, выше указанного

CREATE PROC WorkerExperience

@experience int

AS

SELECT distinct Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Должность, Сотрудники.Название_отдела, DATEDIFF(year,dbo.Трудовой_стаж.Дата_приёма_на_работу,GETDATE()) AS 'Стаж' // DATEDIFF - функция, которая возвращает интервал времени day, прошедшего от указанной даты Дата_с до текущей даты, установленной на данном сервере, GETDATE - возвращает текущую дату, установленную на данном сервере

FROM Сотрудники, Трудовой_стаж

WHERE Сотрудники.Личный_номер=Трудовой_стаж.Личный_номер AND Трудовой_стаж.Стаж >= @experience // Выборка строк таблицы, где значение колонки «Стаж» больше или равно введенному значению @experience

3.4 T-SQL-определения курсоров

1. Курсор для просмотра сотрудников в выбранном отделе

CREATE PROCEDURE curs1

@otdel varchar(100)

AS

DECLARE curs1 CURSOR

GLOBAL SCROLL KEYSET

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

TYPE_WARNING

// Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT

FOR

SELECT*FROM Сотрудники

WHERE Название_отдела LIKE @otdel

// Выборка строк таблицы, где значение колонки «Название_отдела» соответствует введенному значению @otdel

FOR READ ONLY // Только для чтения

open global curs1 // открытие глобального курсора

DECLARE

@@Counter int

SET @@Counter =@@CURSOR_ROWS

// присвоение переменной @@Counter значения, равного числу рядов курсора =@@CURSOR_ROWS

Select @@Counter 'Количество сотрудников в этом отделе'

CLOSE curs1 // закрытие курсора

DEALLOCATE curs1 // освобождение курсора

2. Курсор для просмотра количествa командировок в этом месяце

CREATE PROCEDURE curs2

AS

DECLARE curs2 CURSOR

GLOBAL SCROLL KEYSET

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

TYPE_WARNING

//Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT

FOR

SELECT

Командировки.Номер_командировки, Командировки.Дата_с, Командировки.Срок, Командировки.Место,

Командировки.Личный_номер, Командировки.Название_отдела

FROM Командировки

FOR UPDATE // Курсор на обновление в таблице

open global curs2 // открытие глобального курсора

DECLARE

@@nomer int,

@@date datetime,

@@srok int,

@@mesto varchar(50),

@@l_nomer int,

@@otdel varchar(100),

@@var int,

@@Counter int

SET @@Counter = 1

SET @@var = 0

WHILE @@Counter<= @@CURSOR_ROWS

// Выполняется до тех пор, пока число строк в таблице меньше или равно числу рядов курсора

BEGIN

FETCH curs2 INTO @@nomer, @@date, @@srok, @@mesto , @@l_nomer,@@otdel

// FETCH - получает определенную строку из курсора и помещает данные из столбцов выборки в переменные @@nomer, @@date, @@srok, @@mesto , @@l_nomer,@@otdel

IF (DATEDIFF(M,@@date,GETDATE())= 0) // Проверка, чтобы месяц текущей даты, установленной на данном сервере, был равен значению переменной @@date

BEGIN

SET @@var=@@var+1 // Установить значение переменной @@var большим на единицу

print @@date

END

SET @@Counter =@@Counter +1 // Установить значение переменной @@Counter большим на единицу

END

Select @@var as 'В этом месяце командировок:'

CLOSE curs2 //закрытие курсора

DEALLOCATE curs2 //освобождение курсора

3. Поиск сотрудника по фамилии

CREATE PROCEDURE curs3 // открытие глобального курсора

@fio varchar (100)

AS

DECLARE curs3 CURSOR

GLOBAL SCROLL KEYSET // глобальный прокручиваемый ключевой курсор, который будет существовать до закрытия текущего соединения

TYPE_WARNING // Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT

FOR

SELECT Сотрудники.Личный_Номер, Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Отчество, Сотрудники.Должность,

Сотрудники.Звание, Сотрудники.Название_отдела

FROM Сотрудники

FOR UPDATE // Курсор на обновление в таблице

open global curs3

DECLARE

@@id int,

@@name varchar(100),

@@oName varchar(100),

@@lastName varchar(100),

@@position varchar(100),

@@rank varchar(100),

@@dept varchar(100),

@@Counter int,

@@var int

SET @@Counter = 1

SET @@var = 0

WHILE @@Counter<= @@CURSOR_ROWS // Выполняется до тех пор, пока число строк в таблице меньше или равно числу рядов курсора

BEGIN

FETCH curs3 INTO @@id, @@name, @@oName,@@lastName,@@position,@@rank,@@dept

// FETCH - получает определенную строку из курсора и помещает данные из столбцов выборки в переменные @@id, @@name, @@oName,@@lastName,@@position,@@rank и @@dept

IF @fio = @@name // Проверка, чтобы вводимая фамилия @fio была равна значению @@name

BEGIN

Select @@id as 'Личный номер', @@name+' '+SUBSTRING(@@oName, 1, 1)+'.'+ SUBSTRING(@@lastName, 1, 1)+'.'

// SUBSTRING - функция, которая возвращает часть значения @@oName и @@lastName, чтобы «склеить» ФИО сотрудника

as 'ФИО',@@position as 'Должность', @@rank 'Звание', @@dept 'Отдел'

END

SET @@Counter =@@Counter +1 // Установить значение переменной @@Counter большим на единицу

END

CLOSE curs3 //закрытие курсора

DEALLOCATE curs3 //освобождение курсора

4. Курсор для просмотра отпусков по выбранному типу

CREATE PROCEDURE curs4

@otpusk varchar(100)

AS

DECLARE curs4 CURSOR

GLOBAL SCROLL KEYSET

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

TYPE_WARNING

// Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT

FOR

SELECT * FROM Табель_отпусков

FOR UPDATE // Курсор на обновление в таблице

open global curs4 // открытие глобального курсора

DECLARE

@@nomer int,

@@type varchar(100),

@@date datetime,

@@day int,

@@id int,

@@dept varchar(100),

@@Counter int

SET @@Counter = 1

WHILE @@COUNTER<= @@CURSOR_ROWS

// Выполняется до тех пор, пока число строк в таблице меньше или равно числу рядов курсора

BEGIN

FETCH curs4 INTO @@nomer, @@type,@@date,@@day, @@id, @@dept

// FETCH - получает определенную строку из курсора и помещает данные из столбцов выборки в переменные @@nomer, @@type,@@date,@@day, @@id и @@dept

IF(CHARINDEX(@otpusk,@@type)<>0) // Проверка, чтобы значений вводимой переменной @otpusk не было в колонке со значениями @@type

BEGIN

Select @@nomer as 'Номер табеля', @@type as 'Тип отпуска', CAST(@@date AS nvarchar(12)) as 'Дата с', @@day as 'Срок',

// CAST - функция преобразования типа данных в nvarchar(12) значений колонки «Дата_с»

@@id 'Личный номер сотрудника', @@dept 'Отдел'

END

SET @@Counter =@@Counter +1 // Установить значение переменной @@Counter большим на единицу

END

CLOSE curs4 //закрытие курсора

DEALLOCATE curs4 //освобождение курсора

5. Курсор для просмотра сотрудников, у кого в текущем месяце День рождения

CREATE PROCEDURE curs5

as

DECLARE curs5 CURSOR

GLOBAL SCROLL KEYSET

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

TYPE_WARNING

// Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT

FOR

SELECT * FROM День_рождения

FOR UPDATE // Курсор на обновление в таблице

open global curs5 // открытие глобального курсора

DECLARE

@@age int,

@@date datetime,

@@id int,

@@otdel varchar(100),

@@var int,

@@Counter int

SET @@Counter = 1

SET @@var = 0

WHILE @@Counter<= @@CURSOR_ROWS

// Выполняется до тех пор, пока число строк в таблице меньше или равно числу рядов курсора

BEGIN

FETCH curs5 INTO @@age, @@date, @@id,@@otdel

// FETCH - получает определенную строку из курсора и помещает данные из столбцов выборки в переменные @@age, @@date, @@id и @@otdel

IF (MONTH(@@date) = MONTH(DATEADD(MONTH, 0, GETDATE())))

// Проверка, чтобы значение MONTH введенной переменной (@@date совпадало с текущим месяцем, в соответствии с текущей датой, установленной на данном сервере; GETDATE - функция, возвращающая текущую системную отметку времени базы данных

BEGIN

SET @@var=@@var+1

// Установить значение переменной @@var большим на единицу

PRINT @@date // Вывод на экран значения переменной @@date

Select @@age as 'Полных лет', CAST(@@date AS nvarchar(12)) as 'Дата рождения',

@@id 'Личный номер сотрудника', @@otdel 'Отдел'

END

SET @@Counter =@@Counter +1

// Установить значение переменной @@Counter большим на единицу

END

Select @@var as 'В этом месяце день рождение у:'

CLOSE curs5 //закрытие курсора

DEALLOCATE //освобождение курсора

3.5 Распределение прав доступа

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

? ограничение прав на чтение, изменение или уничтожение;

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

? исключение утечки информации при обработке и передаче между объектами вычислительной техники.

Созданы группы пользователей, исполняющие различные роли:

1) Админ (login ? admin/password ? 0). Имеет доступ ко всей информации, может добавлять/изменять данные в таблицах, добавлять новых пользователей и удалять существующих.

CREATE LOGIN admin // Создание логина admin

WITH PASSWORD = '0' // Присвоение пароля

CREATE USER admin // Создание пользователя admin

FOR LOGIN admin

GRANT ALL PRIVILEGES // Назначение пользователю всех прав

TO admin

WITH GRANT OPTION // C возможностью назначения прав другим пользователям

GO

2) Сотрудник отдела кадров (kadr/1). Так же, как и админ, имеет доступ ко всем данным, есть возможность изменять их, но не может производить операции с пользователями.

CREATE LOGIN kadr // Создание логина kadr

WITH PASSWORD = '1' // Присвоение пароля

CREATE USER kadr // Создание пользователя kadr

FOR LOGIN kadr

DENY ALL PRIVILEGES ON OBJECT::Пользователи TO kadr // Запрет всех действий с таблицей

GRANT SELECT, UPDATE, DELETE, INSERT ON День_рождения TO kadr // Назначение всех прав доступа к таблице

GRANT SELECT, UPDATE, DELETE, INSERT ON Командировки TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Нормативные_доументы TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Нормативные_документы_отделов TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Отделы TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Приказы TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Приказы_сотрудников TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Сотрудники TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Спецзвания TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Табель_отпусков TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Трудовой_стаж TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Увольнения TO kadr

3) Сотрудник милиции (sort/2). Имеет доступ к информации, содержащейся в данной информационной системе, только в режиме чтения (отсутствуют кнопки удаления, добавления, сохранения).

CREATE LOGIN sort // Создание логина sort

WITH PASSWORD = '2' // Присвоение пароля

CREATE USER sort // Создание пользователя sort

FOR LOGIN sotr

DENY ALL PRIVILEGES ON OBJECT::DeletedItem TO sort // Запрет всех действий с таблицей

DENY ALL PRIVILEGES ON OBJECT::DeletedWorker TO sort

DENY ALL PRIVILEGES ON OBJECT::Пользователи TO sort // Запрет всех действий с таблицей

GRANT SELECT ON День_рождения TO sort // Назначение прав доступа только на выборку данных из таблицы в режиме чтения

GRANT SELECT ON Сотрудники TO sotr

GRANT SELECT ON Отделы TO sotr

GRANT SELECT ON Трудовой_стаж TO sort

GRANT SELECT ON Командировки TO sotr

GRANT SELECT ON Нормативные_документы TO sotr

GRANT SELECT ON Нормативные_документы_отделов TO sotr

GRANT SELECT ON Приказы TO sotr

GRANT SELECT ON Приказы_сотрудников TO sotr

GRANT SELECT ON Спецзвания TO sotr

GRANT SELECT ON Табель_отпусков TO sotr

GRANT SELECT ON Увольнения TO sotr

4 Результат тестирования информационной системы

Для нормальной работы системы необходимо 256 Мбайт оперативной памяти, Windows NT/2000/XP и выше, Access2000 и выше.

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

После тестирования получили результаты:

? Информационная система поддерживает многопользовательский режим.

? Тестирование запросов прошло успешно.

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

Отчеты составлены на основе запросов и используются для получения информации.

Заключение

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

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

Во время выполнения курсовой работы было разработано многопользовательская информационная система «Отдел кадров РОВД г.Климовичи».

При создании ИС использовались СУБД Microsoft ACCESS 2000, средство моделирования баз данных All Fusion Data Modeler 4.1, средство моделирования бизнес-процессов All Fusion Process Modeler r7, сервер баз данных Microsoft SQL Server Express 2005, язык баз данных T-SQL.

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

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

Список использованных литературных источников

1. Бьюли, А. Изучаем SQL / А. Бьюли, Э. Оппель. - М.: Символ, 2007. - 312 с.

2. Жилинский, А. Самоучитель Microsoft SQL Server 2005 / А. Жилинский. - СПб: BHV, 2004. - 224 с.

3. Бхамидипати, К. SQL. Справочник программиста/ К. Бхамидипати. - М.: Эком, 2003. - 304 с.

4. Литвин, П. Разработка корпоративных приложений в Access 2002. Для профессионалов / П. Литвин, К. Гетц, М. Гунделой. - СПб.: Питер, 2002. - 1008 с.

5. Виейра, Р. Программирование баз данных Microsoft SQL Server 2005. Базовый курс / Р. Вийера. - М.: Вильямс, 2003. - 848 с.

6. Уилтон, П. SQL для начинающих / П. Уилтон, Дж. Колби. - М.: Вильямс, 2006. - 496 с.

7. Фленов, М. Transact SQL в подлиннике / М. Фленов. - СПб: BHV, 2006. - 576 с.

8. Моисеенко, С. SQL. Задачи и решения / С. Моисеенко. СПб.: Питер, 2006. - 256 с.

9. Microsoft SQL Server 7 для профессионалов. - СПб.: Питер, 2000. - 896 с.

10. Базы данных -- революционная эволюция. / [Электронный ресурс] Режим доступа: http://www.advlab.ru/articles/article378.htm. -- Дата доступа: 25.03.2013

11. Маклаков, С. В. Моделирование бизнес-процессов с BPwin 4.0 / С. В. Маклаков // -- М.:«Диалог-МИФИ», 2002. -- 209 с.

Размещено на Allbest.ru