Ma’lumotlar bazasida triggerlar bilan ishlash - Informatika va AT - Slaydlar

Ma’lumotlar bazasida triggerlar bilan ishlash

Yuklangan vaqt:

10.12.2024

Ko'chirishlar soni:

0

Hajmi:

84.8134765625 KB

Ko'chirib olish

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.

O'xshashlar

Web serverlarda ma’lumotlar modeli. Relyatsion ma’lulotlar bazasida munosabatlar

Realyatsion ma’lumotlar bazalarini yuqori darajadagi dasturlash tillari bilan ishlash

Elektron pochta bilan ishlash

Excelda diagrammalar bilan ishlash

Sayt dizayni bilan ishlash