Postgresql 简明教程
PostgreSQL - TRIGGERS
PostgreSQL Triggers 是数据库回调函数,在指定的数据库事件发生时自动执行/调用。
以下是有关 PostgreSQL 触发器的要点 -
-
可以指定 PostgreSQL 触发器在对行执行操作之前触发(在检查约束并尝试 INSERT、UPDATE 或 DELETE 执行之前),在操作完成后(在检查约束并完成 INSERT、UPDATE 或 DELETE 执行之后),或代替操作(在对视图执行插入、更新或删除操作的情况下)。
-
标记为 FOR EACH ROW 的触发器对操作修改的每一行调用一次。与此相反,标记为 FOR EACH STATEMENT 的触发器仅对任何给定的操作执行一次,而不管修改多少行。
-
WHEN 子句和触发器操作都可以使用 NEW.column-name 和 OLD.column-name 形式的引用访问正在插入、删除或更新的行中的元素,其中 column-name 是触发器所关联的表中的列名称。
-
如果提供了 WHEN 子句,则仅针对 WHEN 子句为 true 的行执行指定的 PostgreSQL 语句。如果没有提供 WHEN 子句,则为所有行执行 PostgreSQL 语句。
-
如果为同一事件定义了同种类型的多个触发器,它们将按照名称的字母顺序执行。
-
BEFORE、AFTER 或 INSTEAD OF 关键字确定触发器操作相对于关联行的插入、修改或删除的执行时间。
-
当与其关联的表被删除时,触发器会自动被删除。
-
要修改的表必须与触发器所附加的表或视图存在于同一数据库中,并且必须仅使用 tablename ,而不是 database.tablename 。
-
当指定 CONSTRAINT 选项时,将创建约束触发器。这与常规触发器相同,不同之处在于可以使用 SET CONSTRAINTS 调整触发器触发的时机。当实施的约束遭到破坏时,期望约束触发器引发异常。
Syntax
创建 trigger 的基本语法如下 -
CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
-- Trigger logic goes here....
];
在此处, event_name 可以是对 table_name 表执行的 INSERT、DELETE、UPDATE 和 TRUNCATE 数据库操作。您也可以在表名称后指定 FOR EACH ROW。
以下是针对表格的指定列上的 UPDATE 操作创建触发器的语法 -
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
-- Trigger logic goes here....
];
Example
让我们考虑一个情况,我们希望保留每次插入 COMPANY 表(我们稍后将按以下方式新建该表)中的每条记录的审计跟踪(如果您已经拥有它,请删除 COMPANY 表)。
testdb=# CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
要保留审计跟踪,我们将创建一个名为 AUDIT 的新表,每当 COMPANY 表中有新记录的条目时,日志消息都会插入到其中 −
testdb=# CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);
此处,ID 是 AUDIT 记录 ID,EMP_ID 是 ID,它将来自 COMPANY 表,DATE 将保留在 COMPANY 表中创建记录时的时间戳。现在,让我们在 COMPANY 表上创建一个触发器,如下所示 −
testdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
其中 auditlogfunc() 是 PostgreSQL procedure ,并具有以下定义 −
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;
现在,我们将开始实际的工作。让我们开始在 COMPANY 表中插入记录,这将导致在 AUDIT 表中创建审计日志记录。因此,让我们在 COMPANY 表中创建一个记录,如下所示 −
testdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
这将在 COMPANY 表中创建一个如下所示的记录 −
id | name | age | address | salary
----+------+-----+--------------+--------
1 | Paul | 32 | California | 20000
同时,将在 AUDIT 表中创建一个记录。此记录是触发器的结果,我们已在 COMPANY 表上的 INSERT 操作中创建了该记录。同样,您可以根据您的要求创建 UPDATE 和 DELETE 操作的触发器。
emp_id | entry_date
--------+-------------------------------
1 | 2013-05-05 15:49:59.968+05:30
(1 row)
Listing TRIGGERS
您可以从 pg_trigger 表中列出当前数据库中的所有触发器,如下所示 −
testdb=# SELECT * FROM pg_trigger;
上述 PostgreSQL 语句将列出所有触发器。
如果您想列出特定表上的触发器,请使用 AND 子句和表名,如下所示 −
testdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';
上述 PostgreSQL 语句也只会列出一个条目,如下所示 −
tgname
-----------------
example_trigger
(1 row)