Mavzu: sql da index yaratish. Sqlda triggerlar yaratish. Ishdan maqsad

mysql> SELECT @sum AS 'Umumiy summa';

+-----------------------+

| Umumiy summa |

+-----------------------+

| 1852.48 |

+-----------------------+

1 row in set (0.00 sec)

Quyidagi hisoblandi: 14.98 + 1937.50 - 100, yoki 1852


Triggerni quyidagicha o’chirish mumkin:
DROP TRIGGER test.ins_sum;

OLD va NEW

Keling, OLD va NEW nima ekanligini batafsil ko'rib chiqaylik. Ushbu ko'rsatmalar trigger bilan ishlaydigan ustunlarga kirishga imkon beradi. Ushbu ko'rsatmalar faqat MySQL-ga tegishli. Ular registrga sezgir emas.

INSERT triggerida faqat NEW.col_name ishlatilishi mumkin. Bu tushunarli, OLD.col_name hali mavjud emas.

DELETE triggerida faqat OLD.col_name ishlatilishi mumkin.

UPDATE triggerida ikkalasini ham ishlatishingiz mumkin.

Keyingi misol
Quyidagi misol bir nechta ko'rsatmalardan trigger yaratishga imkon beradi. Faqatgina ularni BEGIN ... ... END ichiga qo'shish va triggerning davomiyligini belgilovchi delimiter e'lon qilish kerak.
\d //
CREATE TRIGGER upd_check BEFORE UPDATE ON hisob
FOR EACH ROW
BEGIN
IF NEW.middori < 0 THEN
SET NEW.middori = 0;
ELSEIF NEW.middori > 100 THEN
SET NEW.middori = 100;
END IF;
END;//

mysql> \d ;

Trigger ichida siz CALL usulidan foydalangan holda saqlangan protsedurani chaqirishingiz mumkin, ammo ko’plikda emas.

Shuningdek, triggerlardan tranzaktsiyalarda foydalanib bo'lmaydi.

mysql> create table Buyurtma

-> (id int auto_increment primary key,
-> Nomi varchar(30),
-> Ketgan_sana date,
-> Kelgan_sana date)$$
Query OK, 0 rows affected (0.53 sec)

mysql> CREATE trigger buyurtma_vaqti BEFORE INSERT

-> ON buyurtma
-> FOR EACH ROW BEGIN
-> SET NEW.ketgan_sana = NOW();
-> END//
Query OK, 0 rows affected (0.19 sec)

mysql> insert into buyurtma

-> (Nomi)
-> values
-> ('Kitob'),
-> ('O`yinchoq'),
-> ('Portfel')//
Query OK, 3 rows affected (0.15 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from buyurtma//

+----+-----------+-------------+-------------+
| id | Nomi | Ketgan_sana | Kelgan_sana |
+----+-----------+-------------+-------------+
| 1 | Kitob | 2019-11-25 | NULL |
| 2 | O`yinchoq | 2019-11-25 | NULL |
| 3 | Portfel | 2019-11-25 | NULL |
+----+-----------+-------------+-------------+
3 rows in set (0.00 sec)

mysql> DELIMITER //

mysql> CREATE TRIGGER `test_user_pass` BEFORE INSERT 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//
Query OK, 0 rows affected (0.09 sec)
mysql> DELIMITER ;

mysql> INSERT INTO `user` SET `fam`='Нагайченко', `name`='Максим', `otch` = 'Валерьевич', `pass` = 'password', `login` = 'maxnag';

Query OK, 1 row affected (0.00 sec)

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//
Query OK, 0 rows affected (0.09 sec)
mysql> DELIMITER ;

1 2	mysql> UPDATE `user` SET `fam`='Иванов', `name`='Иван', `otch` = 'Иванович', `pass` = 'пароль', `login` = 'ivan' WHERE id=1; Query OK, 1 row affected (0.00 sec)

1 2 3 4 5 6 7 8

mysql> SELECT * FROM `user`; +----+-------------+------+------+----------------------------------+--------+ | id | fam | name | otch | pass | login | +----+-------------+------+------+----------------------------------+--------+ | 1 | Иванов | И | И | e242f36f4f95f12966da8fa2efd59992 | ivan | +----+-------------+------+------+----------------------------------+--------+ 1 row in set (0.00 sec) DELIMITER // CREATE trigger buyurtma_vaqti BEFORE INSERT ON buyurtma FOR EACH ROW BEGIN SET NEW.ketgan_vaqti = NOW(); END// DELIMITER ;

Topshiriq: Har bir talaba jadvallar ustida index va triggerlar yaratishishi hamda natijasi bilan ko’rsatib o’tishi kerak.