Создание триггера на BEFORE UPDATE
mysql> DELIMITER //
mysql> CREATE TRIGGER `test_user_pass2` BEFORE UPDATE ON `test`.`user`
-> FOR EACH ROW
-> BEGIN
-> SET NEW.name = LEFT(NEW.name,1);
-> SET NEW.otch = LEFT(NEW.otch,1);
-> SET NEW.pass = md5(NEW.pass);
-> END//
mysql> DELIMITER ;
Обновление записи
mysql> UPDATE `user` SET `fam`='Иванов', `name`='Иван', `otch` = 'Иванович', `pass` = 'пароль', `login` = 'ivan' WHERE id=1;
mysql> SELECT * FROM `user`;
| id | fam | name | otch | pass | login |
| 1 | Иванов | И | И | e242f36f4f95f12966da8fa2efd59992 | ivan |
NEW — для доступа к новым записям
OLD — для доступа к старым записям
Напимер, если обновить фамилию, то новое значение доступно через NEW.fam, а к старому OLD.fam
Удаление триггера
Для удаления триггера используется, как обычно оператор DROP, пример
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;
где
schema_name — название БД,
trigger_name — название триггера
Список созданных триггеров
Показать триггер можно с помощью команды
SHOW TRIGGERS [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
Задание 1
1. Создать в СУБД MySQL таблицы:
CREATE TABLE `test` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`content` TEXT NOT NULL
) ENGINE = MYISAM
— лог
CREATE TABLE `log` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`msg` VARCHAR( 255 ) NOT NULL,
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`row_id` INT( 11 ) NOT NULL
) ENGINE = MYISAM
— триггер
DELIMITER |
CREATE TRIGGER `update_test` AFTER INSERT ON `test`
FOR EACH ROW BEGIN
INSERT INTO log Set msg = 'insert', row_id = NEW.id;
END;* This source code was highlighted with Source Code Highlighter.
2. Теперь добавьте запись в таблицу test. В таблице log тоже появится запись, обратите внимание на поле row_id, в нем хранится id вставленной вами строки.
3. Удалите триггер
DROP TRIGGER `update_test`;
4. Cоздайте еще одну таблицу, в которой будут храниться резервные копии строк из таблицы test.
CREATE TABLE `testing`.`backup` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`row_id` INT( 11 ) UNSIGNED NOT NULL,
`content` TEXT NOT NULL
) ENGINE = MYISAM
5. Создайте два триггера
DELIMITER |
CREATE TRIGGER `update_test` before update ON `test`
FOR EACH ROW BEGIN
INSERT INTO backup Set row_id = OLD.id, content = OLD.content;
END;
CREATE TRIGGER `delete_test` before delete ON `test`
FOR EACH ROW BEGIN
INSERT INTO backup Set row_id = OLD.id, content = OLD.content;
END* This source code was highlighted with Source Code Highlighter.
Теперь если мы отредактируем или удалим строку из test она скопируется в backup.
Задание 2
Создать таблицы следующей структуры:
1. Квартиры(*НомерКвартиры, Адрес, Телефон, КолвоКомнат, Этаж, Район, Владелец, Стоимость, ПризнакПродажи), если квартира не продана, то ПризнакПродажи=0, если продана, то 1.
2. Сделка(*НомерСделки, Дата, СуммаСделки, ФИО_Реелтера). Написать триггер, который каждый раз при добавлении записи в таблицу «Сделка», помечает квартиру в таблице «Квартиры» как проданную, т.е. поле «ПризнакПродажи» устанавливается равным 1.
Задание 3
Отпуск товара со склада выполняется по документу «Накладная», который имеет шапку и строки. В шапке накладной содержится информация о том, кому, кем и когда отпускается товар со склада. В строках накладной содержится информация о тех товарах, которые отпускаются по этой накладной. По одной накладной можно отпустить несколько разных товаров со склада.
Информация о накладных хранится в двух таблицах:
«ШапкаНакладной» и «СтрокиНакладной». Таблицы имеют следующую структуру:
1. ШапкаНакладной(*НомерНакл, Поставщик, Покупатель, ДатаОтпуска, СуммаПоНакл)
2. СтрокиНакладной(*НомерЗаписи, НомерНакл, НазваниеТовара, Цена, Колво, ЕдИзм).
Где
* - отмечены первичные ключи
ЕдИзм – единица измерения товара: штуки, пачки, ящики и т.д.
СуммаПоНакл – сумма товара по всей накладной, считается как сумма произведения Цена* Колво по всем строкам данной накладной.
Поле «НомерНакл» в таблице «СтрокиНакладной» является внешним ключом и ссылается на первичный ключ таблицы «ШапкаНакладной».
Создать таблицы. Написать триггеры, которые бы при изменении, удалении, добавлении строк в таблице «СтрокиНакладной» автоматически обновляли поле СуммаПоНакл в таблице «ШапкаНакладной».
Задание 4
Создайте триггеры для своего варианта базы данных.
Контрольные вопросы
1. Что такое триггер?
2. Для чего используются контекстные переменные в теле триггера?
3. Можно ли в теле триггера использовать локальные переменные?
4. Можно ли в теле триггера использовать операторы ветвлений и циклические операторы?
5. С помощью каких команд можно создать триггер? Как удалить триггер?
6. Каким образом посмотреть какие триггеры уже созданы в базе данных?
7. Приведите примеры триггеров.
Лабораторная работа № 19
Тема: «Создание транзакции с откатом и фиксацией»
Цель работы: Научиться создавать запросы в клиентском приложении.
Время выполнения: подготовка: 5 мин; выполнение: 120 мин; проверка: 10 мин; всего: 135 мин.
Указания к выполнению лабораторной работы
Транзакция - это группа операций обработки данных, выполняемых как некоторое неделимое действие над базой данных, осмысленное с точки зрения пользователя.
По умолчанию СУБД MySQL работает в режиме автоматического завершения транзакций, т.е. как только выполняется оператор обновления данных, который модифицирует таблицу, MySQL тут же сохраняет изменения на диске. Для объединения в транзакцию нескольких операторов необходимо отключить этот режим. Это можно осуществить при помощи оператора SET AUTOCOMMIT
mysql> SET AUTOCOMMIT=0;
После отключения режима автоматического завершения транзакций следует использовать оператор COMMIT, чтобы сохранить изменения на диске, либо ROLLBACK, чтобы отменять изменения, выполненные с момента начала транзакции. Для того, чтобы включить режим автоматического завершения транзакций необходимо выполнить оператор SET AUTOCOMMIT=1.
mysql> SET AUTOCOMMIT=0;
mysql> INSERT INTO catalogs VALUES(NULL,'Переферия');
mysql> INSERT INTO catalogs VALUES(NULL,'Разное');
mysql> SELECT * FROM catalogs;
| id_catalog | name |
| Процессоры | |
| Материнские платы | |
| Видеоадаптары | |
| Жёсткие диски | |
| Оперативная память | |
| Переферия | |
| Разное |
mysql> ROLLBACK;
mysql> SELECT * FROM catalogs;
| id_catalog | name |
| Процессоры | |
| Материнские платы | |
| Видеоадаптары | |
| Жёсткие диски | |
| Оперативная память |
mysql> INSERT INTO catalogs VALUES(NULL,'Переферия');
mysql> INSERT INTO catalogs VALUES(NULL,'Разное');
mysql> COMMIT;
mysql> SELECT * FROM catalogs;
| id_catalog | name |
| Процессоры | |
| Материнские платы | |
| Видеоадаптары | |
| Жёсткие диски | |
| Оперативная память | |
| Переферия | |
| Разное |
mysql> SET AUTOCOMMIT=1;
В таблицу catalogs добавляются новые записи, при этом выполнения оператора ROLLBACK позволяет отменить все изменения, которые были произведены над таблицей catalogs. Напротив, оператор COMMIT сохраняет изменения на диске и уже следующий оператор ROLLBACK вернёт последнее сохранённое состояние.
Для того чтобы включить режим автоматического завершения транзакций только для отдельной последовательности операторов, можно воспользоваться оператором START TRANSACTION.
mysql> START TRANSACTION;
mysql> SELECT @total := count FROM products
-> WHERE name = 'Celeron D 320 2.4GHz';
| @total := count |
mysql> UPDATE products SET count = @total - 1
-> WHERE name = 'Celeron D 320 2.4GHz';
mysql> COMMIT;
После выполнения оператора START TRANSACTION режим автоматического завершения транзакций остаётся включённым до явного завершения транзакции с помощью оператора COMMIT или отката транзакции при помощи ROLLBACK.
Общий вид работы транзакции:
1. SET AUTOCOMMIT=0;2. START TRANSACTION;3. UPDATE ...; UPDATE ...; UPDATE ...;4. если на каком либо из UPDATE произошел сбой, то - ROLLBACK , иначе - COMMIT;5. SET AUTOCOMMIT=1;Задание 11. Написать транзакцию: в таблице PaySumma изменить оплату (поле PaySum) с кодом 6 с 20 на 100, а затем изменить дату оплаты (PayDate ) с '06/13/2001' на '06/11/2001'". Транзакция должна пройти успешно.
2. Написать транзакцию: в таблицу Sotr(id, FIO, zarpl) добавить три новые записи (1, Иванов, 20000); (2, Петров, 23000); (3, Сидоров, 2500). Затем удалить запись с фамилией Егоров. Поскольку Егорова нет, то транзакция не должна пройти.
Задание 2
Написать транзакции к своему варианту базы данных
Контрольные вопросы
1. Что такое транзакции?
2. Какой оператор используется для того, чтобы транзакция прошла?
3. Какой оператор используется для того, чтобы транзакция не выполнялась?
4. Что определяется командой SET AUTOCOMMIT?
5. Каким оператором можно воспользоваться, чтобы включить режим автоматического завершения транзакций только для отдельной последовательности операторов?
Практическая работа № 20
Тема: «Блокировка таблиц».
Цель работы: Научиться создавать запросы в клиентском приложении.
Время выполнения: подготовка: 5 мин; выполнение: 120 мин; проверка: 10 мин; всего: 135 мин.
Указания к выполнению лабораторной работы
Для таблиц типа MyISAM использование транзакций недопустимо. Однако их можно эмулировать при помощи операторов LOCK TABLES и UNLOCK TABLES. Данные операторы блокируют всю таблицу, в результате чего никто не может работать с таблицами до тех пор, пока они остаются заблокированными.Пример:LOCK TABLES catalogs WRITE;INSERT INTO catalogs VALUES(NULL, ’ПЕрифери’);INSERT INTO catalogs VALUES(NULL,’Разное’);UNLOCK TABLES; Если в потоке возникает блокировка операции READ для некоторой таблицы, то только этот поток (и все другие потоки) могут читать из данной таблицы. Если для некоторой таблицы в потоке существует блокировка WRITE, тогда только поток, содержащий блокировку, может осуществлять операции чтения (READ) и записи (WRITE) на данной таблице. Остальные потоки блокируются.При использовании команды LOCK TABLES необходимо блокировать все таблицы, которые предполагается использовать в последующих запросах, употребляя при этом те же самые псевдонимы, которые будут в запросах! Если таблица упоминается в запросе несколько раз (с псевдонимами), необходимо заблокировать каждый псевдоним!
Блокировка WRITE обычно имеет более высокий приоритет, чем блокировка READ, чтобы гарантировать, что изменения обрабатываются так быстро, как возможно. Это означает, что если один поток получает блокировку READ и затем иной поток запрашивает блокировку WRITE, последующие запросы на блокировку READ будут ожидать, пока поток WRITE не получит блокировку и не снимет ее
Задание 11. Выполните операторы создания и блокировки таблиц, представленные ниже.DROP TABLE IF EXISTS `access`;CREATE TABLE `access` ( `uid` int(10) unsigned default NULL, `resource` varchar(255) default NULL, UNIQUE KEY `row` (`uid`,`resource`)) ENGINE=MyISAM DEFAULT CHARSET=latin1; LOCK TABLES `access` WRITE;INSERT INTO `access` VALUES (26,'MOD_pages'),(31,'MOD_pages'),(32,'MOD_pages'),(33,'MOD_pages'),(34,'MOD_pages'),(38,'MOD_pages');UNLOCK TABLES; DROP TABLE IF EXISTS `orders`;CREATE TABLE `orders` ( `id` int(10) unsigned NOT NULL auto_increment, `serializedItems` text, `status` tinyint(3) unsigned NOT NULL default '0', `orderTime` int(10) unsigned default NULL, `completeTime` int(10) unsigned default NULL, `managerId` int(10) unsigned default NULL, `itemsCount` int(10) unsigned default NULL, `totalPrice` float(7,2) default NULL, `orderComment` text, `userName` varchar(255) default NULL, `userEmail` varchar(255) default NULL, `userPhone` varchar(255) default NULL, `prices` enum('roz','opt') default NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;LOCK TABLES `orders` WRITE;INSERT INTO `orders` VALUES (18,'a:1:{i:273;s:2:\"22\";}',0,1144262776,NULL,NULL,22,3775.20,'asdf\r\nasdf\r\nasdf\r\nasdf\r\nasdf\r\nasdf','','','','opt'),(19,'a:1:{i:351;s:1:\"2\";}',0,1145315161,NULL,NULL,2,674.00,'gf','g','s','gfd','roz'),(17,'a:6:{i:256;s:3:\"234\";i:273;s:1:\"4\";i:257;s:3:\"234\";i:133;s:2:\"43\";i:264;s:4:\"1190\";i:241;s:1:\"4\";}',3,1144262515,1144262671,1,1709,14909.49,'','egf','','','opt');UNLOCK TABLES;2. Оператор блокировки таблицы, представленный ниже записан неверно. Напишите правильный оператор блокировки таблиц при использовании псевдонимов.LOCK TABLES products READ;
SELECT * FROM products AS P:
Контрольные вопросы
1. Для чего нужны блокировки таблиц?
2. Какие операторы блокируют всю таблицу?
3. Какие виды блокировок вы знаете?
4. Какой вид блокировки имеет более высокий приоритет?
Практическая работа № 21
Тема: «Создание отчетов»
Цель работы: Научиться создавать отчеты при помощи Fast Report.
Время выполнения: подготовка: 3 мин; выполнение: 77 мин; оформление и сдача: 10 мин; всего: 90 мин.
Указания к выполнению лабораторной работы
Компонент снабжен встроенным дизайнером, который можно вызвать двойным щелчком мыши на компоненте TfrxReport.
Для использования дизайнера в скомпилированном проекте небходимо использовать компонент TfrxDisigner из палитры FastReport.
Цифрами на рисунке обозначены:
1 – рабочее поле дизайнера;
2 – строка меню;
3 – панели инструментов;
4 – панель объектов;
5 – закладки страниц отчета и редактора кода;
6 – окно «Дерево отчета»;
7 – окно «Инспектор объектов»;
8 – окно «Дерево данных». Из этого окна можно перетаскивать элементы на лист отчета;
9 – линейки. При перетаскивании линейки на лист отчета образуется выносимая линия, к которой могут прилипать объекты;
10 – строка состояния.
Задание 1
1. Разработать простой отчет, показывающий содержание полей одной таблицы из базы данных вашего варианта.
2. Разработать отчет, показывающий данные из нескольких таблиц с итогами из базы данных вашего варианта.
Контрольные вопросы
1. С помощью какого компонента Emracadero Pad Studio C++ Builder можно создать отчет?
2. Расскажите алгоритм создания простого отчета.
3. Каким образом в отчет можно поставить подсчет итогов?
Практическая работа №22
Тема: «Распределение привилегий пользователям. Управление привилегиями пользователей»
Цель работы: Научиться создавать учетные записи пользователей и назначать им привилегии.
Время выполнения: подготовка: 3 мин; выполнение: 167 мин; оформление и сдача: 10 мин; всего: 180 мин.