Oracle Trigger PL/SQL: Invece di & Tipi composti

Cos'è Trigger in PL/SQL?

I TRIGGER vengono memorizzati PL / SQL programmi che vengono attivati ​​dal Oracle motore automaticamente quando Dichiarazioni DML Operazioni come inserimento, aggiornamento ed eliminazione vengono eseguite sulla tabella, oppure al verificarsi di determinati eventi. Il codice da eseguire in caso di trigger può essere definito in base alle esigenze. È possibile scegliere l'evento in corrispondenza del quale il trigger deve essere attivato e la tempistica di esecuzione. Lo scopo di un trigger è quello di mantenere l'integrità delle informazioni nel database.

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 memorizzazione delle informazioni sull'accesso alle tabelle
• Revisione
• Syncreplicazione cronica delle 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 sulla tempistica

• PRIMA del trigger: Si attiva prima che si verifichi l'evento specificato.
• DOPO l'attivazione: Si attiva dopo che si è verificato l'evento specificato.
• INVECE DI Trigger: Un tipo speciale. Ne saprai di più negli argomenti che seguono. (solo per DML)

Classificazione in base al livello

• Livello di ISTRUZIONE Trigger: Si attiva una sola volta per l'evento specificato.
• Livello di ROW: Attivatore: Si attiva per ogni record interessato dall'evento specificato. (solo per DML)

Classificazione basata sull'evento

• Attivazione DML: Si attiva quando viene specificato l'evento DML (INSERIMENTO/AGGIORNAMENTO/ELIMINAZIONE).
• Attivazione DDL: Si attiva quando viene specificato l'evento DDL (CREATE/ALTER).
• Attivazione DATABASE: Si attiva quando viene specificato l'evento del database (LOGON/LOGOFF/STARTUP/SHUTDOWN).

Ciascun trigger è quindi una combinazione dei parametri sopra indicati.

Come creare un trigger

Di seguito è riportata la sintassi per la creazione di un trigger. Lo screenshot seguente mostra questa sintassi di creazione del trigger in 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;

Spiegazione della sintassi:

• La sintassi precedente mostra le diverse istruzioni facoltative presenti nella creazione del trigger.
• PRIMA/DOPO specificherà gli orari dell'evento.
• INSERISCI/AGGIORNA/ACCEDI/CREA/ecc. specificherà l'evento per il quale deve essere attivato il trigger.
• La clausola ON specificherà l'oggetto su cui è valido l'evento sopra menzionato. Ad esempio, si tratterà del nome della tabella su cui può verificarsi l'evento DML nel caso di un trigger DML.
• Il comando "PER OGNI RIGA" specificherà il livello di attivazione della riga.
• La clausola WHEN specificherà la condizione aggiuntiva in cui il trigger deve attivarsi.
• La parte di dichiarazione, la parte di esecuzione e la parte di gestione delle eccezioni sono le stesse di quelle degli altri Blocchi PL/SQL. La parte dichiarativa e la la gestione delle eccezioni alcune parti sono facoltative.

Clausola :NEW e :OLD

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 di riga per contenere questi valori. Possiamo usare queste clausole per fare riferimento ai vecchi e ai nuovi valori all'interno del corpo del trigger.

• :NUOVO – Mantiene un nuovo valore per le colonne della tabella/vista di base durante l'esecuzione del trigger.
• :VECCHIO – Mantiene 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 specifica quale clausola è valida per ciascuna istruzione DML (INSERT/UPDATE/DELETE).

INVECE DI Trigger

Un trigger “INSTEAD OF” è un tipo speciale di trigger. Viene utilizzato solo nei trigger DML. È impiegato quando un evento DML sta per verificarsi su una vista complessa.

Consideriamo un esempio in cui una vista è composta da tre tabelle di base. Quando viene eseguito un evento DML su questa vista, essa risulterà non valida perché i dati provengono da tre tabelle diverse. In questo caso, si utilizza un trigger INSTEAD OF. Il trigger INSTEAD OF viene utilizzato per modificare direttamente le tabelle di base anziché la vista in risposta all'evento.

Esempio 1: In questo esempio, creeremo una vista complessa a partire da due tabelle di base, dove Tabella_1 è la tabella dei dipendenti e Tabella_2 è la tabella dei reparti.

Successivamente, vedremo come il trigger INSTEAD OF viene utilizzato per eseguire un UPDATE dei dettagli della posizione su questa vista complessa. Vedremo anche come :NEW e :OLD sono utili nei trigger. L'esempio viene svolto nei seguenti passaggi:

• Passaggio 1: Creazione delle tabelle 'emp' e 'dept' con le colonne appropriate
• Fase 2: Inserimento dei valori di esempio nelle tabelle
• Passaggio 3: Creazione di una vista per le tabelle create in precedenza
• Passaggio 4: Aggiornamento della vista prima del trigger INSTEAD OF
• Passaggio 5: Creazione del trigger INSTEAD OF
• Passaggio 6: Aggiornamento della vista dopo l'attivazione di INSTEAD OF

Passaggio 1) Creazione delle tabelle 'emp' e 'dept' con le colonne appropriate.

Lo screenshot qui sotto mostra la creazione delle tabelle base 'emp' e 'dept' in 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 Spiegazione

• Code righe 1-7: Creazione della tabella 'emp'.
• Code righe 8-12: Creazione della tabella 'dept'.

Produzione:

Table Created

Passo 2) Ora che abbiamo creato le tabelle, le popoleremo con valori di esempio.

Lo screenshot qui sotto mostra le righe di esempio inserite nelle tabelle 'dept' e '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 Spiegazione

• Code righe 13-19: Inserimento dei dati nella tabella 'dept'.
• Code righe 20-26: Inserimento dei dati nella tabella 'emp'.

Produzione:

PL/SQL procedure completed

Passo 3) Creazione di una vista per le tabelle create in precedenza.

Lo screenshot qui sotto mostra la creazione e la successiva interrogazione della vista complessa.

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 Spiegazione

• Code righe 27-32: Creazione della vista 'guru99_emp_view'.
• Code riga 33: Interrogazione su guru99_emp_view.

Produzione:

View created

Passo 4) Aggiornamento della vista prima del trigger INSTEAD OF.

Lo screenshot qui sotto mostra il tentativo di aggiornamento sulla vista complessa e l'errore risultante.

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

Code Spiegazione

• Code righe 34-38: Aggiornare la posizione di "XXX" a "FRANCIA". Si è verificata un'eccezione perché le istruzioni DML non sono consentite direttamente sulla vista complessa.

Produzione:

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

ORA-06512: at line 2

Passo 5) Per evitare l'errore riscontrato durante l'aggiornamento della vista nel passaggio precedente, in questo passaggio utilizzeremo un trigger "INSTEAD OF".

Lo screenshot qui sotto mostra la creazione del trigger 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 Spiegazione

• Code riga 39: Creazione del trigger INSTEAD OF per l'evento 'UPDATE' sulla vista 'guru99_emp_view' a livello di riga. Contiene l'istruzione di aggiornamento per aggiornare la posizione nella tabella base 'dept'.
• Code riga 44: L'istruzione di aggiornamento utilizza ':NEW' e ':OLD' per trovare il valore delle colonne prima e dopo l'aggiornamento.

Produzione:

Trigger Created

Passo 6) Aggiornamento della vista dopo il trigger INSTEAD OF. Ora l'errore non verrà più visualizzato, poiché il trigger "INSTEAD OF" gestirà l'operazione di aggiornamento di questa vista complessa. Quando il codice viene eseguito, la sede del dipendente XXX verrà aggiornata da "Giappone" a "Francia".

Lo screenshot qui sotto mostra l'aggiornamento riuscito tramite il trigger INSTEAD OF e la visualizzazione aggiornata.

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

SELECT * FROM guru99_emp_view;

Code Spiegazione:

• Code righe 49-53: Aggiornamento della posizione di "XXX" a "FRANCIA". L'operazione è andata a buon fine perché il trigger "INSTEAD OF" ha interrotto l'esecuzione effettiva dell'istruzione di aggiornamento sulla vista ed ha eseguito l'aggiornamento della tabella di base.
• Code riga 55: Verifica del record aggiornato.

Produzione:

PL/SQL procedure successfully completed

Trigger composto

Il grilletto composto è un grilletto che consente di specificare azioni per ciascuno dei quattro punti temporali in un unico corpo grilletto. I quattro diversi punti temporali supportati sono i seguenti.

• PRIMA DELLA DICHIARAZIONE – livello
• PRIMA DELLA FILA – livello
• DOPO LA FILA – livello
• DOPO LA DICHIARAZIONE – livello

Consente di combinare azioni con tempistiche diverse in un unico trigger.

La schermata seguente mostra la sintassi del trigger composto con le sue quattro sezioni temporali.

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;

Spiegazione della sintassi:

• La sintassi sopra riportata mostra la creazione di un trigger 'COMPOUND'.
• La sezione dichiarativa è comune a tutti i blocchi di esecuzione nel corpo del trigger.
• Questi quattro blocchi di temporizzazione possono essere in qualsiasi sequenza. Non è obbligatorio utilizzare tutti e quattro i blocchi di temporizzazione. Possiamo creare un trigger COMPOSTO solo per le temporizzazioni richieste.

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

Lo screenshot qui sotto mostra un esempio di trigger composto e il relativo output.

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 Spiegazione:

• Code righe 2-10: Creazione del trigger composto. Viene creato per la temporizzazione PRIMA del livello ROW per popolare lo stipendio con il valore predefinito 5000. Questo modificherà lo stipendio al valore predefinito '5000' prima di inserire il record nella tabella.
• Code righe 11-14: Inserisci il record nella tabella 'emp'.
• Code riga 16: Verifica del record inserito.

Produzione:

Trigger created

PL/SQL procedure successfully completed.

Abilitazione e disabilitazione dei trigger

I trigger possono essere abilitati o disabilitati. Per abilitare o disabilitare un trigger, è necessario specificare un'istruzione ALTER (DDL) per il trigger in questione.

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

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

Spiegazione della sintassi:

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

DOMANDE FREQUENTI