Oracle PL / SQL ट्रिगर ट्यूटोरियल: इसके बजाय, कंपाउंड (उदाहरण)

विषय - सूची:

Anonim

PL / SQL में ट्रिगर क्या है?

TRIGGERS ऐसे प्रोग्राम हैं जो Oracle इंजन द्वारा स्वचालित रूप से निकाल दिए जाते हैं जब DML स्टेटमेंट जैसे इन्सर्ट, अपडेट, डिलीट को टेबल पर निष्पादित किया जाता है या कुछ ईवेंट होते हैं। ट्रिगर के मामले में बहिष्कृत कोड को आवश्यकता के अनुसार परिभाषित किया जा सकता है। आप उस घटना को चुन सकते हैं जिस पर ट्रिगर को निकाल दिया जाना चाहिए और निष्पादन का समय। ट्रिगर का उद्देश्य डेटाबेस पर जानकारी की अखंडता को बनाए रखना है।

इस ट्यूटोरियल में, आप सीखेंगे-

  • ट्रिगर के लाभ
  • Oracle में ट्रिगर के प्रकार
  • ट्रिगर कैसे बनाएँ
  • : नया और: पुराने खंड
  • ट्रिगर का पहला
  • यौगिक ट्रिगर

ट्रिगर के लाभ

ट्रिगर्स के लाभ निम्नलिखित हैं।

  • स्वचालित रूप से कुछ व्युत्पन्न स्तंभ मान उत्पन्न करना
  • संदर्भात्मक अखंडता को लागू करना
  • टेबल प्रवेश पर सूचना लॉगिंग और भंडारण जानकारी
  • लेखा परीक्षा
  • तालिकाओं की तुल्यकालिक प्रतिकृति
  • सुरक्षा प्राधिकरणों का निपटान
  • अमान्य लेनदेन को रोकना

Oracle में ट्रिगर के प्रकार

ट्रिगर को निम्न मापदंडों के आधार पर वर्गीकृत किया जा सकता है।

  • समय के आधार पर वर्गीकरण
    • पहले ट्रिगर: निर्दिष्ट घटना होने से पहले यह आग लग जाती है।
    • ट्रिगर के बाद: निर्दिष्ट घटना होने के बाद यह आग लग जाती है।
    • ट्रिगर का पहला चरण: एक विशेष प्रकार। आप आगे के विषयों के बारे में अधिक जानेंगे। (केवल डीएमएल के लिए)
  • स्तर के आधार पर वर्गीकरण
    • स्थिति स्तर ट्रिगर: यह निर्दिष्ट ईवेंट स्टेटमेंट के लिए एक बार फायर करता है।
    • ROW स्तर ट्रिगर: यह प्रत्येक रिकॉर्ड के लिए फायर करता है जो निर्दिष्ट घटना में प्रभावित हुआ। (केवल डीएमएल के लिए)
  • घटना के आधार पर वर्गीकरण
    • डीएमएल ट्रिगर: यह डीएमएल घटना निर्दिष्ट होने पर फायर करता है (INSERT / UPDATE / DELETE)
    • DDL ट्रिगर: DDL घटना निर्दिष्ट होने पर यह आग लगाता है (सृजन / चेतावनी)
    • डेटाबेस ट्रिगर: यह आग लग जाती है जब डेटाबेस घटना निर्दिष्ट होती है (लॉगऑन / लोगो / STARTUP / SHDDOWN)

इसलिए प्रत्येक ट्रिगर उपरोक्त मापदंडों का संयोजन है।

ट्रिगर कैसे बनाएँ

ट्रिगर बनाने के लिए नीचे सिंटैक्स है।

CREATE [ OR REPLACE ] TRIGGER 
[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON[FOR EACH ROW][WHEN ]DECLAREBEGINEXCEPTIONEND;

सिंटैक्स स्पष्टीकरण:

  • उपरोक्त वाक्यविन्यास विभिन्न वैकल्पिक बयानों को दर्शाता है जो ट्रिगर निर्माण में मौजूद हैं।
  • घटना के समय से पहले / बाद में निर्दिष्ट किया जाएगा।
  • INSERT / अद्यतन / लोगो / सृजन / आदि। उस घटना को निर्दिष्ट करेगा जिसके लिए ट्रिगर को निकाल दिया जाना चाहिए।
  • उपवाक्य निर्दिष्ट करेगा कि किस वस्तु पर उपर्युक्त घटना मान्य है। उदाहरण के लिए, यह तालिका का नाम होगा, जिस पर डीएमएल घटना डीएमएल ट्रिगर के मामले में हो सकती है।
  • "EACH ROW के लिए" कमांड ROW स्तर ट्रिगर को निर्दिष्ट करेगा।
  • जब खंड अतिरिक्त स्थिति को निर्दिष्ट करेगा जिसमें ट्रिगर को आग लगानी होगी।
  • घोषणा का हिस्सा, निष्पादन भाग, अपवाद हैंडलिंग भाग अन्य PL / SQL ब्लॉक के समान है। घोषणा भाग और अपवाद हैंडलिंग हिस्सा वैकल्पिक हैं।

: नया और: पुराने खंड

एक पंक्ति स्तर ट्रिगर में, ट्रिगर प्रत्येक संबंधित पंक्ति के लिए आग लगाता है। और कभी-कभी डीएमएल बयान से पहले और बाद में मूल्य जानना आवश्यक है।

Oracle ने इन मूल्यों को धारण करने के लिए RECORD- स्तर ट्रिगर में दो खंड दिए हैं। हम ट्रिगर बॉडी के अंदर पुराने और नए मूल्यों को संदर्भित करने के लिए इन क्लॉस का उपयोग कर सकते हैं।

  • : NEW - यह ट्रिगर निष्पादन के दौरान बेस टेबल / दृश्य के कॉलम के लिए एक नया मान रखता है
  • : OLD - यह ट्रिगर निष्पादन के दौरान बेस टेबल / दृश्य के कॉलम का पुराना मूल्य रखता है

इस क्लॉज का इस्तेमाल डीएमएल इवेंट के आधार पर किया जाना चाहिए। नीचे दी गई तालिका में निर्दिष्ट किया जाएगा कि कौन सा खंड डीएमएल स्टेटमेंट (INSERT / UPDATE / DELETE) के लिए मान्य है।

सम्मिलित करें अपडेट करें हटाएँ
:नवीन व वैध वैध निवेश करें। डिलीट केस में कोई नई वैल्यू नहीं है।
:पुराना निवेश करें। डालने के मामले में कोई पुराना मूल्य नहीं है वैध वैध

ट्रिगर का पहला

"ट्रिगर का INSTEAD" विशेष प्रकार का ट्रिगर है। इसका उपयोग केवल डीएमएल ट्रिगर में किया जाता है। इसका उपयोग तब किया जाता है जब कोई डीएमएल इवेंट जटिल दृश्य पर होने वाला हो।

एक उदाहरण पर विचार करें जिसमें 3 बेस टेबल से एक दृश्य बनाया गया है। जब कोई DML घटना इस दृश्य पर जारी की जाती है, तो वह अमान्य हो जाएगी क्योंकि डेटा 3 अलग-अलग तालिकाओं से लिया गया है। तो इस INSTEAD OF ट्रिगर का उपयोग किया जाता है। ट्रिगर की INSTEAD का उपयोग दी गई घटना के लिए दृश्य को संशोधित करने के बजाय सीधे आधार तालिकाओं को संशोधित करने के लिए किया जाता है।

उदाहरण 1 : इस उदाहरण में, हम दो बेस टेबल से एक जटिल दृश्य बनाने जा रहे हैं।

  • Table_1 एंपायर टेबल है और
  • Table_2 विभाग की तालिका है।

फिर हम यह देखने जा रहे हैं कि इस जटिल दृश्य पर स्थान विवरण विवरण जारी करने के लिए INSTEAD ऑफ़ ट्रिगर का उपयोग कैसे किया जाता है। हम यह भी देखने जा रहे हैं कि कैसे: NEW और: OLD ट्रिगर्स में उपयोगी है।

  • चरण 1: उपयुक्त स्तंभों के साथ तालिका 'एम्प' और 'विभाग' बनाना
  • चरण 2: नमूना मूल्यों के साथ तालिका को आबाद करना
  • चरण 3: उपरोक्त बनाई गई तालिका के लिए दृश्य बनाना
  • चरण 4: ट्रिगर के बजाय देखने से पहले का अद्यतन
  • चरण 5: ट्रिगर के बजाय का निर्माण
  • चरण 6: ट्रिगर के बजाय देखने का अद्यतन

चरण 1) उपयुक्त स्तंभों के साथ तालिका 'एम्प' और 'विभाग' बनाना

CREATE TABLE emp(emp_no NUMBER,emp_name VARCHAR2(50),salary NUMBER,manager VARCHAR2(50),dept_no NUMBER);/CREATE TABLE dept(Dept_no NUMBER,Dept_name VARCHAR2(50),LOCATION VARCHAR2(50));/

कोड स्पष्टीकरण

  • कोड लाइन 1-7 : टेबल 'एम्पायर' निर्माण।
  • कोड लाइन 8-12 : टेबल 'विभाग' निर्माण।

उत्पादन

टेबल बनाया गया

चरण 2) अब चूंकि हमने तालिका बनाई है, इसलिए हम उपरोक्त तालिकाओं के लिए नमूना मूल्यों और व्यूज़ क्रिएशन के साथ इस तालिका को आबाद करेंगे। 

BEGININSERT INTO DEPT VALUES(10,‘HR’,‘USA’);INSERT INTO DEPT VALUES(20,'SALES','UK’);INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN');COMMIT;END;/BEGININSERT INTO EMP VALUES(1000,'XXX5,15000,'AAA',30);INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10);COMMIT;END;/

कोड स्पष्टीकरण

  • कोड लाइन 13-19 : 'विभाग' तालिका में डेटा सम्मिलित करना।
  • कोड लाइन 20-26: डेटा को 'एम्प' टेबल में सम्मिलित करना।

उत्पादन

PL / SQL प्रक्रिया पूरी की

चरण 3) उपरोक्त बनाई गई तालिका के लिए एक दृश्य बनाना। 

CREATE VIEW guru99_emp_view(Employee_name:dept_name,location) ASSELECT emp.emp_name,dept.dept_name,dept.locationFROM emp,deptWHERE emp.dept_no=dept.dept_no;/
SELECT * FROM guru99_emp_view;

कोड स्पष्टीकरण

  • कोड लाइन 27-32: 'Guru99_emp_view' दृश्य का निर्माण।
  • कोड लाइन 33: गुरु को छोड़ना99_emp_view।

उत्पादन

दृश्य बनाया गया

कर्मचारी का नाम DEPT_NAME स्थान
ZZZ मानव संसाधन अमेरीका
YYY बिक्री यूके
XXX वित्तीय जापान

चरण 4) ट्रिगर के बजाय देखने से पहले अद्यतन।

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’;COMMIT;END;/

कोड स्पष्टीकरण

  • कोड लाइन 34-38: "XXX" के स्थान को 'फ्रांस' में अपडेट करें। इसने इसका अपवाद उठाया क्योंकि डीएमएल के बयानों को जटिल दृश्य में अनुमति नहीं है।

उत्पादन

ORA-01779: एक स्तंभ को संशोधित नहीं कर सकता जो गैर-संरक्षित तालिका में मैप करता है

ORA-06512: लाइन 2 पर

चरण 5) पिछले चरण में दृश्य अद्यतन करने के दौरान त्रुटि से बचने के लिए, इस चरण में हम "ट्रिगर के बजाय" का उपयोग करने जा रहे हैं।

CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE dept_name=:old.dept_name;END;/

कोड स्पष्टीकरण

  • कोड लाइन 39: ROW स्तर पर 'गुरु99_emp_view' दृश्य पर 'अद्यतन' घटना के लिए ट्रिगर की स्थापना। इसमें आधार तालिका 'विभाग' में स्थान को अद्यतन करने के लिए अद्यतन विवरण है।
  • कोड लाइन 44: अपडेट स्टेटमेंट अपडेट के पहले और बाद में कॉलम का मान ज्ञात करने के लिए ': NEW' और ': OLD' का उपयोग करता है।

उत्पादन

ट्रिगर बनाया गया

चरण 6) ट्रिगर के बजाय देखने का अद्यतन। अब त्रुटि "ट्रिगर के बजाय" के रूप में नहीं आएगी जो इस जटिल दृश्य के अपडेट ऑपरेशन को संभाल लेगी। और जब कोड ने कर्मचारी के स्थान को निष्पादित किया है XXX को "जापान" से "फ्रांस" में अपडेट किया जाएगा।

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';COMMIT;END;/
SELECT * FROM guru99_emp_view;

कोड स्पष्टीकरण:

  • कोड लाइन 49-53: "XXX" के स्थान का अद्यतन 'फ्रांस' के लिए। यह सफल है क्योंकि 'INSTEAD OF' ट्रिगर ने वास्तविक अपडेट स्टेटमेंट को देखने पर रोक दिया है और बेस टेबल अपडेट का प्रदर्शन किया है।
  • कोड लाइन 55: अपडेट किए गए रिकॉर्ड को सत्यापित करना।

आउटपुट:

PL / SQL प्रक्रिया सफलतापूर्वक पूर्ण हुई

कर्मचारी का नाम DEPT_NAME स्थान
ZZZ मानव संसाधन अमेरीका
YYY बिक्री यूके
XXX वित्तीय फ्रांस

यौगिक ट्रिगर

कम्पाउंड ट्रिगर एक ट्रिगर है जो आपको सिंगल ट्रिगर बॉडी में चार टाइमिंग पॉइंट्स में से प्रत्येक के लिए एक्शन निर्दिष्ट करने की अनुमति देता है। चार अलग-अलग समय बिंदु जो इसका समर्थन करते हैं, नीचे दिए गए हैं।

  • पहले के स्तर - स्तर
  • पहले से कम स्तर
  • स्तर के बाद - स्तर
  • स्तर के बाद - स्तर

यह अलग-अलग समय के लिए क्रियाओं को एक ही ट्रिगर में संयोजित करने की सुविधा प्रदान करता है।

CREATE [ OR REPLACE ] TRIGGER 
FOR[INSERT | UPDATE | DELET… .]ON ‭ ‬BEFORE STATEMENT ISBEGIN;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN;END EACH ROW;AFTER EACH ROW ISBEGIN;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN;END AFTER STATEMENT;END;

सिंटैक्स स्पष्टीकरण:

  • उपरोक्त सिंटैक्स 'COMPOUND' ट्रिगर के निर्माण को दर्शाता है।
  • ट्रिगर बॉडी में सभी एक्ज़क्यूटिव ब्लॉक के लिए डिक्लेरेटिव सेक्शन आम है।
  • ये 4 टाइमिंग ब्लॉक किसी भी क्रम में हो सकते हैं। ये सभी 4 टाइमिंग ब्लॉक होना अनिवार्य नहीं है। हम केवल आवश्यक समय के लिए एक COMPOUND ट्रिगर बना सकते हैं।

उदाहरण 1 : इस उदाहरण में, हम डिफ़ॉल्ट मान 5000 के साथ वेतन कॉलम को ऑटो-पॉप्युलेट करने के लिए ट्रिगर बनाने जा रहे हैं।

CREATE TRIGGER emp_trigFOR INSERTON empCOMPOUND TRIGGERBEFORE EACH ROW ISBEGIN:new.salary:=5000;END BEFORE EACH ROW;END emp_trig;/
BEGININSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30);COMMIT;END;/
SELECT * FROM emp WHERE emp_no=1004;

कोड स्पष्टीकरण:

  • कोड लाइन 2-10 : कंपाउंड ट्रिगर का निर्माण। यह डिफ़ॉल्ट मान 5000 के साथ वेतन को पॉपुलर करने के लिए BE RORE- स्तर के समय के लिए बनाया गया है। यह तालिका में रिकॉर्ड डालने से पहले वेतन को डिफ़ॉल्ट मूल्य '5000' में बदल देगा।
  • कोड लाइन ११-११ : रिकॉर्ड को 'एम्प ’टेबल में डालें।
  • कोड लाइन 16 : सम्मिलित रिकॉर्ड को सत्यापित करना।

आउटपुट:

ट्रिगर बनाया

PL / SQL प्रक्रिया सफलतापूर्वक पूर्ण हुई।

EMP_NAME कर्मचारी सं वेतन मैनेजर DEPT_NO
सीसीसी 1004 5000 एएए ३०

ट्रिगर को सक्षम और अक्षम करना

ट्रिगर सक्षम या अक्षम किया जा सकता है। ट्रिगर को सक्षम या अक्षम करने के लिए, उस अक्षम या सक्षम करने वाले ट्रिगर के लिए ALTER (DDL) स्टेटमेंट देना होगा।

नीचे ट्रिगर्स को सक्षम / अक्षम करने के लिए सिंटैक्स हैं।

ALTER TRIGGER 
 [ENABLE|DISABLE];ALTER TABLE 
 [ENABLE|DISABLE] ALL TRIGGERS;

सिंटैक्स स्पष्टीकरण:

  • पहला सिंटैक्स दिखाता है कि एकल ट्रिगर को कैसे सक्षम / अक्षम किया जाए।
  • दूसरा कथन दिखाता है कि किसी विशेष तालिका पर सभी ट्रिगर को कैसे अक्षम / अक्षम किया जाए।

सारांश

इस अध्याय में, हमने PL / SQL ट्रिगर और उनके फायदों के बारे में जाना है। हमने अलग-अलग वर्गीकरण भी सीखे हैं और INSTEAD OF ट्रिगर और COMPOUND ट्रिगर पर चर्चा की है।