Oracle Οδηγός ενεργοποίησης PL/SQL: Αντί για, Σύνθετο [Παράδειγμα]

Τι είναι το Trigger στο PL/SQL;

ΣΚΑΝΔΙΣΤΕΣ είναι αποθηκευμένα προγράμματα που ενεργοποιούνται από Oracle αυτόματα όταν εκτελούνται δηλώσεις DML όπως εισαγωγή, ενημέρωση, διαγραφή στον πίνακα ή συμβαίνουν κάποια συμβάντα. Ο κωδικός που πρέπει να εκτελεστεί σε περίπτωση ενεργοποίησης μπορεί να οριστεί σύμφωνα με την απαίτηση. Μπορείτε να επιλέξετε το συμβάν κατά το οποίο πρέπει να ενεργοποιηθεί η σκανδάλη και το χρονοδιάγραμμα της εκτέλεσης. Ο σκοπός της ενεργοποίησης είναι η διατήρηση της ακεραιότητας των πληροφοριών στη βάση δεδομένων.

Οφέλη από Triggers

Ακολουθούν τα οφέλη των ερεθισμάτων.

Αυτόματη δημιουργία ορισμένων παραγόμενων τιμών στηλών
Επιβολή αναφορικής ακεραιότητας
Καταγραφή συμβάντων και αποθήκευση πληροφοριών σχετικά με την πρόσβαση σε τραπέζι
Ελεγκτικά
Syncεπίμονη αναπαραγωγή πινάκων
Επιβολή αδειών ασφαλείας
Αποτροπή μη έγκυρων συναλλαγών

Τύποι ενεργοποίησης σε Oracle

Οι ενεργοποιητές μπορούν να ταξινομηθούν με βάση τις ακόλουθες παραμέτρους.

Ταξινόμηση με βάση το συγχρονισμός
BEFORE Trigger: Ενεργοποιείται πριν συμβεί το καθορισμένο συμβάν.
AFTER Trigger: Ενεργοποιείται αφού συμβεί το καθορισμένο συμβάν.
ΑΝΤΙ ΣΚΑΝΔΡΑΣ: Ειδικός τύπος. Θα μάθετε περισσότερα για τα περαιτέρω θέματα. (μόνο για DML)
Ταξινόμηση με βάση το επίπεδο
Ενεργοποίηση επιπέδου STATEMENT: Ενεργοποιείται μία φορά για την καθορισμένη δήλωση συμβάντος.
Ενεργοποίηση επιπέδου ROW: Ενεργοποιείται για κάθε εγγραφή που επηρεάστηκε στο καθορισμένο συμβάν. (μόνο για DML)
Ταξινόμηση με βάση το Συμβάν
Έναρξη DML: Ενεργοποιείται όταν έχει καθοριστεί το συμβάν DML (INSERT/UPDATE/DELETE)
Ενεργοποίηση DDL: Ενεργοποιείται όταν έχει καθοριστεί το συμβάν DDL (ΔΗΜΙΟΥΡΓΙΑ/ΑΛΛΑΓΗ)
Ενεργοποίηση ΒΑΣΕΩΝ ΔΕΔΟΜΕΝΩΝ: Ενεργοποιείται όταν έχει καθοριστεί το συμβάν της βάσης δεδομένων (LOGON/LOGOFF/STARTUP/SHUTDOWN)

Έτσι, κάθε έναυσμα είναι ο συνδυασμός των παραπάνω παραμέτρων.

Πώς να δημιουργήσετε έναυσμα

Παρακάτω είναι η σύνταξη για τη δημιουργία ενός κανόνα.

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.
Η εντολή "ΓΙΑ ΚΑΘΕ ΣΕΙΡΑ" θα καθορίσει την ενεργοποίηση του επιπέδου ROW.
Η ρήτρα WHEN θα καθορίσει την πρόσθετη συνθήκη στην οποία πρέπει να ενεργοποιηθεί η σκανδάλη.
Το τμήμα δήλωσης, μέρος εκτέλεσης, τμήμα χειρισμού εξαιρέσεων είναι το ίδιο με αυτό του άλλου Μπλοκ PL/SQL. Το τμήμα δήλωσης και το τμήμα χειρισμού εξαιρέσεων είναι προαιρετικά.

:ΝΕΟ και :ΠΑΛΙΟ Ρήτρα

Σε μια σκανδάλη σε επίπεδο σειράς, η ενεργοποίηση ενεργοποιείται για κάθε σχετική σειρά. Και μερικές φορές απαιτείται να γνωρίζετε την τιμή πριν και μετά τη δήλωση DML.

Oracle έχει παράσχει δύο ρήτρες στο έναυσμα σε επίπεδο RECORD για τη διατήρηση αυτών των τιμών. Μπορούμε να χρησιμοποιήσουμε αυτές τις ρήτρες για να αναφερθούμε στις παλιές και τις νέες τιμές μέσα στο σώμα της σκανδάλης.

:NEW – Διατηρεί μια νέα τιμή για τις στήλες του βασικού πίνακα/προβολής κατά την εκτέλεση της ενεργοποίησης
:OLD – Διατηρεί την παλιά τιμή των στηλών του βασικού πίνακα/προβολής κατά την εκτέλεση της ενεργοποίησης

Αυτή η ρήτρα θα πρέπει να χρησιμοποιείται με βάση το συμβάν DML. Ο παρακάτω πίνακας θα καθορίσει ποια ρήτρα είναι έγκυρη για ποια δήλωση DML (INSERT/UPDATE/DELETE).

	ΕΝΘΕΤΟ	ΕΚΣΥΓΧΡΟΝΊΖΩ	ΔΙΑΓΡΑΦΗ
:ΝΕΟΣ	ΕΓΚΥΡΟΣ	ΕΓΚΥΡΟΣ	ΜΗ ΕΓΚΥΡΟ. Δεν υπάρχει νέα τιμή στην περίπτωση διαγραφής.
:ΠΑΛΑΙΟΣ	ΜΗ ΕΓΚΥΡΟ. Δεν υπάρχει παλιά τιμή στο ένθετο	ΕΓΚΥΡΟΣ	ΕΓΚΥΡΟΣ

ΑΝΤΙ ΣΚΑΝΔΙΣΤΗΣ

Το "ΑΝΤΙ ΤΗΣ σκανδάλης" είναι ο ειδικός τύπος σκανδάλης. Χρησιμοποιείται μόνο σε ενεργοποιητές DML. Χρησιμοποιείται όταν πρόκειται να συμβεί οποιοδήποτε συμβάν DML στη σύνθετη προβολή.

Εξετάστε ένα παράδειγμα στο οποίο δημιουργείται μια προβολή από 3 πίνακες βάσης. Όταν εκδίδεται οποιοδήποτε συμβάν DML σε αυτήν την αναλυτική προβολή, αυτό θα καταστεί άκυρο επειδή τα δεδομένα λαμβάνονται από 3 διαφορετικούς πίνακες. Έτσι σε αυτό το ΑΝΤΙ ΓΙΑ χρησιμοποιείται η σκανδάλη. Το έναυσμα ΑΝΤΙ ΝΑ χρησιμοποιείται για την άμεση τροποποίηση των βασικών πινάκων αντί για την τροποποίηση της προβολής για το δεδομένο συμβάν.

Παράδειγμα 1: Σε αυτό το παράδειγμα, θα δημιουργήσουμε μια σύνθετη προβολή από δύο βασικούς πίνακες.

Ο πίνακας_1 είναι πίνακας emp και

Ο πίνακας_2 είναι πίνακας τμήματος.

Στη συνέχεια, θα δούμε πώς χρησιμοποιείται το έναυσμα ΑΝΤΙ ΓΙΑ την έκδοση ΕΝΗΜΕΡΩΣΗ της δήλωσης λεπτομερειών τοποθεσίας σε αυτήν τη σύνθετη προβολή. Θα δούμε επίσης πώς τα :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: Δημιουργία «dept» πίνακα.

Παραγωγή

Δημιουργήθηκε πίνακας

Βήμα 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".

Παραγωγή

Διαδικασία PL/SQL ολοκληρωθεί

Βήμα 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.

Παραγωγή

Η προβολή δημιουργήθηκε

ΟΝΟΜΑ ΥΠΑΛΛΗΛΟΥ	DEPT_NAME	ΤΟΠΟΘΕΣΙΑ
Zzz	HR	ΗΠΑ
Εεε	SALES	UK
XXX	ΧΡΗΜΑΤΟΟΙΚΟΝΟΜΙΚΗ	ΙΑΠΩΝΙΑ

Βήμα 4) Ενημέρωση προβολής πριν από την ενεργοποίηση.

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

Επεξήγηση κώδικα

Γραμμή κώδικα 34-38: Ενημερώστε την τοποθεσία του "XXX" σε "FRANCE". Έθεσε την εξαίρεση επειδή η δηλώσεις DML δεν επιτρέπονται στην σύνθετη θέα.

Παραγωγή

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: Δημιουργία ΑΝΤΙ ΚΑΝΟΝΙΣΜΟΥ για το συμβάν "UPDATE" στην προβολή "guru99_emp_view" σε επίπεδο ROW. Περιέχει τη δήλωση ενημέρωσης για την ενημέρωση της τοποθεσίας στον βασικό πίνακα «τμήμα».
Κωδικός γραμμή 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". Είναι επιτυχής επειδή η ενεργοποίηση "ΑΝΤΙ ΓΙΑ" σταμάτησε την πραγματική δήλωση ενημέρωσης στην προβολή και πραγματοποίησε την ενημέρωση του βασικού πίνακα.
Κωδικός γραμμή 55: Επαλήθευση της ενημερωμένης εγγραφής.

Παραγωγή:

Η διαδικασία PL/SQL ολοκληρώθηκε με επιτυχία

ΟΝΟΜΑ ΥΠΑΛΛΗΛΟΥ	DEPT_NAME	ΤΟΠΟΘΕΣΙΑ
Zzz	HR	ΗΠΑ
Εεε	SALES	UK
XXX	ΧΡΗΜΑΤΟΟΙΚΟΝΟΜΙΚΗ	ΓΑΛΛΙΑ

Σύνθετη σκανδάλη

Η σκανδάλη Compound είναι μια σκανδάλη που σας επιτρέπει να καθορίσετε ενέργειες για καθένα από τα τέσσερα σημεία χρονισμού στο σώμα μιας σκανδάλης. Τα τέσσερα διαφορετικά σημεία χρονισμού που υποστηρίζει είναι τα παρακάτω.

ΠΡΙΝ ΔΗΛΩΣΗ – επίπεδο
ΠΡΙΝ ΣΕΙΡΑ – επίπεδο
ΜΕΤΑ ΤΗ ΣΕΙΡΑ – επίπεδο
ΜΕΤΑ ΤΗ ΔΗΛΩΣΗ – επίπεδο

Παρέχει τη δυνατότητα συνδυασμού των ενεργειών για διαφορετικό χρονισμό στην ίδια σκανδάλη.

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. Αυτό θα αλλάξει τον μισθό σε προεπιλεγμένη τιμή '5000' πριν εισαγάγει την εγγραφή στον πίνακα.
Κωδικός γραμμή 11-14: Εισαγάγετε την εγγραφή στον πίνακα "emp".
Κωδικός γραμμή 16: Επαλήθευση της εισαγόμενης εγγραφής.

Παραγωγή:

Δημιουργήθηκε έναυσμα

Η διαδικασία PL/SQL ολοκληρώθηκε με επιτυχία.

EMP_NAME	EMP_NO	ΜΙΣΘΟΣ	ΔΙΕΥΘΥΝΤΗΣ	DEPT_NO
CCC	1004	5000	AAA	30

Ενεργοποίηση και απενεργοποίηση ενεργοποίησης

Οι ενεργοποιήσεις μπορούν να ενεργοποιηθούν ή να απενεργοποιηθούν. Για να ενεργοποιήσετε ή να απενεργοποιήσετε τη σκανδάλη, πρέπει να δοθεί μια δήλωση ALTER (DDL) για την ενεργοποίηση που την απενεργοποιεί ή την ενεργοποιεί.

Παρακάτω είναι η σύνταξη για την ενεργοποίηση/απενεργοποίηση των καναλιών.

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

Επεξήγηση σύνταξης:

Η πρώτη σύνταξη δείχνει πώς να ενεργοποιήσετε/απενεργοποιήσετε τη μοναδική ενεργοποίηση.
Η δεύτερη δήλωση δείχνει πώς να ενεργοποιήσετε/απενεργοποιήσετε όλους τους κανόνες ετικέτας σε έναν συγκεκριμένο πίνακα.

Σύνοψη

Σε αυτό το κεφάλαιο, μάθαμε για τους ενεργοποιητές PL/SQL και τα πλεονεκτήματά τους. Επίσης, μάθαμε τις διαφορετικές ταξινομήσεις και συζητήσαμε ΑΝΤΙ ΓΙΑ ΕΚΚΙΝΗΣΗ και ΣΥΝΘΕΣΗ.

Μπορεί να σας αρέσει:

Τι είναι το Trigger στο PL/SQL;

Οφέλη από Triggers

Τύποι ενεργοποίησης σε Oracle

Πώς να δημιουργήσετε έναυσμα

ΣΧΕΤΙΚΑ ΑΡΘΡΑ

:ΝΕΟ και :ΠΑΛΙΟ Ρήτρα

ΑΝΤΙ ΣΚΑΝΔΙΣΤΗΣ

Σύνθετη σκανδάλη

Σύνοψη