Ma'lumotlar bazasida tranzaksiya mbbtda ma`lumotlar bilan ishlashda triggerlarni ishlatish



Download 0,65 Mb.
bet4/4
Sana31.12.2021
Hajmi0,65 Mb.
#221888
1   2   3   4
Bog'liq
2-mavzu

Triggerga misol

  • SQL> CREATE TRIGGER trigger_1
  • 2 BEFORE UPDATE OF ism
  • 3 ON talaba_1
  • 4 BEGIN
  • 5 INSERT INTO talaba_2(‘ism’, ‘familiya’)
  • 6 VALUES (‘Baxrom’, ‘Qosimov’);
  • 7 END;
  • 8 /
  • Trigger 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;
  • END
  • Bu yerda FOR EACH ROW har bir satrni tekshirishga imkon beradi.
  • SQL so`rov BEGIN va END o`rtasida yoziladi.
  • 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 bazasi yaxlitligini tekshiradi
        • Ma`lumot qo`shilganda yoki o`chirilganda uning ekvalentlari to`g`risida xabar beradi;
    • Tarkibiy ma`lumotlarni saqlash
        • masalan: kiritilayotgan ma`lumotlar ob`yektga mos bo`lishi kerak;
    • Xavfsizlikni boshqarish
        • Masalan: Foydalanuvchi imtiyozlarini tekshirish
  • SQL>CREATE OR REPLACE TRIGGER audit_emp
  • AFTER UPDATE AND 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_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 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 it
  • Trigger orqali o`zgartirishga misol

Triggerga misol

  • 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.

Download 0,65 Mb.

Do'stlaringiz bilan baham:
1   2   3   4




Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©hozir.org 2024
ma'muriyatiga murojaat qiling

kiriting | ro'yxatdan o'tish
    Bosh sahifa
юртда тантана
Боғда битган
Бугун юртда
Эшитганлар жилманглар
Эшитмадим деманглар
битган бодомлар
Yangiariq tumani
qitish marakazi
Raqamli texnologiyalar
ilishida muhokamadan
tasdiqqa tavsiya
tavsiya etilgan
iqtisodiyot kafedrasi
steiermarkischen landesregierung
asarlaringizni yuboring
o'zingizning asarlaringizni
Iltimos faqat
faqat o'zingizning
steierm rkischen
landesregierung fachabteilung
rkischen landesregierung
hamshira loyihasi
loyihasi mavsum
faolyatining oqibatlari
asosiy adabiyotlar
fakulteti ahborot
ahborot havfsizligi
havfsizligi kafedrasi
fanidan bo’yicha
fakulteti iqtisodiyot
boshqaruv fakulteti
chiqarishda boshqaruv
ishlab chiqarishda
iqtisodiyot fakultet
multiservis tarmoqlari
fanidan asosiy
Uzbek fanidan
mavzulari potok
asosidagi multiservis
'aliyyil a'ziym
billahil 'aliyyil
illaa billahil
quvvata illaa
falah' deganida
Kompyuter savodxonligi
bo’yicha mustaqil
'alal falah'
Hayya 'alal
'alas soloh
Hayya 'alas
mavsum boyicha


yuklab olish