Esercitazione sui trigger Oracle PL / SQL: invece di, composto (esempio)

Cos'è Trigger in PL / SQL?

I TRIGGER sono programmi memorizzati che vengono attivati automaticamente dal motore Oracle quando istruzioni DML come inserimento, aggiornamento, eliminazione vengono eseguite sulla tabella o si verificano alcuni eventi. Il codice da eseguire in caso di trigger può essere definito secondo il requisito. Puoi scegliere l'evento in base al quale il trigger deve essere attivato e la tempistica dell'esecuzione. Lo scopo del trigger è mantenere l'integrità delle informazioni sul database.

In questo tutorial imparerai-

Vantaggi dei trigger
Tipi di trigger in Oracle
Come creare trigger
: NUOVO e: VECCHIO clausola
INVECE DI Trigger
Trigger composto

Vantaggi dei trigger

Di seguito sono riportati i vantaggi dei trigger.

Generazione automatica di alcuni valori di colonna derivati
Applicazione dell'integrità referenziale
Registrazione degli eventi e archiviazione delle informazioni sull'accesso alla tabella
Auditing
Replica sincrona di tabelle
Imporre autorizzazioni di sicurezza
Prevenire transazioni non valide

Tipi di trigger in Oracle

I trigger possono essere classificati in base ai seguenti parametri.

Classificazione basata sui tempi
- BEFORE Trigger: viene attivato prima che si sia verificato l'evento specificato.
- DOPO 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 basata sul livello
- Livello STATEMENT Trigger: viene attivato una volta per l'istruzione dell'evento specificato.
- Trigger di livello ROW: si attiva per ogni record che è stato influenzato dall'evento specificato. (solo per DML)
Classificazione basata sull'evento
- Trigger DML: si attiva quando viene specificato l'evento DML (INSERT / UPDATE / DELETE)
- Trigger DDL: si attiva quando viene specificato l'evento DDL (CREATE / ALTER)
- Trigger DATABASE: si attiva quando viene specificato l'evento del database (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Quindi ogni trigger è la combinazione dei parametri di cui sopra.

Come creare trigger

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

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

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 è valido l'evento di cui sopra. Ad esempio, questo sarà il nome della tabella su cui potrebbe verificarsi l'evento DML nel caso di trigger DML.
Il comando "FOR EACH ROW" specificherà il trigger di livello ROW.
La clausola WHEN specificherà la condizione aggiuntiva in cui il trigger deve attivarsi.
La parte di dichiarazione, parte di esecuzione, parte di gestione delle eccezioni è uguale a quella degli altri blocchi PL / SQL. La parte della dichiarazione e la parte di gestione delle eccezioni sono facoltative.

: NUOVO e: VECCHIO clausola

In un trigger a 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 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 - Conserva il vecchio valore delle colonne della tabella / vista di base durante l'esecuzione del trigger

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

	INSERIRE	AGGIORNARE	ELIMINA
:NUOVO	VALIDO	VALIDO	NON VALIDO. Non c'è un nuovo valore nel caso di cancellazione.
:VECCHIO	NON VALIDO. Non è presente alcun vecchio valore nel caso di inserimento	VALIDO	VALIDO

INVECE DI Trigger

"INSTEAD OF trigger" è il tipo speciale di trigger. Viene utilizzato solo nei trigger DML. Viene utilizzato quando si verifica un evento DML nella vista complessa.

Considera un esempio in cui una vista è composta da 3 tabelle di base. Quando un evento DML viene emesso su questa visualizzazione, non sarà più valido perché i dati vengono presi da 3 diverse tabelle. Quindi in questo trigger viene utilizzato il trigger INSTEAD OF. Il trigger INSTEAD OF 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 è la tabella emp e

Table_2 è la tabella del reparto.

Quindi vedremo come il trigger INSTEAD OF viene utilizzato per emettere UPDATE l'istruzione dei dettagli della posizione su questa vista complessa. Vedremo anche come: NEW e: OLD sono utili nei trigger.

Passaggio 1: creazione della tabella "emp" e "dept" con le colonne appropriate
Passaggio 2: compilare la tabella con i valori di esempio
Passaggio 3: creazione della vista per la tabella creata sopra
Passaggio 4: aggiornamento della visualizzazione prima del trigger anziché
Passaggio 5: creazione del trigger invece di
Passaggio 6: aggiornamento della visualizzazione dopo invece del trigger

Passaggio 1) Creazione della tabella "emp" e "dept" con le 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

Riga di codice 1-7 : creazione della tabella "emp".
Riga di codice 8-12 : creazione della tabella "dept".

Produzione

Tabella creata

Passaggio 2) Ora, poiché 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

Riga di codice 13-19 : Inserimento di dati nella tabella "dept".
Riga di codice 20-26: Inserimento di dati nella tabella "emp".

Produzione

Procedura PL / SQL 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

Riga di codice 27-32: creazione della vista "guru99_emp_view".

Riga di codice 33: query guru99_emp_view.

Produzione

Visualizza creato

NOME DIPENDENTE	DEPT_NAME	POSIZIONE
ZZZ	HR	Stati Uniti d'America
YYY	I SALDI	UK
XXX	FINANZIARIO	GIAPPONE

Passaggio 4) Aggiornamento della vista prima invece del trigger.

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

Spiegazione del codice

Riga di codice 34-38: aggiorna la posizione di "XXX" in "FRANCIA". Ha sollevato l'eccezione perché le istruzioni DML non sono consentite nella vista complessa.

Produzione

ORA-01779: impossibile modificare una colonna che associa a una tabella senza chiave

ORA-06512: alla riga 2

Passaggio 5) Per evitare l'errore durante l'aggiornamento della visualizzazione nel passaggio precedente, in questo passaggio utilizzeremo "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

Riga di codice 39: creazione del trigger INSTEAD OF per l'evento "UPDATE" nella vista "guru99_emp_view" a livello ROW. Contiene la dichiarazione di aggiornamento 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.

Produzione

Trigger creato

Passaggio 6) Aggiornamento della visualizzazione dopo invece di trigger. Ora l'errore non verrà dato che "invece di trigger" gestirà l'operazione di aggiornamento di questa vista complessa. E quando il codice è stato eseguito, la posizione del dipendente XXX verrà aggiornata in "Francia" da "Giappone".

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

SELECT * FROM guru99_emp_view;

Spiegazione del codice:

Riga di codice 49-53: aggiornamento dell'ubicazione di "XXX" in "FRANCE". Ha esito positivo perché il trigger "INSTEAD OF" ha interrotto l'istruzione di aggiornamento effettiva in visualizzazione ed ha eseguito l'aggiornamento della tabella di base.
Riga di codice 55: verifica del record aggiornato.

Produzione:

Procedura PL / SQL completata con successo

NOME DIPENDENTE	DEPT_NAME	POSIZIONE
ZZZ	HR	Stati Uniti d'America
YYY	I SALDI	UK
XXX	FINANZIARIO	FRANCIA

Trigger composto

Il trigger composto è un trigger che consente di specificare azioni per ciascuno dei quattro punti di temporizzazione nel corpo del singolo trigger. I quattro diversi punti di temporizzazione che supporta sono i seguenti.

PRIMA DELLA DICHIARAZIONE - livello
PRIMA DELLA RIGA - livello
DOPO RIGA - livello
DOPO LA DICHIARAZIONE - livello

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

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;

Spiegazione della sintassi:

La sintassi precedente mostra la creazione del trigger "COMPOUND".
La sezione dichiarativa è comune a tutti i blocchi 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 COMPOUND 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_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;

Spiegazione del codice:

Riga di codice 2-10 : creazione del trigger composto. Viene creato per il tempo PRIMA DELLA RIGA- livello per popolare lo stipendio con il valore predefinito 5000. Ciò cambierà lo stipendio al valore predefinito "5000" prima di inserire il record nella tabella.
Riga di codice 11-14 : inserire il record nella tabella "emp".
Riga di codice 16 : verifica del record inserito.

Produzione:

Trigger creato

Procedura PL / SQL completata con successo.

EMP_NAME	EMP_NO	STIPENDIO	MANAGER	DEPT_NO
CCC	1004	5000	AAA	30

Abilitazione e disabilitazione dei trigger

I trigger possono essere abilitati o disabilitati. 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 
 [ENABLE|DISABLE];ALTER TABLE 
 [ENABLE|DISABLE] 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 trigger INSTEAD OF e trigger COMPOUND.