PostgreSQL 触发器

PostgreSQL 触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。

以下是关于 PostgreSQL 触发器几个比较重要的点：

• PostgreSQL 触发器可以在以下几种情况下触发：

• 在执行操作之前（在检查约束并尝试插入、更新或删除之前）。

• 在执行操作之后（在检查约束并插入、更新或删除完成之后）。

• 更新操作（在对一个视图进行插入、更新、删除时）。

• 触发器的 FOR EACH ROW 属性是可选的，如果选中，当操作修改时每行调用一次；相反，选中 FOR EACH STATEMENT，不管修改了多少行，每个语句标记的触发器执行一次。

• WHEN 子句和触发器操作在引用 NEW.column-name 和 OLD.column-name 表单插入、删除或更新时可以访问每一行元素。其中 column-name 是与触发器关联的表中的列的名称。

• 如果存在 WHEN 子句，PostgreSQL 语句只会执行 WHEN 子句成立的那一行，如果没有 WHEN 子句，PostgreSQL 语句会在每一行执行。

• BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。

• 要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 tablename，而不是 database.tablename。

• 当创建约束触发器时会指定约束选项。这与常规触发器相同，只是可以使用这种约束来调整触发器触发的时间。当约束触发器实现的约束被违反时，它将抛出异常。

语法

创建触发器时的基本语法如下：

CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
　-- Logica del trigger...
];

In questo caso, event_name può essere INSERT, DELETE e UPDATE delle operazioni di database sul tavolo menzionato table_name. Puoi scegliere di specificare FOR EACH ROW dopo il nome della tabella.

Di seguito è riportata la sintassi per creare un trigger su una o più colonne specifiche di una tabella durante un'operazione di aggiornamento:

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
　-- Logica del trigger...
];

Esempio online

Supponiamo una situazione in cui dobbiamo mantenere un esperimento di audit per ogni record inserito nella tabella COMPANY (se già esistente, verrà eliminata e ricreata):

w3codeboxdb=# CREATE TABLE COMPANY(
　　　ID INT PRIMARY KEY　NOT NULL,
　　　NAME　TEXT　NOT NULL,
　　　AGE　INT　NOT NULL,
　　　ADDRESS　CHAR(50),
　　　SALARY　REAL
);

Per mantenere l'esperimento di audit, creeremo una nuova tabella chiamata AUDIT. Ogni volta che una nuova voce viene inserita nella tabella COMPANY, il messaggio di log verrà inserito al suo interno:

w3codeboxdb=# CREATE TABLE AUDIT(
　　　EMP_ID INT NOT NULL,
　　　ENTRY_DATE TEXT NOT NULL
);

In questo caso, ID è l'ID del registro AUDIT, EMP_ID è l'ID proveniente dalla tabella COMPANY, DATE manterrà il timestamp del momento in cui il record è stato creato nella tabella COMPANY. Quindi, creiamo un trigger sulla tabella COMPANY come segue:

w3codeboxdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

auditlogfunc() è un programma PostgreSQL, la sua definizione è la seguente:

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
　　　BEGIN
　　　　　　INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
　　　　　　RETURN NEW;
　　　END;
$example_table$ LANGUAGE plpgsql;

Ora, iniziamo a inserire dati nella tabella COMPANY:

w3codeboxdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00);

In questo momento, è stata inserita una registrazione nella tabella COMPANY:

Allo stesso modo, è stata inserita una registrazione nella tabella AUDIT perché abbiamo creato un trigger durante l'inserimento nella tabella COMPANY. In modo simile, possiamo creare trigger su aggiornamenti e cancellazioni secondo necessità:

emp_id | entry_date
--------+-------------------------------
　　　　　　1 | 2013-05-05 15:49:59.968+05:30
(1 row)

Elenca i trigger

Puoi elencare tutti i trigger attualmente presenti nel database dalla tabella pg_trigger:

w3codeboxdb=# SELECT * FROM pg_trigger;

Se vuoi elencare i trigger di una tabella specifica, la sintassi è la seguente:

w3codeboxdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

Otteniamo i risultati di seguito:

　tgname
-----------------
　example_trigger
(1 row)

Eliminare il trigger

La sintassi di base per eliminare un trigger è la seguente:

drop trigger ${trigger_name} on ${table_of_trigger_dependent};

Il comando per eliminare il trigger example_trigger sul tavolo company di questo articolo è:

drop trigger example_trigger on company;