Oracle PL/SQL ट्रिगर: & मिश्रित प्रकारों के बजाय

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

ट्रिगर्स संग्रहीत हैं PL / SQL वे प्रोग्राम जो द्वारा संचालित होते हैं Oracle इंजन स्वचालित रूप से जब डीएमएल कथन जैसे कि इंसर्ट, अपडेट और डिलीट जैसे ऑपरेशन टेबल पर या किसी खास घटना के घटित होने पर निष्पादित होते हैं। ट्रिगर के मामले में निष्पादित होने वाला कोड आवश्यकतानुसार परिभाषित किया जा सकता है। आप वह घटना चुन सकते हैं जिस पर ट्रिगर सक्रिय होना चाहिए और निष्पादन का समय भी निर्धारित कर सकते हैं। ट्रिगर का उद्देश्य डेटाबेस में मौजूद जानकारी की अखंडता बनाए रखना है।

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

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

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

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

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

समय के आधार पर वर्गीकरण

• ट्रिगर से पहले: यह निर्दिष्ट घटना घटित होने से पहले ही सक्रिय हो जाता है।
• ट्रिगर के बाद: यह निर्दिष्ट घटना घटित होने के बाद सक्रिय होता है।
• ट्रिगर के बजाय: एक विशेष प्रकार। आगे के विषयों में आपको इसके बारे में और अधिक जानकारी मिलेगी। (केवल डीएमएल के लिए)

स्तर के आधार पर वर्गीकरण

• कथन स्तर ट्रिगर: यह निर्दिष्ट इवेंट स्टेटमेंट के लिए एक बार ट्रिगर होता है।
• पंक्ति स्तर ट्रिगर: यह निर्दिष्ट घटना से प्रभावित प्रत्येक रिकॉर्ड के लिए सक्रिय होता है। (केवल DML के लिए)

घटना के आधार पर वर्गीकरण

• डीएमएल ट्रिगर: यह तब सक्रिय होता है जब डीएमएल इवेंट निर्दिष्ट किया जाता है (इन्सर्ट/अपडेट/डिलीट)।
• डीडीएल ट्रिगर: यह तब सक्रिय होता है जब डीडीएल इवेंट निर्दिष्ट किया जाता है (क्रिएट/अल्टर)।
• डेटाबेस ट्रिगर: यह तब सक्रिय होता है जब डेटाबेस इवेंट निर्दिष्ट किया जाता है (लॉगऑन/लॉगऑफ़/स्टार्टअप/शटडाउन)।

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

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

ट्रिगर बनाने के लिए नीचे दिया गया सिंटैक्स है। नीचे दिए गए स्क्रीनशॉट में ट्रिगर बनाने का यह सिंटैक्स दिखाया गया है। Oracle.

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 क्लॉज़ उस ऑब्जेक्ट को निर्दिष्ट करेगा जिस पर उपर्युक्त घटना मान्य है। उदाहरण के लिए, डीएमएल ट्रिगर के मामले में, यह उस टेबल का नाम होगा जिस पर डीएमएल घटना घटित हो सकती है।
• “FOR EACH ROW” कमांड पंक्ति स्तर ट्रिगर निर्दिष्ट करेगा।
• WHEN क्लॉज़ उस अतिरिक्त शर्त को निर्दिष्ट करेगा जिसके तहत ट्रिगर को सक्रिय होना आवश्यक है।
• घोषणा भाग, निष्पादन भाग और अपवाद प्रबंधन भाग अन्य सभी के समान हैं। PL/SQL ब्लॉकघोषणा भाग और उपवाद सम्भालना कुछ भाग वैकल्पिक हैं।

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

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

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

• :नया – ट्रिगर के निष्पादन के दौरान यह बेस टेबल/व्यू के कॉलम के लिए एक नया मान रखता है।
• :पुराना – यह ट्रिगर निष्पादन के दौरान बेस टेबल/व्यू के कॉलम के पुराने मान को बनाए रखता है।

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

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

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

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

उदाहरण 1: इस उदाहरण में, हम दो बेस टेबल से एक कॉम्प्लेक्स व्यू बनाने जा रहे हैं, जहां Table_1 एम्प्लॉई टेबल है और Table_2 डिपार्टमेंट टेबल है।

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

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

चरण 1) उपयुक्त स्तंभों के साथ 'emp' और 'dept' नाम की तालिकाएँ बनाएँ।

नीचे दिया गया स्क्रीनशॉट 'emp' और 'dept' बेस टेबल के निर्माण को दर्शाता है। Oracle.

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));
/

Code व्याख्या

• Code पंक्ति 1-7: टेबल 'emp' का निर्माण।
• Code पंक्ति 8-12: टेबल 'dept' का निर्माण।

आउटपुट:

Table Created

चरण 2) अब, चूंकि हमने टेबल बना ली हैं, इसलिए हम उनमें नमूना मान भरेंगे।

नीचे दिए गए स्क्रीनशॉट में 'dept' और 'emp' टेबल में डाली जा रही नमूना पंक्तियाँ दिखाई गई हैं।

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,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code व्याख्या

• Code पंक्ति 13-19: 'dept' टेबल में डेटा डाला जा रहा है।
• Code पंक्ति 20-26: 'emp' टेबल में डेटा डाला जा रहा है।

आउटपुट:

PL/SQL procedure completed

चरण 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;

Code व्याख्या

• Code पंक्ति 27-32: 'guru99_emp_view' व्यू का निर्माण।
• Code पंक्ति 33: guru99_emp_view क्वेरी की जा रही है।

आउटपुट:

View created

चरण 4) INSTEAD OF ट्रिगर से पहले व्यू का अपडेट।

नीचे दिए गए स्क्रीनशॉट में कॉम्प्लेक्स व्यू पर अपडेट करने का प्रयास और उसके परिणामस्वरूप उत्पन्न त्रुटि दिखाई गई है।

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

Code व्याख्या

• Code पंक्ति 34-38: “XXX” का स्थान बदलकर 'फ्रांस' कर दिया गया है। इससे एक अपवाद उत्पन्न हुआ क्योंकि कॉम्प्लेक्स व्यू पर डीएमएल स्टेटमेंट सीधे तौर पर उपयोग करने की अनुमति नहीं है।

आउटपुट:

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

चरण 5) पिछले चरण में व्यू को अपडेट करते समय आई त्रुटि से बचने के लिए, इस चरण में हम "INSTEAD OF ट्रिगर" का उपयोग करने जा रहे हैं।

नीचे दिया गया स्क्रीनशॉट INSTEAD OF ट्रिगर के निर्माण को दर्शाता है।

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

Code व्याख्या

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

आउटपुट:

Trigger Created

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

नीचे दिया गया स्क्रीनशॉट INSTEAD OF ट्रिगर के माध्यम से सफल अपडेट और रिफ्रेश किए गए दृश्य को दर्शाता है।

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

SELECT * FROM guru99_emp_view;

Code स्पष्टीकरण:

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

आउटपुट:

PL/SQL procedure successfully completed

यौगिक ट्रिगर

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

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

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

नीचे दिए गए स्क्रीनशॉट में कंपाउंड ट्रिगर सिंटैक्स को उसके चार टाइमिंग सेक्शन के साथ दिखाया गया है।

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
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' ट्रिगर के निर्माण को दर्शाता है।
• ट्रिगर बॉडी में मौजूद सभी निष्पादन ब्लॉकों के लिए घोषणात्मक अनुभाग समान होता है।
• ये चारों टाइमिंग ब्लॉक किसी भी क्रम में हो सकते हैं। चारों टाइमिंग ब्लॉक का होना अनिवार्य नहीं है। हम केवल आवश्यक टाइमिंग के लिए ही कंपाउंड ट्रिगर बना सकते हैं।

उदाहरण 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;

Code स्पष्टीकरण:

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

आउटपुट:

Trigger created

PL/SQL procedure successfully completed.

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

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

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

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

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

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

अक्सर पूछे जाने वाले प्रश्न