Лабораторна робота №4, Внесення змін до таблиць бази даних та оброблення цих змін тригерами

ЗМІСТ

ЛАБОРАТОРНА РОБОТА №4 «ВНЕСЕННЯ ЗМІН ДО ТАБЛИЦЬ БАЗИ ДАНИХ ТА ОБРОБЛЕННЯ ЦИХ ЗМІН ТРИГЕРАМИ ». 3

1. Внесення змін до таблиці БД за допомогою INSERT. 3

1.1. Доповнення таблиці одним рядком. 3

1.2. Доповнення таблиці декількома рядками. 4

1.3. Доповнення таблиці рядком, первинний ключ якої має властивості IDENTITY   4

2. Внесення змін до таблиці БД за допомогою DELETE. 6

2.1. Вилучення записів з таблиці 6

2.2. Вилучення записів таблиці з використанням підзапитів. 7

2.3. Вилучення заданої кількості записів. 7

3. Внесення змін до таблиці БД за допомогою UPDATE. 8

3.1. Заміна значенння поля одного рядка таблиці 8

3.2. Заміна значень поля всіх рядків таблиці 8

3.3. Заміна значень поля таблиці за умовою.. 9

3.4. Заміна значень поля таблиці з використанням From.. 9

3.5. Заміна значень поля таблиці з використанням підзапиту. 10

4. Оброблення змін до бд за допомогою тригерів. 11

5.1.Резервне копіювання баз даних. 19

5.2. Відновлення бази даних. 21

ЛАБОРАТОРНА РОБОТА №4 «ВНЕСЕННЯ ЗМІН ДО ТАБЛИЦЬ БАЗИ ДАНИХ ТА ОБРОБЛЕННЯ ЦИХ ЗМІН ТРИГЕРАМИ»

Мета лабораторної роботи: Опрацювати та набути практичні знання і навички з внесення змін до таблиць бази даних з використанням операторів TRANSACT-SQL: INSERT, DELETE і UPDATE та відпрацювання операції з резервного копіювання і відновлення бази даних.

1. Внесення змін до таблиці БД за допомогою INSERT

1.1. Доповнення таблиці одним рядком

На рис. 1 представлено доповнення нового запису до таблиці Sklad бази даних AWTO.

Рис. 1 - Доповнення таблиці Sklad одним новим записом

Цей дозапис до таблиці (представлення) БД виконується командою INSERT, базовий синтаксис якої має такий вид:

INSERT [INTO] ім’я_таблиці [(список_полів/column_list)]

VALUES (список_значень/data_values)

Параметр column_list – це список атрибутів таблиці, до яких вносятся зміни.

Так як в наведеному на рис.1 прикладі значення всіх стовпчиків надані в тому ж порядку, що і в таблиці Sklad, то імена полів в параметрі column_list можна не вказувати. В ті атрибути, які не вказані в списку column_list вставляються значення за змовчанням (якщо вони задані для стовпчика) або значення NULL, але це не стосується ключових полів, значення яких повинно бути вказане.

1.2. Доповнення таблиці декількома рядками

За допомогою команди INSERT таблицю можна доповнювати декількома записами (див.рис.2). В наведеному на рис.2 прикладі в параметрі (column_list) перераховані всі атрибути таблиці dbo.Sklad, яка доповнюється двома новими записами.

Рис. 2 - Доповнення таблиці Sklad двома новими записами

1.3. Доповнення таблиці рядком, первинний ключ якої має властивості IDENTITY

При доповненні такої таблиці новими рядками значення первинного ключа формується компонентою SQL Server Database Engine автоматично, тому в команді INSERT це поле не вказується. Якщо потрібно явно вказувати значення для таких полів, необхідно попередньо виконати команду:

SET IDENTITY_INSERT ON

Нижче наведено приклад доповнення новим записом таблиці avto, структура якої наведена на рис. 3. Первинний ключ Kod_avto визначений як ідентифікатор з початковим значенням 1 і кроком нарощування 1, тобто має властивість IDENTITY.

Рис. 3 - Структура таблиці avto

Рис. 4 - Фрагмент даних таблиці avto до модифікації

Рис. 5 - Доповнення таблиці avto новим записом

Рис. 6 - Фрагмент даних таблиці avto після модифікації

Якщо потрібно вставляти значення з одної таблиці в іншу, то потрібно використовувати наступний формат команди INSERT:

INSERT [INTO] им’я_таблиці [(список_полів)]

(SELECT параметри)

Завдання для опрацювання в лабораторній роботі

1. Доповнити таблицю БД одним записом.

2. Доповнити таблицю БД декількома записами.

3. Доповнити таблицю БД новим записом, первинний ключ якої має властивості IDENTITY.

Завдання для самостійного опрацювання

1. Доповнити представлення БД одним (декількома) записами.

2. Доповнити таблицю БД новим записом з іншої таблиці (представлення) з використанням SELECT.

2. Внесення змін до таблиці БД за допомогою DELETE

2.1. Вилучення записів з таблиці

За допомогою DELETE можна вилучати один чи декілька рядків з таблиці чи представлення. Базовий синтаксис DELETE має такий вид:

DELETE [FROM] ім’я_таблиці [WHERE умова]

На рис.7 показано приклад вилучення з таблиці Menedzer інформацію про менеджера код якого дорівнює 12. Проте слід пам’ятати, при вилученні запису з таблиці-довідника, буде помилка, якщо первинний ключ, запису, що вилучається, є вторинним (зовнішнім) ключем в підпорядкованій (дочірній) таблиці.

Рис. 7 - Вилучення одного запису з таблиці

Якщо параметр WHERE відсутній, то вилучаються всі записи з таблиці. Таблиця, з якої вилучені всі записи, залишається пустою в БД, вилучити її можна за допомогою команди DROP TABLE.

2.2. Вилучення записів таблиці з використанням підзапитів

Вилучати записи з базової таблиці в залежності від значень даних в іншій таблиці можна з використанням підзапитів. Наприклад, необхідно вилучити записи з представлення SHOW , якщо його поле Дата_продажі співпадає з полем Data_P таблиці Prodaz і дорівнює 2015-12-0.

Рис. 8 - Вилучення записів таблиці з використанням підзапитів

2.3. Вилучення заданої кількості записів

Для вилучення заданої кількості чи відсотка запитів виконується параметр TOP

Завдання для опрацювання в лабораторній роботі

1. Вилучити записи з таблиці чи представлення БД за певною умовою.

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

Завдання для самостійного опрацювання

1. Вилучити певну кількість записів з таблиці чи представлення з використанням параметра ТОР.

2. Вилучити записи з таблиці з використанням TRANCATE TABLE.

3. Внесення змін до таблиці БД за допомогою UPDATE

3.1. Заміна значенння поля одного рядка таблиці

За допомогою UPDATE можна змінювати значення окремих атрибутів в рядках їх групах чи в усіх рядках в одній базовій таблиці чи представленні.

На рис.9 наведено приклад зміни значень атрибуту Name_men таблиці Menedzer за умови, що Kod_men=2:

Рис. 9 - Зміна значення поля в одному рядку таблиці БД

Базовий синтаксис команди UPDATEмає такий вид:

UPDATE им’я_таблиці

SET поле1=вираз1 [,…, полеN=ВиразN]

[WHERE умова]

Якщо опція WHERE пропущена, то будуть змінені всі рядки таблиці.

3.2. Заміна значень поля всіх рядків таблиці

На рис.10 наведено приклад зміни значення поля Cina=Cina*2 в усіх рядках таблиці Sklad:

Рис. 10 - Зміна значення поля в усіх рядках таблиці БД

3.3. Заміна значень поля таблиці за умовою

На рис.11 проілюстровано приклад зміни ціни в таблиці Sklad бази даних AWTO з використанням оператора CASE, який визначає умови зміни поля Cina при переоцінці автомобілів.

Рис. 11 - Зміна значення поля таблиці БД за умовою

3.4. Заміна значень поля таблиці з використанням From

На рис.12 проілюстровано приклад зміни назви моделі атомобіля (Name_mod) в таблиці Model бази даних AWTO для автомобіля Шевроле при співпаданні коду автомобіля з таблиці Model з кодом автомобіля з таблиці avto.

Рис. 12 - Зміна значення поля таблиці з використанням FROM

3.5. Заміна значень поля таблиці з використанням підзапиту

На рис.13 проілюстровано приклад зміни назви моделі атомобіля (Name_mod) в таблиці Model бази даних AWTO для автомобіля Reno з використанням підзапиту.

Рис. 13 - Зміна значення поля таблиці з використанням підзапиту

Завдання для опрацювання в лабораторній роботі

1. Замінити значення поля в одному рядку таблиці чи представлення.

2. Замінити значення поля в усіх рядках таблиці чи представлення.

3. Замінити значення поля в рядках таблиці чи представлення за певною умовою.

4. Змінити значення поля таблиці з використанням FROM.

Завдання для самостійного опрацювання

1. Змінити значення поля таблиці з використанням підзапитів.

4. Оброблення змін до бд за допомогою тригерів

Тригер – це особливий вид процедури, яка виконується Microsoft SQL Server автоматично, при виконанні певної інструкції T- SQL.

Існує два види тригерів:

- тригери DML, які запускаються при виконанні команд по внесенню змін до таблиці БД.

- тригери DDL, які запускаються при виконанні команд: CREATE, ALTER, DROP.

Тригери DML представлені такими видами:

INSERT TRIGGER – тригери цього виду запускаються при спробі вставки даних за допомогою команди INSERT.

UPDATE TRIGGER - тригери цього виду запускаються при спробі вставки даних за допомогою команди UPDATE.

DELETE TRIGGER - тригери цього виду запускаються при спробі вставки даних за допомогою команди DELETE.

Також тригери можна класифікувати за їх поведінкою.

AFTER – тригер виконується після успішного виконання команди, що змінює дані в таблиці. Якщо команда з якихось причин не може бути успішно завершена, то тригер также не виконується. Зміна даних в результаті виконання запиту користувача і виконання тригера виконується в тілі однієї трансакції. Тобто, якщо відбудеться відкіт тригера (в ньому буде виконана команда ROLLBACK TRAN), то також будуть відкочені і користувацькі зміни. Тригери типу AFTER неможна виконати для представлень. Для кожної таблиці можно створити більше одного тригера AFTER для кожної операції (INSERT, UPDATE, DELETE).

INSTEAD OF тригер викликається замість команд, назначених для запуска триггера. Тригери INSTEAD OF можуть працювати як з таблицями так і представленнями. За умолчуванням в SQL Server 2008 всі тригери є тригерами типу AFTER.

Кожен тригер пов’язаний з певною таблицею БД і розглядається як одна трансакція, яка починається командою BEGIN і завершується командою END.

Якщо не потрібно тригеру передавати записи потрібно добавити команду ROLLBACK, яка дає наказ серверу призупинити оброблення модифікації і заборонити трансакцію.

Команда RAISEERROR  - відправляє повідомлення користувачу про помилку.

Для отримання інформації про кількість рядків, що будуть змінені при успішному завершенні тригера, можна використовувати функцію @@ROWCOUN.

Розглянемо роботу тригерів, що обробляють операції внесення змін до таблиці за допомогою команд INSERT, UPDATE чи DELETE.

Вибираємо таблицю БД, в яку будемо вносити зміни і активізуємо опцію тригери.

Відкриється вікно (рис.14), в якому вибираємо опцію створення тригера.

Після чого відкриється наступне вікно (рис. 15), що містить сценарій створення тригера на мові Transact SQL.

Рис. 14 - Вікно активізації створення тригера

Рис. 15 - Сценарій створення тригера на мові Transact SQL

Внесення змін до БД виконується операторами мови маніпулювання даними DML (Data Manipulation Language), яка є складовою мови SQL.

Основними командами DML єкоманди – дозапису, модифікації і вилучення даних INSERT, UPDATE и DELETE.

Тригер INSERT

Тригери INSERT запускаються при кожній спробі створити новий запис в таблиці командо INSERT. При спробі вставити новий запис в таблицу SQL Server копіює цей запис в таблицю тригерів БД і спеціальну таблицю inserted.

На рис. 16 надано приклад тригера, що обробляє операцію зі створення нового запису.

Рис. 16 - Приклад тригера, що обробляє операцю з дозапису (INSERT) в таблицю dbo.avto

Після створення тригера натиснути кнопку виконання. В результаті на екран вдається повідомлення про успішне його виконання, а до складу тригерів таблиці буде включено даний тригер. Даний тригер спрацьовує за умови, коли дозапис в таблицю виконується не через конструктор, а через відповідний запит. На рис.17 подано приклад створення нового запису в таблиці бази даних.

Рис. 17 - Створення нового запису в таблиці через запит з використання оператора INSERT

Розглянемо приклад тригера, який забороняє вставку нових рядків до таблиці SHOW БД AWTO.

Рис. 18 - Приклад тригера заборони вставки нових рядків в таблицю dbo.SHOW

При спробі дозапису на екран буде видано повідомлення про те, що вставка рядків заборонена (рис.19).

Рис. 19 - Повідомлення про заборону дозапису нових рядків до таблиці БД

За допомогою тригера можливий контроль для реалізації обмежень на значення окремих полів. Розглянемо приклад тригера для реалізаціїї обмеження на значення поля. Тригер буде реагувати наступним чином – відмінить команду, якщо в таблиці Sklad величина залишку в полі Kol_S виявилась менше кількості, що підлягає продажі в полі Kol_Р в таблиці Prodaz (в прикладі Kod_dwg=13 , Kod_avto=1 , Kod_mod=11). Наведений тригер налагоджений на оброблення лише одного запису.

Рис. 20 - Приклад тригера контролю обмеження на значення поля

Спроба дозапису в таблицю Prodaz нового запису, в якому кількість продаж в полі Kol_Р=4 перевищує кількість на складі даної моделі авто з заданими параметрами двигуна (в прикладі Kod_dwg=13 , Kod_avto=1 , Kod_mod=11), завершується в тригері, який припиняє внесення змін і видає повідомлення, що потрібної кількості на складі не має.

Рис. 21 - Приклад повідомлення на спробу дозапису в таблицю Prodaz запису, в якому кількість продаж авто перевищує їх наявність на складі

Тригер DELETE

Тригери DELETE запускаються при кожній спробі вилучити запис з таблиці командою DELETE. Вилучений запис з таблиці SQL Server копіює в таблицю тригерів БД і спеціальну таблицю deleted. Розглянемо приклад тригера, який забороняє вилучати записи з таблиці Menedzer даних про менеджерів оклад яких дорівнює або більше 5000 грн.

Рис. 22 - Приклад тригера, який забороняє вилучення

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

USE AWTO

DELETE Menedzer

WHERE OKLAD=’5000’

При спробі вилучити запис, який відповідає заданій умові видається діагностичне повідомлення (дивю рис. 22)

Рис. 23 - Повідомлення про заборону вилучення рядків до таблиці БД

Завдання для опрацювання в лабораторній роботі

1. Реалізувати тригери, що обробляють внесення змін до таблиць БД з використанням оператора INSERT.

2. Реалізувати тригери, що забороняють внесення змін до таблиць БД з використанням оператора INSERT.

3. Реалізувати тригери, що обробляють внесення змін до таблиць БД з використанням оператора UPDATE.

4. Реалізувати тригери, що забороняють внесення змін до таблиць БД з використанням оператора UPDATE.

5. Реалізувати тригери, що обробляють внесення змін до таблиць БД з використанням оператора DELETE.

6. Реалізувати тригери, що забороняють внесення змін до таблиць БД з використанням оператора DELETE.

Завдання для самостійного опрацювання

1. Створити тригер для реалізаціїї обмеження на значення поля.

5.1. Резервне копіювання баз даних

Встановивши мишку на БД, для якої створюємо резервну копію, активізуємо «Задачі (Tasks) / Створення резервної копії (Back Up…» (рис.1).

Рис. 24 - Вікно активізації параметрів для створення резервної копії

З’явиться вікно резервного копіювання БД (рис.25). В цьому вікні можна задавати тип резервного копіювання, а також директорію, де необхідно зберегти копію. За замовченням система додасть власний шлях для зберігання резервної копії.

Рис. 25 - Вікно для створення резервної копії

При натисненні ОК виконається резервне копіювання в директорію, яка виділяється СКБД за змовчанням, і у окремому вікні буде видано повідомлення про його успішне завершення (Рис.26).

Рис. 26 - Повідомлення про успішне завершення копіювання

Якщо натиснути ДОБАВИТЬ (ADD), то у вікні, що з’явиться можна вказати іншу директорію для зберігання резервної копії.

Рис. 27 - Вікно для вибору місця для зберігання резервної копії

Резервну копію можно копіювати на інші носії чи на інший сервер.

5.2. Відновлення бази даних

Встановивши мишку на БД активізувати опцію Задачі (Tasks) / Восстановление (Restore), База данных (DataBase) (рис.28).

Рис. 28 - Вікно активізації параметрів для відновлення бази даних

У вікні, що відкриється можна вказати ім’я бази даних, в яку буде перенесена повна резервна копія. Після натиснення ОК буде виконано відновлення бази даних і на екран видано повідомлення про успішне завершення операції (рис.29).

Рис. 29 - Вікно відновлення бази даних з повідомлення про його успішне завершення

Завдання для самостійного опрацювання

1. Відпрацювати операції резервного копіювання та відновлення БД.

З повагою ІЦ "KURSOVIKS"!