背景

我有一個包含幾十個表的數據庫，其中一些應該有審計列。

問題

我想避免手動創建審計列的苦差事，並編寫一次update和delete触發器。

程式碼

有幾段不同的程式碼必須放在一起。

審計表

以下程式碼創建一個審計表和城市表：

CREATE TABLE audit (
 created timestamp with time zone NOT NULL DEFAULT ('now'::text)::timestamp with time zone,
 deleted timestamp with time zone,
 updated timestamp with time zone
);

範例表

一個簡單的表格作為問題的範例：

CREATE TABLE city (
 name       text,
 population real,
 altitude   int
);

父表觸發器

以下程式碼在審計表上創建數據庫觸發器和刪除預防規則：

CREATE FUNCTION audit_delete() RETURNS trigger AS
$audit_delete$
BEGIN
 OLD.deleted := current_timestamp;
 RETURN NULL;
END;
$audit_delete$
LANGUAGE plpgsql;

CREATE TRIGGER audit_delete BEFORE DELETE ON audit
   FOR EACH ROW EXECUTE PROCEDURE audit_delete();

添加列

繼承要求子表中的列與父表匹配，並且必須手動創建。如：

for $name in (SELECT list of tables); do
 ALTER TABLE $name ADD COLUMN
   created timestamp with time zone NOT NULL DEFAULT ('now'::text)::timestamp with time zone;
 ALTER TABLE $name ADD COLUMN
   deleted timestamp with time zone;
 ALTER TABLE $name ADD COLUMN
   updated timestamp with time zone;
done

可能有一種方法可以使用資訊模式檢測和自動添加缺失的列。

建立繼承層次結構

以下程式碼將繼承應用於給定表：

ALTER TABLE city INHERIT audit;

插入數據

在數據庫中添加一些數據：

INSERT INTO city ('Vancouver', 603502, 10 );
INSERT INTO city ('Seattle', 620887, 10 );
INSERT INTO city ('La Rinconada', 30000, 5100 );
INSERT INTO city ('Jericho', 20300, -260 );

刪除數據

刪除一些數據：

DELETE FROM city WHERE name = 'Jericho';

此時，我希望該deleted列包含“Jericho”值的日期。相反，該行實際上已從數據庫中刪除。

的觸發器audit_delete()沒有觸發。

問題

1. 什麼是創建觸發器的最佳（或通常）方法，該觸發器delete為從審計表繼承的所有表的查詢設置刪除日期（使用 PostgreSQL 9.2）？
2. （可選）這對觸發器有用嗎？

謝謝！

相關連結

• http://projects.nocternity.net/index.py/en/psql-inheritance
• http://www.postgresql.org/docs/current/static/tutorial-inheritance.html
• https://stackoverflow.com/questions/8469116/how-can-i-make-a-trigger-to-use-inheritance-with-that-tables
• http://tapoueh.org/blog/2010/11/24-dynamic-triggers-in-plpgsql.html
• http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
• http://archives.postgresql.org/pgsql-general/2009-08/msg00026.php
• http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html

從http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html：

DELETE 和 UPDATE 觸發器需要在子表上。對子項的操作不會觸發父項的觸發器。

因此，還必須執行以下程式碼：

CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
   FOR EACH ROW EXECUTE PROCEDURE audit_delete();

該audit_delete()功能不會像問題中所寫的那樣工作：無法使用OLD.deleted = current_timestamp. 相反，假設表的主鍵欄位為id：

CREATE OR REPLACE FUNCTION audit_delete()
 RETURNS trigger AS
$BODY$
BEGIN
 EXECUTE
   'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
 RETURN NULL;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 1;

引用自：https://dba.stackexchange.com/questions/30069