Ma’lumotlar bazasida triggerlar bilan ishlash - Информатика и ИТ - Презентации

Информация о документе

Цена	3000UZS
Размер	67.0KB
Покупки	0
Дата загрузки	10 Декабрь 2024
Расширение	pptx
Раздел	Презентации
Предмет	Информатика и ИТ

Ma’lumotlar bazasida triggerlar bilan ishlash

Купить

Ma’lumotlar bazasida triggerlar bilan ishlash REJA: 1. Ma’lumotlar bazasida triggerlar 2. MBda ma’lumotlar bilan ishlashda triggerlarni ishlatish 3. Ma'lumotlar bazasidagi triggerlar uchun cheklovlar

MBda triggerlar orqali ma'lumotlarni qayta ishlash imkoniyatlarini kengaytirish ■ Ma`lumotlar bazadagi jarayonlarni foydalanuvchilar emas, balik MBni o`zi amalga oshirishi – Triggerlar orqali bojarish bajarilishi(Event) ■ Triggerlar turiga qarab aniq belgilangan shartlar asosida boshlanadi – Triggerlrni ishga tushirish( C ondition) – Bitta jarayonni o`zida barcha jarayonlar(tranzaksiya) bajariladi(Action) – Triggerlar qayta ishlanadigan ma'lumotlar uchun zarur bo'lgan turli imtiyozlar uchun ishlatiladi; – Tranzaksiya operatorlaridan content sifatida foydalanish mumkin emas (COMMIT, SAVEPOINT, ROLLBACK)

Trigger toifalari ■ Trigger tanasida hodisa necha marta bajariladi? – Per statement : trigger tanasidagi hodisa bir marta bajariladi; – For each row: trigger tanasidagi har bir satr uchun bir marta bajariladi. – Trigger ishga tushganda ■ DML ifodasini bajarish bilan bog'liq (before yoki after); ■ muayyan tizim resurslaridan kelib chiqqan holda aniq vaziyat (Masalan tizim soatidan signal, taymer tugashi, xotirani bo'shatish).

Triggerning ob`yektga ta`siri 1 misol: SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, 'EDUCATION', 'NEW YORK');SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, 'EDUCATION', 'NEW YORK'); Trigger tanasidagi hodisa bir marta bajariladi. SQL> UPDATE emp 2 SET sal = sal * 1.1 3 WHERE deptno = 30;SQL> UPDATE emp 2 SET sal = sal * 1.1 3 WHERE deptno = 30;2 misol: Trigger tanasidagi har bir satr uchun bir marta bajariladi.

Triggerning o`b`yektga ta`siri 5DEPTNO 10 20 30 40 DNAME ACCOUNTING RESEARCH SALES OPERATIONS LOC NEW YORK DALLAS CHICAGO BOSTONDEPT ob`yekti BEFORE BEFORE AFTER AFTER

SQLda trigger yaratishning sintaksisi ■ Trigger name – Ma`lumotlar bazasiga unikal nom bilan; ■ Timing - Nazorat qilinadigan hodisalar tartibiga bog'liq (before yoki after yoki o`z o`rnida); ■ Triggering event – Trigger ishga tushgan paytdagi hodisa; ■ Filtering condition – Filterlash paytida tekshirish; ■ Target – Trigger bajarilishidan oldin murojat qilinadigan ob`yekt; ■ Trigger Parameters – ob`yekt yoki uning ustunlariga ishlatiladigan parametrlar; – :new, :old qiymatlarning eski va yangi versiyalari uchun; ■ Trigger action – Trigger tanasida yoziladigan SQL so`rov Begin ... End 6

Trigger yaratishning sintaksisi CREATE [OR REPLACE] TRIGGER trigger_nomi hodisa1 OR hodisa2 OR hodisa3 ON ob`yekt_nomi BEGIN SQL so`rov; END;CREATE [OR REPLACE] TRIGGER trigger_nomi hodisa1 OR hodisa2 OR hodisa3 ON ob`yekt_nomi BEGIN SQL so`rov; END; 7 Ushbu triggerda SQL so`rovda oddiy so`rovlarni yozish mumkin. Hodisada so`rov bajarilishidan oldin yoki keyin ro`y beradigan amallar yozilishi mumkin. Ular bitta yoki bir nechta bo`lishi mumkin.

Triggerga misol SQL> CREATE TRIGGER trigger_1 2 BEFORE OR UPDATE OF ism 3 ON talaba_1 4 BEGIN 5 INSERT INTO talaba_2(‘ism’, ‘familiya’) 6 VALUES (‘Baxrom’, ‘Qosimov’); 7 END; 8 /SQL> CREATE TRIGGER trigger_1 2 BEFORE OR UPDATE OF ism 3 ON talaba_1 4 BEGIN 5 INSERT INTO talaba_2(‘ism’, ‘familiya’) 6 VALUES (‘Baxrom’, ‘Qosimov’); 7 END; 8 / 8Trigger name: trigger_1 Timing: BEFORE Triggering event: UPDATE of ism column Target: talaba_1 Trigger action: INSERT values INTO talaba_2 table

Satrni tekshiruvchi trigger yaratish sintaksisi CREATE [OR REPLACE] TRIGGER trigger_name event1 [ OR event2 OR event3] ON table_name [REFERENCING OLD AS old | NEW AS new ] FOR EACH ROW [WHEN condition ] BEGIN SQL statements; ENDCREATE [OR REPLACE] TRIGGER trigger_name event1 [ OR event2 OR event3] ON table_name [REFERENCING OLD AS old | NEW AS new ] FOR EACH ROW [WHEN condition ] BEGIN SQL statements; END 9 Bu yerda FOR EACH ROW har bir satrni tekshirishga imkon beradi. SQL so`rov BEGIN va END o`rtasida yoziladi.

Triggerga misol 10SQL>CREATE OR REPLACE TRIGGER derive_commission_trg 2 BEFORE UPDATE OF sal ON emp 3 FOR EACH ROW 4 WHEN (new.job = 'SALESMAN') 5 BEGIN 6 :new.comm := :old.comm * (:new.sal/:old.sal); 7 END; 8 /SQL>CREATE OR REPLACE TRIGGER derive_commission_trg 2 BEFORE UPDATE OF sal ON emp 3 FOR EACH ROW 4 WHEN (new.job = 'SALESMAN') 5 BEGIN 6 :new.comm := :old.comm * (:new.sal/:old.sal); 7 END; 8 / Trigger name: derive_commission_trg Timing: BEFORE executing the statement Triggering event: UPDATE of sal column Filtering condition: job = ‘SALESMAN’ Target: emp table Trigger parameters: old, new Trigger action: calculate the new commission to be updated

Ma`lumot jarayonlari uchun triggerlardan foydalanish – Ob`yektlarni boshqarish bo`yicha operatsiyalar – Har bir amal uchun oldin yoki keyin tekshiriladi; – Elementlarni yozishni kuzatish – Har doim ma`lumot kiritilayotganda tekshiriladi; – Ma`lumotlar bzasi yaxlitligini tekshiradi – Ma`lumot qo`shilganda yoki o`chirilganda uning ekvalentlari to`g`risida xabar beradi; – Maintenance of Semantic Integrity – e.g. when the factory is closed, all employees should become unemployed – Tarkibiy ma`lumotlarni saqlash – masalan: kiritilayotgan ma`lumotlar ob`yektga mos bo`lishi kerak; – Xavfsizlikni boshqarish – Masalan: Foydalanuvchi imtiyozlarini tekshirish 11

Triggerga misol 12SQL>CREATE OR REPLACE TRIGGER audit_emp AFTER OR DELETE ON emp FOR EACH ROW BEGIN UPDATE audit_table SET del = del + 1 WHERE user_name = ‘USER’ AND table_name = 'EMP’; END; /SQL>CREATE OR REPLACE TRIGGER audit_emp AFTER OR DELETE ON emp FOR EACH ROW BEGIN UPDATE audit_table SET del = del + 1 WHERE user_name = ‘USER’ AND table_name = 'EMP’; END; /

Trigger orqali yozishga misol 13SQL>CREATE OR REPLACE TRIGGER audit_emp_values 2 AFTER DELETE OR UPDATE ON emp 3 FOR EACH ROW 4 BEGIN 5 INSERT INTO audit_emp_values (user_name, 6 timestamp, id, old_last_name, new_last_name, 7 old_title, new_title, old_salary, new_salary) 8 VALUES (USER, SYSDATE, :old.empno, :old.ename, 9 :new.ename, :old.job, :new.job, 10 :old.sal, :new.sal); 11 END; 12 /SQL>CREATE OR REPLACE TRIGGER audit_emp_values 2 AFTER DELETE OR UPDATE ON emp 3 FOR EACH ROW 4 BEGIN 5 INSERT INTO audit_emp_values (user_name, 6 timestamp, id, old_last_name, new_last_name, 7 old_title, new_title, old_salary, new_salary) 8 VALUES (USER, SYSDATE, :old.empno, :old.ename, 9 :new.ename, :old.job, :new.job, 10 :old.sal, :new.sal); 11 END; 12 /

Trigger orqali o`zgartirishga misol SQL>CREATE OR REPLACE TRIGGER cascade_updates 2 AFTER UPDATE OF deptno ON dept 3 FOR EACH ROW 4 BEGIN 5 UPDATE emp 6 SET emp.deptno = :new.deptno 7 WHERE emp.deptno = :old.deptno; 8 END 9 /SQL>CREATE OR REPLACE TRIGGER cascade_updates 2 AFTER UPDATE OF deptno ON dept 3 FOR EACH ROW 4 BEGIN 5 UPDATE emp 6 SET emp.deptno = :new.deptno 7 WHERE emp.deptno = :old.deptno; 8 END 9 / 14

15SQL> CREATE OR REPLACE TRIGGER emp_count 2 AFTER DELETE ON emp 3 FOR EACH ROW 4 DECLARE 5 num INTEGER; 6 BEGIN 7 SELECT COUNT(*) INTO num FROM emp; 8 DBMS_OUTPUT.PUT_LINE(' There are now ' || num || ' employees.'); 9 END; 10 /SQL> CREATE OR REPLACE TRIGGER emp_count 2 AFTER DELETE ON emp 3 FOR EACH ROW 4 DECLARE 5 num INTEGER; 6 BEGIN 7 SELECT COUNT(*) INTO num FROM emp; 8 DBMS_OUTPUT.PUT_LINE(' There are now ' || num || ' employees.'); 9 END; 10 / SQL> DELETE FROM emp 2 WHERE deptno = 30; ERROR at line 1: ORA-04091: table CGMA2.EMP is mutating, trigger/ function may not see itSQL> DELETE FROM emp 2 WHERE deptno = 30; ERROR at line 1: ORA-04091: table CGMA2.EMP is mutating, trigger/ function may not see it Trigger orqali o`zgartirishga misol

Triggerga misol 16SQL> CREATE OR REPLACE TRIGGER emp_count 2 AFTER DELETE ON emp 3 -- FOR EACH ROW 4 DECLARE 5 num INTEGER; 6 BEGIN 7 SELECT COUNT(*) INTO num FROM emp; 8 DBMS_OUTPUT.PUT_LINE(' There are now ' || num || ' employees.'); 9 END; 10 /SQL> CREATE OR REPLACE TRIGGER emp_count 2 AFTER DELETE ON emp 3 -- FOR EACH ROW 4 DECLARE 5 num INTEGER; 6 BEGIN 7 SELECT COUNT(*) INTO num FROM emp; 8 DBMS_OUTPUT.PUT_LINE(' There are now ' || num || ' employees.'); 9 END; 10 / SQL> DELETE FROM emp WHERE deptno = 30; There are now 8 employees. 6 rows deleted.SQL> DELETE FROM emp WHERE deptno = 30; There are now 8 employees. 6 rows deleted.

Купить

Похожие документы
Operatsion tizimlarda journaling file system
I/O scheduling algoritmlari
O‘rta Osiyo kutubxonalarining o‘rganilish masalalari
Funksiyalar va arrow function
Tashkilot xodimlari ma’lumotlarini jamlovchi tizim