PostgreSQL Trigger: crea, elenca e rilascia con l'esempio

Come viene utilizzato Trigger PostgreSQL?

Un trigger può essere contrassegnato con l'operatore FOR EACH ROW durante la sua creazione. Tale trigger verrà chiamato una volta per ogni riga modificata dall'operazione. Un trigger può anche essere contrassegnato con l'operatore FOR EACH STATEMENT durante la sua creazione. Questo trigger verrà eseguito solo una volta per un'operazione specifica.

PostgreSQL creare trigger

Per creare un trigger, utilizziamo la funzione CREATE TRIGGER. Ecco la sintassi della funzione:

CREATE TRIGGER trigger-name [BEFORE|AFTER|INSTEAD OF] event-name  
ON table-name  
[  
 -- Trigger logic  
];

Il trigger-name è il nome del trigger.

BEFORE, AFTER e INSTEAD OF sono parole chiave che determinano quando verrà richiamato il trigger.

Il nome evento è il nome dell'evento che causerà il richiamo del trigger. Questo può essere INSERT, AGGIORNA, ELIMINA, ecc.

Il nome-tabella è il nome della tabella su cui deve essere creato il trigger.

Se il trigger deve essere creato per un'operazione INSERT, dobbiamo aggiungere il parametro ON nome-colonna.

La seguente sintassi lo dimostra:

CREATE TRIGGER trigger-name AFTER INSERT ON column-name  
ON table-name  
[  
 -- Trigger logic
];

PostgreSQL Crea esempio di trigger

Utilizzeremo la tabella dei prezzi riportata di seguito:

Prezzo:

Creiamo un'altra tabella, Price_Audits, dove registreremo le modifiche apportate alla tabella Price:

CREATE TABLE Price_Audits (
   book_id INT NOT NULL,
    entry_date text NOT NULL
);

Ora possiamo definire una nuova funzione denominata auditfunc:

CREATE OR REPLACE FUNCTION auditfunc() RETURNS TRIGGER AS $my_table$
   BEGIN
      INSERT INTO Price_Audits(book_id, entry_date) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$my_table$ LANGUAGE plpgsql;

La funzione precedente inserirà un record nella tabella Price_Audits incluso il nuovo ID riga e l'ora di creazione del record.

Ora che abbiamo la funzione trigger, dovremmo associarla alla nostra tabella dei prezzi. Daremo al trigger il nome price_trigger. Prima che venga creato un nuovo record, la funzione di trigger verrà richiamata automaticamente per registrare le modifiche. Ecco l'innesco:

CREATE TRIGGER price_trigger AFTER INSERT ON Price
FOR EACH ROW EXECUTE PROCEDURE auditfunc();

Inseriamo un nuovo record nella tabella Prezzi:

INSERT INTO Price 
VALUES (3, 400);

Ora che abbiamo inserito un record nella tabella Price, dovremmo inserire un record anche nella tabella Price_Audit. Ciò sarà il risultato del trigger che abbiamo creato nella tabella dei prezzi. Controlliamo questo:

SELECT * FROM Price_Audits;

Verrà restituito quanto segue:

Il trigger ha funzionato correttamente.

Trigger dell'elenco Postgres

Tutti i trigger che crei in PostgreSQL sono memorizzati nella tabella pg_trigger. Per visualizzare l'elenco dei trigger presenti sul file banca dati, interroga la tabella eseguendo il comando SELECT come mostrato di seguito:

SELECT tgname FROM pg_trigger;

Questo restituisce quanto segue:

La colonna tgname della tabella pg_trigger indica il nome del trigger.

Trigger di rilascio di Postgres

Per rilasciare a PostgreSQL trigger, utilizziamo l'istruzione DROP TRIGGER con la seguente sintassi:

DROP TRIGGER [IF EXISTS] trigger-name 
ON table-name [ CASCADE | RESTRICT ];

Il parametro trigger-name indica il nome del trigger che deve essere eliminato.

Il nome della tabella indica il nome della tabella da cui deve essere eliminato il trigger.

La clausola IF EXISTS tenta di eliminare un trigger esistente. Se tenti di eliminare un trigger che non esiste senza utilizzare la clausola IF EXISTS, riceverai un errore.

L'opzione CASCADE ti aiuterà a rilasciare automaticamente tutti gli oggetti che dipendono dal trigger.

Se si utilizza l'opzione RESTRICT, il trigger non verrà eliminato se gli oggetti dipendono da esso.

Per esempio:

Per eliminare il trigger denominato example_trigger nella tabella Price, eseguiamo il seguente comando:

Per rilasciare il trigger denominato example_trigger nella tabella Company, eseguire il seguente comando:

DROP TRIGGER example_trigger IF EXISTS
ON Company;

Utilizzando pgAdmin

Ora vediamo come vengono eseguite tutte e tre le azioni utilizzando pgAdmin.

Come creare Trigger in PostgreSQL utilizzando pgAdmin

Ecco come puoi creare un trigger in Postgres utilizzando pgAdmin:

Passaggio 1) Accedi al tuo account pgAdmin

Apri pgAdmin e accedi al tuo account utilizzando le tue credenziali

Passaggio 2) Creare un database dimostrativo

1. Dalla barra di navigazione a sinistra, fare clic su Database.
2. Fare clic su Demo.

Passaggio 3) Digitare la query

Per creare la tabella Price_Audits, digita la query nell'editor:

CREATE TABLE Price_Audits (
   book_id INT NOT NULL,
    entry_date text NOT NULL
)

Passaggio 4) Eseguire la query

Fare clic sul pulsante Esegui

Passaggio 5) Eseguire il codice per auditfunc

Eseguire il seguente codice per definire la funzione auditfunc:

CREATE OR REPLACE FUNCTION auditfunc() RETURNS TRIGGER AS $my_table$
   BEGIN
      INSERT INTO Price_Audits(book_id, entry_date) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$my_table$ LANGUAGE plpgsql

Passaggio 6) Esegui il codice per creare il trigger

Eseguire il seguente codice per creare il trigger price_trigger:

CREATE TRIGGER price_trigger AFTER INSERT ON Price
FOR EACH ROW EXECUTE PROCEDURE auditfunc()

Passaggio 7) Inserisci un nuovo record

1. Eseguire il seguente comando per inserire un nuovo record nella tabella Prezzi:

   INSERT INTO Price
   VALUES (3, 400)

2. Eseguire il seguente comando per verificare se un record è stato inserito nella tabella Price_Audits:

   SELECT * FROM Price_Audits

   Questo dovrebbe restituire quanto segue:

Passaggio 8) Controllare il contenuto della tabella

Controlliamo il contenuto della tabella Price_Audits:

Elencare i trigger utilizzando pgAdmin

Passo 1) Esegui il seguente comando per controllare i trigger nel tuo database:

SELECT tgname FROM pg_trigger

Questo restituisce quanto segue:

Eliminazione dei trigger utilizzando pgAdmin

Per rilasciare il trigger denominato example_trigger nella tabella Company, eseguire il seguente comando:

DROP TRIGGER example_trigger IF EXISTS
ON Company

Sintesi

• A PostgreSQL trigger si riferisce a una funzione che viene attivata automaticamente quando si verifica un evento del database su un oggetto del database, ad esempio una tabella.
• Esempi di tali eventi del database includono INSERT, UPDATE, DELETE, ecc.
• Un trigger esiste solo durante la durata dell'oggetto database per il quale è stato creato.
• Se l'oggetto del database viene eliminato, verrà eliminato anche il trigger.
• PostgreSQL i trigger vengono creati utilizzando l'istruzione CREATE TRIGGER.
• Ogni trigger è associato a una funzione che indica cosa farà il trigger quando viene richiamato.

Scarica il database utilizzato in questo tutorial