Oracle Tutorial sul trigger PL/SQL: invece di, composto [esempio]

Cos'è Trigger in PL/SQL?

TRIGGER sono programmi memorizzati che vengono attivati ​​da Oracle motore automaticamente quando istruzioni DML come insert, update, delete vengono eseguite sulla tabella o si verificano alcuni eventi. Il codice da eseguire in caso di trigger può essere definito secondo i requisiti. Puoi scegliere l'evento in base al quale deve essere attivato il trigger e il momento dell'esecuzione. Lo scopo del trigger è 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 su sincronizzazione
  • BEFORE Trigger: si attiva prima che si verifichi l'evento specificato.
  • AFTER 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 su livello
  • Trigger di livello STATEMENT: si attiva una volta per l'istruzione dell'evento specificata.
  • Trigger di livello RIGA: si attiva per ogni record interessato dall'evento specificato. (solo per DML)
  • Classificazione basata su 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 un trigger

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

creare trigger

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à i tempi dell'evento.
  • INSERISCI/AGGIORNA/ACCEDI/CREA/ecc. specificherà l'evento per il quale deve essere attivato il trigger.
  • La clausola ON specificherà su quale oggetto è valido l'evento sopra menzionato. Ad esempio, questo sarà il nome della tabella su cui potrebbe verificarsi l'evento DML nel caso del trigger DML.
  • Il comando "FOR EACH ROW" specificherà il trigger del livello ROW.
  • 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 uguali a quelle dell'altra Blocchi PL/SQL. La parte della dichiarazione e la parte della gestione delle eccezioni 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 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 – 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 specificherà quale clausola è valida per quale istruzione DML (INSERT/UPDATE/DELETE).

INSERT AGGIORNAMENTO DELETE
:NUOVO VALIDO VALIDO NON VALIDO. Non è presente alcun nuovo valore nel caso di eliminazione.
:VECCHIO NON VALIDO. Non è presente alcun valore precedente 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 un evento DML sta per verificarsi 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, questo non sarà più valido perché i dati vengono presi da 3 tabelle diverse. Quindi in questo caso 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 un determinato evento.

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

  • Table_1 è la tabella dip e
  • Table_2 è la tabella del dipartimento.

Poi vedremo come il trigger INSTEAD OF viene utilizzato per emettere l'istruzione UPDATE the location detail 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 valori di esempio
  • Passaggio 3: creazione della vista per la tabella creata sopra
  • Passaggio 4: aggiornamento della vista prima del trigger anziché
  • Passaggio 5: creazione del trigger anziché
  • Passaggio 6: aggiornamento della vista dopo il trigger anziché

Passo 1) Creazione della tabella "emp" e "dept" con le colonne appropriate

INVECE DI Trigger

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 'reparto'.

Uscita

Tabella creata

Passo 2) Ora che abbiamo creato la tabella, popoleremo questa tabella con valori di esempio e creazione di visualizzazioni per le tabelle sopra.

INVECE DI Trigger

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

Spiegazione del codice

  • Riga di codice 13-19: Inserimento dei dati nella tabella 'dip'.
  • Riga di codice 20-26: Inserimento dei dati nella tabella 'emp'.

Uscita

Procedura PL/SQL completato

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

INVECE DI Trigger

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;

Spiegazione del codice

  • Riga di codice 27-32: Creazione della vista 'guru99_emp_view'.
  • Riga di codice 33: Interrogazione su guru99_emp_view.

Uscita

Vista creata

NOME DIPENDENTE DEPT_NAME LOCATION
ZZZ HR USA
AAAA SALES UK
XXX FINANZIARIA GIAPPONE

Passo 4) Aggiornamento della vista prima del trigger.

INVECE DI Trigger

BEGIN
UPDATE 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é il Dichiarazioni DML non sono consentiti nella vista complessa.

Uscita

ORA-01779: impossibile modificare una colonna mappata a una tabella senza chiave conservata

ORA-06512: alla linea 2

Passo 5)Per evitare che si verifichino errori durante l'aggiornamento della vista nel passaggio precedente, in questo passaggio utilizzeremo "invece di trigger".

INVECE DI Trigger

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

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 l'istruzione update per aggiornare la posizione nella tabella di base "dept".
  • Riga di codice 44: L'istruzione di aggiornamento utilizza ":NEW" e ":OLD" per trovare il valore delle colonne prima e dopo l'aggiornamento.

Uscita

Trigger creato

Passo 6) Aggiornamento della vista dopo il trigger instead-of. Ora l'errore non si verificherà poiché "instead of trigger" gestirà l'operazione di aggiornamento di questa vista complessa. E quando il codice sarà stato eseguito, la posizione del dipendente XXX verrà aggiornata da "Giappone" a "Francia".

INVECE DI Trigger

BEGIN
UPDATE 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 della posizione di "XXX" in "FRANCIA". Ha esito positivo perché il trigger 'INSTEAD OF' ha interrotto l'effettiva istruzione di aggiornamento sulla visualizzazione ed 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 LOCATION
ZZZ HR USA
AAAA SALES UK
XXX FINANZIARIA FRANCIA

Trigger composto

Il trigger Compound è un trigger che consente di specificare azioni per ciascuno dei quattro punti temporali nel corpo del trigger singolo. 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

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

Trigger composto

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;

Spiegazione della sintassi:

  • La sintassi precedente mostra la creazione del trigger 'COMPOUND'.
  • La sezione dichiarativa è comune a tutto il blocco di esecuzione nel corpo del trigger.
  • Questi 4 blocchi temporali possono essere in qualsiasi sequenza. Non è obbligatorio avere tutti questi 4 blocchi temporali. 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.

Trigger composto

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;

Spiegazione del codice:

  • Riga di codice 2-10: Creazione del trigger composto. Viene creato per il livello BEFORE ROW- per popolare lo stipendio con il valore predefinito 5000. Ciò modificherà lo stipendio al valore predefinito "5000" prima di inserire il record nella tabella.
  • Riga di codice 11-14: inserisce 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 UFF_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 disabiliti o lo abiliti.

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 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 DEL trigger e del trigger COMPOUND.