Oracle PL/SQL ट्रिगर ट्यूटोरियल: कम्पाउंड के बजाय [उदाहरण]

द्वारा: फियोना ब्राउन फियोना ब्राउन
Hours Updated

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

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

ट्रिगर्स के लाभ

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

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

ट्रिगर्स के प्रकार Oracle

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

  • वर्गीकरण के आधार पर समय
  • BEFORE ट्रिगर: यह निर्दिष्ट घटना घटित होने से पहले फायर होता है।
  • AFTER ट्रिगर: यह निर्दिष्ट घटना घटित होने के बाद फायर होता है।
  • ट्रिगर के बजाय: एक विशेष प्रकार। आप आगे के विषयों के बारे में अधिक जानेंगे। (केवल DML के लिए)
  • वर्गीकरण के आधार पर स्तर
  • स्टेटमेंट स्तर ट्रिगर: यह निर्दिष्ट इवेंट स्टेटमेंट के लिए एक बार फायर करता है।
  • ROW स्तर ट्रिगर: यह प्रत्येक रिकॉर्ड के लिए फायर करता है जो निर्दिष्ट घटना में प्रभावित हुआ है। (केवल DML के लिए)
  • वर्गीकरण के आधार पर कार्यक्रम
  • डीएमएल ट्रिगर: यह तब सक्रिय होता है जब डीएमएल इवेंट निर्दिष्ट किया जाता है (INSERT/UPDATE/DELETE)
  • DDL ट्रिगर: यह तब सक्रिय होता है जब DDL इवेंट निर्दिष्ट किया जाता है (CREATE/ALTER)
  • डेटाबेस ट्रिगर: यह तब सक्रिय होता है जब डेटाबेस इवेंट निर्दिष्ट किया जाता है (लॉगऑन/लॉगऑफ/स्टार्टअप/शटडाउन)

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

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

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

ट्रिगर बनाएं

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

वाक्यविन्यास स्पष्टीकरण:

  • उपरोक्त सिंटैक्स ट्रिगर निर्माण में मौजूद विभिन्न वैकल्पिक कथनों को दर्शाता है।
  • BEFORE/ AFTER इवेंट का समय निर्दिष्ट करेगा।
  • INSERT/UPDATE/LOGON/CREATE/आदि उस घटना को निर्दिष्ट करेंगे जिसके लिए ट्रिगर को फायर किया जाना आवश्यक है।
  • ON क्लॉज यह निर्दिष्ट करेगा कि ऊपर उल्लिखित इवेंट किस ऑब्जेक्ट पर वैध है। उदाहरण के लिए, यह वह टेबल नाम होगा जिस पर DML ट्रिगर के मामले में DML इवेंट हो सकता है।
  • कमांड “FOR EACH ROW” ROW स्तर ट्रिगर निर्दिष्ट करेगा।
  • WHEN क्लॉज उस अतिरिक्त स्थिति को निर्दिष्ट करेगा जिसमें ट्रिगर को फायर करना आवश्यक होगा।
  • घोषणा भाग, निष्पादन भाग, अपवाद प्रबंधन भाग अन्य के समान ही है PL/SQL ब्लॉकघोषणा भाग और अपवाद हैंडलिंग भाग वैकल्पिक हैं।

संबंधित आलेख

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

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

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

  • :नया – यह ट्रिगर निष्पादन के दौरान आधार तालिका/दृश्य के स्तंभों के लिए एक नया मान रखता है
  • :OLD – यह ट्रिगर निष्पादन के दौरान आधार तालिका/दृश्य के स्तंभों का पुराना मान रखता है

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

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

ट्रिगर के बजाय

"INSTEAD OF ट्रिगर" एक विशेष प्रकार का ट्रिगर है। इसका उपयोग केवल DML ट्रिगर्स में किया जाता है। इसका उपयोग तब किया जाता है जब कॉम्प्लेक्स व्यू पर कोई DML इवेंट होने वाला होता है।

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

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

  • तालिका_1 emp तालिका है और
  • तालिका_2 विभाग तालिका है.

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

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

चरण 1) उपयुक्त कॉलम के साथ तालिका 'emp' और 'dept' बनाना

ट्रिगर के बजाय

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: तालिका 'emp' निर्माण.
  • कोड लाइन 8-12: तालिका 'विभाग' निर्माण.

उत्पादन

तालिका बनाई गई

चरण 2) अब चूंकि हमने तालिका बना ली है, हम इस तालिका को नमूना मानों से भरेंगे और उपरोक्त तालिकाओं के लिए दृश्य बनाएंगे।

ट्रिगर के बजाय

BEGIN
INSERT INTO DEPT VALUES(10,‘HR’,‘USA’);
INSERT INTO DEPT VALUES(20,'SALES','UK’);
INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN'); 
COMMIT;
END;
/

BEGIN
INSERT 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: 'dept' तालिका में डेटा प्रविष्ट किया जा रहा है।
  • कोड पंक्ति 20-26: 'emp' तालिका में डेटा सम्मिलित करना।

उत्पादन

पीएल/एसक्यूएल प्रक्रिया पूरा

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

ट्रिगर के बजाय

CREATE VIEW guru99_emp_view(
Employee_name:dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

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

  • कोड पंक्ति 27-32: 'guru99_emp_view' दृश्य का निर्माण.
  • कोड पंक्ति 33: guru99_emp_view क्वेरी की जा रही है।

उत्पादन

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

कर्मचारी_नाम विभाग_नाम LOCATION
ZZZ HR अमेरिका
YYY बिक्री UK
XXX वित्तीय जापान

चरण 4) ट्रिगर के बजाय दृश्य का अद्यतन.

ट्रिगर के बजाय

BEGIN
UPDATE 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_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

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

  • कोड पंक्ति 39: ROW स्तर पर 'guru99_emp_view' दृश्य पर 'UPDATE' इवेंट के लिए INSTEAD OF ट्रिगर का निर्माण। इसमें बेस टेबल 'dept' में स्थान को अपडेट करने के लिए अपडेट स्टेटमेंट शामिल है।
  • कोड पंक्ति 44: अद्यतन विवरण अद्यतन से पहले और बाद के स्तंभों का मान ज्ञात करने के लिए ':NEW' और ':OLD' का उपयोग करता है।

उत्पादन

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

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

ट्रिगर के बजाय

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX'; 
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

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

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

आउटपुट:

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

कर्मचारी_नाम विभाग_नाम LOCATION
ZZZ HR अमेरिका
YYY बिक्री UK
XXX वित्तीय फ्रांस

यौगिक ट्रिगर

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

  • बयान से पहले – स्तर
  • पहले पंक्ति – स्तर
  • पंक्ति के बाद – स्तर
  • बयान के बाद – स्तर

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

यौगिक ट्रिगर

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
ON <name of underlying object>
<Declarative part>‭	‬
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

वाक्यविन्यास स्पष्टीकरण:

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

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

यौगिक ट्रिगर

CREATE TRIGGER emp_trig 
FOR INSERT 
ON emp
COMPOUND TRIGGER 
BEFORE EACH ROW IS 
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30); 
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

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

  • कोड लाइन 2-10: कंपाउंड ट्रिगर का निर्माण। यह वेतन को डिफ़ॉल्ट मान 5000 के साथ पॉप्युलेट करने के लिए ROW- स्तर से पहले समय निर्धारण के लिए बनाया गया है। यह रिकॉर्ड को तालिका में डालने से पहले वेतन को डिफ़ॉल्ट मान '5000' में बदल देगा।
  • कोड लाइन 11-14: रिकॉर्ड को 'emp' तालिका में डालें.
  • कोड लाइन 16: सम्मिलित रिकॉर्ड का सत्यापन किया जा रहा है.

आउटपुट:

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

PL/SQL प्रक्रिया सफलतापूर्वक पूरी हो गई।

EMP_नाम EMP_नहीं वेतन पोर्टफोलियो विभाग_सं.
सीसीसी 1004 5000 एएए 30

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

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

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

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

वाक्यविन्यास स्पष्टीकरण:

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

सारांश

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