Mavzu: sql da funksiyalar va triggerlar yaratish

Before delete
mysql> create trigger tuzalganlar
-> before delete
-> on bemorlar_2 for each row
-> begin
-> insert into tuzalganlar
-> values(old.familyasi, old.ismi, old.manzili);
-> end//

Query OK, 0 rows affected (0.13 sec)

Before update
mysql> create trigger check_yoshi before update on bemorlar_2
-> for each row
-> begin

-> if new.yoshi < 0 then

-> set new.yoshi = 0;
-> elseif new.yoshi > 100 then
-> set new.yoshi = 100;
-> end if;
-> end //
Query OK, 0 rows affected (0.16 sec)


After update
mysql> create table update_bemorlar like bemorlar_2;
Query OK, 0 rows affected (0.13 sec)
mysql> select * from update_bemorlar;
Empty set (0.00 sec)

mysql> create trigger after_update

-> after update
-> on bemorlar_2 for each row
-> begin

-> insert into update_bemorlar values

-> (old.bemor_id, old.familyasi, old.ismi, old.otasining_ismi, old.manzili, old.yoshi, old.jinsi);
-> end//
Query OK, 0 rows affected (0.10 sec)


Before Insert
mysql> create trigger before_insert before insert
-> on shifokorlar_2 for each row
-> begin

-> if new.lavozimi = 'Shifokor' then

-> set new.lavozimi = 'Yordamchi';
-> end if;
-> end//

Query OK, 0 rows affected (0.09 sec)

mysql> insert into shifokorlar_2 values
-> (13, 'Abrorov', 'Ali', 'Asadovich', 'Shifokor', 9, 'Magistr', 'Namangan'),
-> (14, 'Asrorov', 'Vali', 'Ahadovich', 'Shifokor', 6, 'Bakalavr', 'Andijon')//
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from shifokorlar_2//

After Insert
mysql> create table ogohlantirish
-> (id int auto_increment,
-> bemor_id int(4),
-> message varchar(255) not null,
-> primary key(id, bemor_id))//
Query OK, 0 rows affected (0.11 sec)
mysql> create trigger after_insert after insert
-> on bemorlar_2 for each row
-> begin
-> if new.ismi is null then
-> insert into ogohlantirish (bemor_id, message)
-> values(new.bemor_id, concat(new.familyasi, ' ismingizni yangilang.'));
-> end if;
-> end//

Query OK, 0 rows affected (0.13 sec)

mysql> insert into bemorlar_2(bemor_id, familyasi) values
-> (9, 'Ahmadjonov')//

Query OK, 1 row affected (0.11 sec)

mysql> select * from ogohlantirish//

Xulosa:

Men ushbu laboratoriya ishida MySQL da triggerlarr va funksiyalar yaratish bo`yicha ko`nikmaga ega bo`ldim.

http://hozir.org