postgresql 触发器知识点_create or replace function returns trigger as

1、审核触发器

触发器最常见的用途之一是采用前后一致且透明的方式向表中记录数据的变化。当创建一个审核触发器时，首先我们必须决定我们要记录的内容。

被记录的事件的逻辑为：谁改变了数据，数据什么时候被改变了，什么操作改变了数据。这些信息可以用下面的表进行保存：

CREATE TABLE audit_log(
                username text, -- who did the change
                event_time_utc timestamp,  --when the event was recorded
                table_name text,  -- contains schema-qualified table name
                operation text,  -- insert, update, delete or truncate
                before_value json,  -- the OLD tuple value
                after_value json);  -- the NEW tuple value

接下来编写触发器函数：

CREATE or REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
DECLARE
    old_row json := NULL;
    new_row json := NULL;
BEGIN
        IF TG_OP in ('UPDATE', 'DELETE') THEN
            old_row = row_to_json(OLD);
        END IF;
        IF TG_OP in ('INSERT', 'UPDATE') THEN
            new_row = row_to_json(NEW);
        END IF;
        INSERT INTO audit_log(username,event_time_utc,table_name,operation,before_value,after_value
        )VALUES (
        session_user,
        current_timestamp AT TIME ZONE 'UTC',
        TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME,
        TG_OP,
        old_row,
        new_row);
        RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建notify_test表

CREATE TABLE notify_test(i int)

函数编写完，准备定义一个新的日志触发器：

CREATE TRIGGER audit_log AFTER INSERT OR UPDATE or DELETE ON notify_test FOR EACH ROW EXECUTE PROCEDURE audit_trigger();

测试：

TRUNCATE notify_test;
 
INSERT INTO notify_test VALUES (1);
 
UPDATE notify_test SET i=2;
 
DELETE FROM notify_test;
 
SELECT * from audit_log;

2、无效的delete

如果我们的业务需求是数据只能在一些表中被添加和修改，但不能被删除，其中一种处理方法是从所有用户处撤销对这些表的delete操作（记得同时要从public处撤销delete），但是这也可以借助触发器来实现。

一个普通的取消触发器可以写成如下代码：

CREATE or REPLACE FUNCTION cancel_op()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_WHEN = 'AFTER' THEN
        RAISE EXCEPTION 'you are not allowed to % rows in %.%',
                TG_OP, TG_TABLE_SCHEMA, TG_TABLE_NAME;
    END IF;
    raise notice '% on rows in %.% won''T HAPPEN',
                TG_OP, TG_TABLE_SCHEMA, TG_TABLE_NAME;
     RETURN NULL;
END;
$$ LANGUAGE plpgsql;

对于before与after触发器，我们可以使用相同的触发器函数。如果你将其当作before触发器使用，那么操作会跳过一个消息，但如果你将其当作after触发器，那么就会引发错误，而且当前事务（子事务）就会回滚。

CREATE TABLE delete_test1(i int);
 
INSERT INTO delete_test1 VALUES(1);
 
CREATE TRIGGER disallow_delete AFTER DELETE on delete_test1 for EACH ROW EXECUTE PROCEDURE cancel_op();
 
DELETE from delete_test1 WHERE i =1;
 
[SQL]DELETE from delete_test1 WHERE i =1;
[Err] ERROR:  you are not allowed to DELETE rows in public.delete_test1
CONTEXT:  PL/pgSQL function cancel_op() line 4 at RAISE
（这里注意到after触发器抛出了一个错误信息）
 
CREATE TRIGGER skip_delete BEFORE DELETE on delete_test1 for EACH ROW EXECUTE PROCEDURE cancel_op();
 
DELETE from delete_test1 WHERE i =1;
 
[SQL]DELETE from delete_test1 WHERE i =1;
NOTICE:  DELETE on rows in public.delete_test1 won'T HAPPEN
CONTEXT:  PL/pgSQL function cancel_op() line 7 at RAISE

这一次，BEFORE触发器取消了DELETE和AFTER触发器，虽然任然在那里，但并未达到。

3、无效的TRUNCATE

你可能已经发现，如果你使用TRUNCATE来删除所有东西，之前的触发器就很容易被DELETE忽略掉。

尽管通过返回NULL（这个仅仅对行级别的BEFORE触发器有效），你可能不会轻易跳过TRUNCATE，但如果TRUNCATE被尝试，你还是无法抛出错误信息。使用之前DELETE使用过的相同函数，来创建AFTER触发器：

CREATE TRIGGER disallow_truncate AFTER TRUNCATE ON delete_test1 FOR EACH STATEMENT EXECUTE PROCEDURE cancel_op();
 
 
这时你再也无法使用TRUNCATE:
TRUNCATE delete_test1;
[SQL]TRUNCATE delete_test1;
 
[Err] ERROR:  you are not allowed to TRUNCATE rows in public.delete_test1
CONTEXT:  PL/pgSQL function cancel_op() line 4 at RAISE

当然，你可以在一个BEFORE触发器抛出错误，但之后，你需要编写你自己的不受限制的错误抛出触发器函数，来替代cancel_op()。

4、修改NEW记录

另一种常用的审核方式是在同一行的特定字段中，如同记录数据一样记录操作信息。例如，我们定义一个触发器，这个触发器可以在每个INSERT和UPDATE事务发生的时候，在字段last_changed_at与字段last_changed_by中记录操作时间与当前的用户。在行级别的BEFORE触发器里面，你可以通过变更NEW记录的方式，修改实际需要被写入数据库的内容。你可以将值分配给一些字段，甚至可以使用相同结构返回一个不同的记录。例如，如果你想通过UPDATE触发器返回OLD，你需要确保行没有被更新。

时间戳触发器

为了在表中创建审核记录的基础内容，我们创建一个触发器，记录最后做变动的用户以及变动的发生时间：

CREATE OR REPLACE FUNCTION changestamp()
  RETURNS TRIGGER AS $$
BEGIN
  NEW.last_changed_by = SESSION_USER;
  NEW.last_changed_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

当然，这个只对于有正确字段的表有效：

​
CREATE TABLE modify_test(
    id serial PRIMARY KEY,
    data text,
    created_by text default SESSION_USER,
    created_at timestamp default CURRENT_TIMESTAMP,
	last_changed_by text default SESSION_USER,
	last_changed_at timestamp default CURRENT_TIMESTAMP);
 
​
CREATE TRIGGER changestamp BEFORE UPDATE ON modify_test FOR EACH ROW EXECUTE PROCEDURE changestamp();

现在，让我们看一下我们刚创建的触发器：

INSERT INTO modify_test(data) VALUES('something');
 
UPDATE modify_test SET data='something else' WHERE id =1;
 
SELECT * FROM modify_test;

5、不可改变的字段触发器

如果你将行中的字段作为你审核记录的部分内容，那么你需要确保值反馈的是真实情况。我们曾经确保字段last_changed_*能够包含正确的值，但是created_by 与created_at这两个值是否正确呢？这些在后续的升级中能够轻易被修改，但它们不应该发生变动。甚至在刚开始的时候，它们可被错误的赋值，因为在insert语句中赋予任何其他值，默认值就会很容易被忽略。

所以，让我们把changestamp()触发器函数修改成一个usagestamp()函数，这样能确保初始值的准确性与稳定性：

CREATE or REPLACE FUNCTION usagestamp()
RETURNS TRIGGER AS $$
BEGIN
   if TG_OP = 'INSERT' THEN
     NEW.created_by = SESSION_USER;
     NEW.created_at = CURRENT_TIMESTAMP;
   ELSE
     NEW.created_by = OLD.created_by;
     NEW.created_at = OLD.created_at;
   END IF;
  
   NEW.last_changed_by = SESSION_USER;
   NEW.last_changed_at = CURRENT_TIMESTAMP;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

在insert这个例子中，我们将created_*字段设置成所需的值，忽视了insert查询尝试设置的值。而对于update，我们只是将继续使用旧值，继续忽略其他修改尝试。

这个函数接下来被用于创建一个BEFORE INSERT OR UPDATE触发器：

CREATE TRIGGER usagestamp BEFORE INSERT OR UPDATE ON modify_test FOR EACH ROW EXECUTE PROCEDURE usagestamp();

现在，让我们尝试更新创建的审计日志信息。首先，我们需要删除原有的触发器，这样同一张表就不会有两个触发器。然后，我们将试图改变created_by和created_at值：

DROP TRIGGER changestamp ON modify_test;
 
UPDATE modify_test SET created_by ='notpostgres',created_at='2019-05-08';
 
SELECT * from modify_test;

可以看出，创建的信息还是一样的，但是最近修改过的信息已经得到更新。

6、有条件的触发器

通用的WHEN子句是控制触发器的一种灵活方法，其与SQL查询中的WHERE相似。借助WHEN子句，你可以编写任何的表达式（除子查询），同时可以在触发器函数被调用之前测试表达式。表达式必须输出Boolean值，如果该值是FLASE（或者NULL，会自动转换成FALSE），触发器函数就不会被调用。

例如，你可以使用以下代码。来实施“周五下午禁止更新”的政策。

CREATE OR REPLACE FUNCTION cancel_with_message()
RETURNS TRIGGER AS $$
BEGIN
   raise EXCEPTION '%', TG_ARGV[0];
   RETURN NULl;
END;
$$ LANGUAGE plpgsql;

这个函数抛出一个异常情况，这个异常是通过create trigger 语句中的字符串参数进行传递的。请注意，我们不能直接将TG_ARGV[0]作为消息进行使用，因为PL/pgSQL的语法要求字符串常量作为raise的第三个元素。

借助前面的触发器函数，我们可以通过指定条件（在WHEN(....)子句中）和条件符合的情况下需要抛出的消息（作为触发器的参数），来设置触发器进而执行各种约束。

CREATE TRIGGER no_update_on_friday_afternoon BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON new_tasks FOR EACH STATEMENT
WHEN (CURRENT_TIME > '12:00' AND EXTRACT(DOW FROM CURRENT_TIMESTAMP)=5) EXECUTE PROCEDURE cancel_with_message('sorry, we have a
"NO task change on Friday afternoon" policy!');

如果在星期五的下午尝试去修改new_tasks表，他会得到关于这个政策的消息。

注意：关于触发器参数，其中需要注意的事情是参数列表总是一个文本数组（text[]）。create trigger语句中给出的所有参数都被转换成字符，这个也包括了任何的null值。这意味着，如果我们把null放入参数列表中，PG_ARGV中的相应插槽中会得到文本null。

7、传递给PL/pgSQL  TRIGGER函数的变量

对于数据库端的操作，触发器时一种合适的工具，例如审计、日志、执行复杂约束甚至是复制。然而，对于大多数应用程序逻辑，我们还是尽可能避免使用触发器，由于触发器会导致离奇却难以调试的问题

下面是一张完整使用PL/pgSQL编写的触发器函数变量列表：