Oracle PL / SQL Trigger Tutorial: Invece di, Compound [Example]

In questo tutorial, imparerete-

• i Vantaggi di Trigger
• Tipi di Trigger in Oracle
• Come Creare Trigger
• :NUOVO e :VECCHIA Clausola
• INVECE DI Trigger
• Composto Trigger

Benefici di Trigger

di Seguito sono i vantaggi di trigger.

• la Generazione di alcuni derivati valori di colonna automaticamente
• Applicare l’integrità referenziale
• Eventi, la registrazione e la memorizzazione di informazioni sull’accesso alla tabella
• Controllo
• la replica Sincrona di tabelle
• Imporre le autorizzazioni di protezione
• la Prevenzione non valido transazioni

Tipi di Trigger in Oracle

i Trigger possono essere classificati in base ai seguenti parametri.

• Classificazione basata sulla tempistica
  • PRIMA del Trigger: si attiva prima che si verifichi l’evento specificato.
  • DOPO IL trigger: si attiva dopo che si è verificato l’evento specificato.
  • INVECE DI Trigger: Un tipo speciale. Imparerai di più sugli ulteriori argomenti. (solo per DML )
• Classificazione in base al livello
  • Livello di ISTRUZIONE Trigger: si attiva una volta per l’istruzione evento specificato.
  • Trigger a livello di RIGA: si attiva per ogni record che è stato interessato nell’evento specificato. (solo per DML)
• Classificazione basata sull’evento
  • DML Trigger: si attiva quando viene specificato l’evento DML (INSERT/UPDATE/DELETE)
  • DDL Trigger: Si attiva quando viene specificato l’evento DDL (CREATE / ALTER)
  • Trigger DEL DATABASE: Si attiva quando viene specificato l’evento del database (LOGON/LOGOFF/STARTUP/SHUTDOWN)

Quindi ogni trigger è la combinazione dei parametri precedenti.

Come creare Trigger

Di seguito è riportata la sintassi per la creazione di un trigger.

CREATE TRIGGER <trigger_name> ON<name of underlying object> DECLARE<Declaration part>BEGIN<Execution part> EXCEPTION<Exception handling part> END;

Spiegazione della sintassi:

• La sintassi precedente mostra le diverse istruzioni opzionali presenti nella creazione del trigger.
• PRIMA / DOPO specificherà i tempi dell’evento.
• INSERISCI / AGGIORNA / ACCEDI / CREA / ecc. specificherà l’evento per il quale il trigger deve essere attivato.
• La clausola ON specificherà su quale oggetto l’evento sopra menzionato è valido. Ad esempio, questo sarà il nome della tabella su cui può verificarsi l’evento DML nel caso di Trigger DML.
• Comando” PER OGNI RIGA ” specificherà il trigger livello RIGA.
• QUANDO clausola specificherà la condizione aggiuntiva in cui il trigger deve sparare.
• La parte di dichiarazione, parte di esecuzione, parte di gestione delle eccezioni è uguale a quella degli altri blocchi PL/SQL. La parte di dichiarazione e la parte di gestione delle eccezioni sono facoltative.

:Clausola NEW e :OLD

In un trigger di livello di riga, il trigger viene attivato per ogni riga correlata. E a volte è necessario conoscere il valore prima e dopo l’istruzione DML.

Oracle ha fornito due clausole nel trigger a livello di RECORD per contenere questi valori. Possiamo usare queste clausole per fare riferimento ai valori vecchi e nuovi all’interno del corpo del trigger.

• :NEW-Contiene un nuovo valore per le colonne della tabella / vista di base durante l’esecuzione del trigger
• :OLD – Contiene il vecchio valore delle colonne della tabella/vista di base durante l’esecuzione del trigger

Questa clausola dovrebbe essere utilizzata in base all’evento DML. La tabella seguente specificherà quale clausola è valida per quale istruzione DML (INSERT/UPDATE/DELETE).

	INSERISCI	AGGIORNAMENTO	ELIMINA
:NUOVO	VALIDO	VALIDO	non VALIDO. Non c’è un nuovo valore in delete case.
:VECCHIO	NON VALIDO. Non c’è nessun vecchio valore in caso di inserimento	VALIDO	VALIDO

INVECE DI Trigger

“INVECE DI trigger” è il tipo speciale di trigger. Viene utilizzato solo nei trigger DML. Viene utilizzato quando si verificherà un evento DML nella vista complessa.

Considera un esempio in cui una vista è composta da 3 tabelle di base. Quando viene emesso un evento DML su questa vista, ciò non sarà valido perché i dati vengono presi da 3 tabelle diverse. Quindi in questo INVECE DI trigger viene utilizzato. Il trigger INVECE DI viene utilizzato per modificare direttamente le tabelle di base invece di modificare la vista per l’evento specificato.

Esempio 1: In questo esempio, creeremo una vista complessa da due tabelle di base.

• Table_1 è una tabella emp e

• Table_2 è una tabella di reparto.

Quindi vedremo come viene utilizzato il trigger INVECE di aggiornare l’istruzione di dettaglio della posizione su questa vista complessa. Vedremo anche come: NEW e: OLD sono utili nei trigger.

• Fase 1: Creare la tabella ” emp ” e ‘rep’ con colonne appropriate
• Step 2: Compilazione della tabella con i valori di esempio
• Passo 3: Creazione di una visualizzazione per la superiore create table
• Passo 4: Aggiornamento di vista prima, invece di trigger
• Passo 5: Creazione di invece di trigger
• Passo 6: Aggiornamento di vista dopo invece di trigger

Passo 1) Creare la tabella ” emp ” e ‘rep’ con colonne appropriate

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

Spiegazione del Codice

• linea di Codice 1-7: Creazione tabella ‘emp’.
• Linea di codice 8-12: creazione della tabella ‘dept’.

Output

Tabella creata

Passaggio 2) Ora da quando abbiamo creato la tabella, popoleremo questa tabella con valori di esempio e Creazione di viste per le tabelle precedenti.

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

Spiegazione del codice

• Linea di codice 13-19: Inserimento dei dati nella tabella ‘dept’.
• Linea di codice 20-26: Inserimento dei dati nella tabella ‘emp’.

Output

PL / SQL procedura completata

Passaggio 3) Creazione di una vista per la tabella creata sopra.

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;

Spiegazione del Codice

• linea di Codice 27-32: Creazione di ‘guru99_emp_view’ vista.

• Linea di codice 33: Interrogazione guru99_emp_view.

Output

View created

EMPLOYEE_NAME	DEPT_NAME	LOCATION
ZZZ	HR	USA
YYY	SALES	UK
XXX	FINANCIAL	JAPAN

Step 4) Update of view before instead-of trigger.

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

Code Explanation

• Code line 34-38: Update the location of “XXX” to ‘FRANCE’. Ha sollevato l’eccezione perché le istruzioni DML non sono consentite nella vista complessa.

Output

ORA-01779: impossibile modificare una colonna che si associa a una tabella non conservata

ORA-06512: alla riga 2

Passo 5) Per evitare l’errore durante l’aggiornamento della vista nel passaggio precedente, in questo passaggio useremo “invece di trigger.”

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

Spiegazione del codice

• Linea di codice 39: Creazione di INVECE DI trigger per l’evento ‘UPDATE’ nella vista ‘guru99_emp_view’ a livello di RIGA. Contiene l’istruzione update per aggiornare la posizione nella tabella di base ‘dept’.
• Riga di codice 44: L’istruzione Update utilizza ‘: NEW ‘e’: OLD ‘ per trovare il valore delle colonne prima e dopo l’aggiornamento.

Uscita

Trigger Creato

Passo 6) Aggiornamento della vista dopo invece-di trigger. Ora l’errore non verrà come il “invece di trigger” gestirà l’operazione di aggiornamento di questa vista complessa. E quando il codice ha eseguito la posizione del dipendente XXX verrà aggiornato a ” Francia “da” Giappone.”

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

SELECT * FROM guru99_emp_view;

Spiegazione di Codice:

• linea di Codice 49-53: Aggiornamento della posizione di “XXX” per “la FRANCIA”. Ha successo perché il trigger ‘INVECE DI’ ha interrotto l’istruzione update effettiva in vista ed eseguito l’aggiornamento della tabella di base.
• Linea di codice 55: Verifica del record aggiornato.

Uscita:

di procedure PL/SQL completato con successo

EMPLOYEE_NAME	DEPT_NAME	POSIZIONE
ZZZ	HR	USA
YYY	VENDITA	regno UNITO
XXX	FINANZIARIA	FRANCIA

Composto Trigger

Il Composto trigger è un trigger che permette di specificare le azioni per ciascuno dei quattro punti di cronometraggio nel singolo trigger corpo. I quattro diversi punti di temporizzazione che supporta è come di seguito.

• PRIMA del livello di ISTRUZIONE
• PRIMA del livello di RIGA
• DOPO il livello di RIGA
• DOPO il livello di ISTRUZIONE

Fornisce la possibilità di combinare le azioni per diversi tempi nello stesso trigger.

CREATE TRIGGER <trigger_name> FORON <name of underlying object><Declarative part>‭ ‬BEFORE STATEMENT ISBEGIN<Execution part>;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN<Execution part>;END EACH ROW;AFTER EACH ROW ISBEGIN<Execution part>;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN<Execution part>;END AFTER STATEMENT;END;

Spiegazione della sintassi:

• La sintassi precedente mostra la creazione di trigger ‘COMPOSTI’.
• La sezione dichiarativa è comune per tutto il blocco di esecuzione nel corpo del trigger.
• Questi 4 blocchi di temporizzazione possono essere in qualsiasi sequenza. Non è obbligatorio avere tutti questi 4 blocchi di temporizzazione. Possiamo creare un trigger COMPOSTO solo per i tempi richiesti.

Esempio 1: In questo esempio, creeremo un trigger per compilare automaticamente la colonna dello stipendio con il valore predefinito 5000.

CREATE TRIGGER emp_trig FOR INSERT ON empCOMPOUND TRIGGER BEFORE EACH ROW IS BEGIN: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;

Spiegazione di Codice:

• linea di Codice 2-10: Creazione di composto di trigger. Viene creato per il timing PRIMA del livello di RIGA per popolare lo stipendio con il valore predefinito 5000. Questo cambierà lo stipendio al valore predefinito ‘5000’ prima di inserire il record nella tabella.
• Linea di codice 11-14: Inserire il record nella tabella ‘emp’.
• Linea di codice 16: Verifica del record inserito.

Output:

Trigger creato

PL / SQL procedura completata con successo.

EMP_NAME	EMP_NO	STIPENDIO	MANAGER	DEPT_NO
CCC	1004	5000	AAA	30

Attivazione e Disattivazione dei Trigger

Trigger può essere attivata o disattivata. Per abilitare o disabilitare il trigger, è necessario fornire un’istruzione ALTER (DDL) per il trigger che lo disabilita o lo abilita.

Di seguito è riportata la sintassi per abilitare / disabilitare i trigger.

ALTER TRIGGER <trigger_name> ;ALTER TABLE <table_name> ALL TRIGGERS;

Spiegazione della sintassi:

• La prima sintassi mostra come abilitare / disabilitare il singolo trigger.
• La seconda istruzione mostra come abilitare / disabilitare tutti i trigger su una particolare tabella.

Sommario

In questo capitolo, abbiamo imparato a conoscere i trigger PL / SQL e i loro vantaggi. Abbiamo anche imparato le diverse classificazioni e discusso INVECE DI trigger e trigger COMPOSTO.