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

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

Trigger yaratishning sintaksisi

• CREATE [OR REPLACE] TRIGGER trigger_nomi
• hodisa1 OR hodisa2 OR hodisa3
• ON ob`yekt_nomi
• BEGIN
• SQL so`rov;
• END;

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

• 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