如何让PostgreSQL在从另一个表中删除时向表中插入一行数据？

Question

我们有一个应用程序，它会根据用户请求从表中删除一行。我无法更改应用程序代码。但是，我希望根据正在删除的行的信息，从几个其他表中获取信息并将一行插入另一个表中（类似于日志记录）。

如何在PostgreSQL中实现这一目标？

- Benson Jin

---

通过触发器，查看https://dev59.com/eHM_5IYBdhLWcg3wn0vT - Marco Mariani

Answer 1

编写一个触发器函数。类似这样：

CREATE OR REPLACE FUNCTION trg_backup_row()
  RETURNS trigger AS
$BODY$
BEGIN

INSERT INTO other_tbl
SELECT (OLD).*, t.other_col                -- all columns of from old table
-- SELECT OLD.col1, OLD.col2, t.other_col  -- alternative: some cols from old tbl
FROM   third_tbl t
WHERE  t.col = OLD.col  -- link to third table with info from deleted row
AND    <unique_condition_to_avoid_multiple_rows_if_needed>;

RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

还有一个触发器 ON DELETE。像这样：

CREATE TRIGGER delaft
  AFTER DELETE
  ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_backup_row();

关键要素

• Best make it a trigger AFTER DELETE and FOR EACH ROW.

• To return all columns from the old table use the syntax (OLD).*. See the manual about accessing composite types. Alternatively OLD.* is valid syntax, too, because OLD is added to the FROM clause implicitly. For a VALUES expression it would have to be (OLD).*, though. Like:

  INSERT INTO other_tbl
  VALUES((OLD).*, some_variable)
  

• You can include values from any other table like I demonstrate. Just make sure to get a single row, or you create multiple entries.

• As the trigger fires AFTER the event, the function can RETURN NULL.

---

关于可见性

针对@couling的留意评论。

虽然外键可以被声明为DEFERRED, 但这只会推迟完整性检查，而不是删除本身。在此AFTER DELETE触发器被调用时，在其之前执行的触发器或者由ON DELETE CASCADE外键引起的被删除的行将不再可见。（显然，这一切都在一个事务中进行。这些细节对于其他事务来说并不重要，它们将看到所有或没有影响。请参考手册了解更多关于MVCC模型和事务隔离的内容。）

因此，如果您想在INSERT中包含依赖于这种方式的行的值，请确保在这些行被删除之前调用这个触发器。您可能需要将此触发器设置为BEFORE DELETE。

或者这也可能意味着您需要按顺序订购触发器，BEFORE触发器显然要先于AFTER触发器执行。而在同一级别的触发器将按照字母顺序执行。

然而，只要我在这里非常准确，我还可以补充一点，即在其他BEFORE触发器中对行（或相关行）所做的更改仅在此之前调用这些触发器时才可见。

我的建议是将其作为AFTER触发器，因为如果其他触发器可能会在操作进行到一半时取消（回滚）DELETE，则它更不容易出现问题，并且更加经济实惠 - 只要没有上述情况。

- Erwin Brandstetter

---

如果您在触发delete之后使用了on delete cascade引用，那么将作为其一部分被删除的子元素是否仍然可用于日志记录目的？ - Philip Couling

Answer 2

您可能需要使用我编写的函数来保留历史数据。简短描述：

历史数据存储在名为audit的单独模式中。因此，第一步是创建此模式：

CREATE SCHEMA audit;

在审计模式中，可以找到来自public的表的精确副本，在公共架构中的数据发生变化时动态创建。因此，在第一次使用数据库之前，审计架构保持为空，直到用户将首次插入其中一个表。

函数_audit_table_creator（name）复制公共架构中表的结构，并在审计架构中创建相同的表，带有一些额外的列，我称之为‘审计时间戳’。审计时间戳保留有关以下信息：

- 记录删除的时间（shift_time）， - 进行删除操作的用户（who_altered）， - ‘DELETE’标记（alter_type），以及 - 仅适用于更新操作的已更改列（changed_columns）；

我认为这种解决方案最大的优点是支持复合主键（函数_where_clause_creator（text []）通过按正确顺序连接字符串为触发器调用的表创建适当的where子句）；

查看历史记录：

每次我们想要检索存档数据时，都必须使用别名，即检索关于user_id = 5的用户的历史数据需要编写：

SELECT * FROM audit.users WHERE user_id = 5; 

因此，相同的查询可以在两个模式中使用，但要检索历史数据，必须在表名前加上“audit.”。

如果您希望为数据库中的所有表自动创建删除触发器，可以执行以下查询：

SELECT * FROM  audit_gen_triggers();

主要功能：

CREATE OR REPLACE FUNCTION audit_delete() 
  RETURNS trigger AS
$BODY$DECLARE
t_name text;                 
query_op text;              
primary_keys text;           
c record;
key_arr text;
keys_arr text;
p_r text;

    BEGIN

    t_name := 'audit.' || TG_TABLE_NAME;
         IF NOT EXISTS(SELECT 1 FROM pg_tables WHERE schemaname = 'audit' AND 
                       tablename = TG_TABLE_NAME) THEN
           EXECUTE 'SELECT _audit_table_creator(table_name := ($1)::name)' 
           USING TG_TABLE_NAME;
        END IF; 

        FOR c IN SELECT pg_attribute.attname
                      FROM pg_index, pg_class, pg_attribute 
                      WHERE 
                      pg_class.oid = TG_TABLE_NAME::regclass AND
                      indrelid = pg_class.oid AND
                      pg_attribute.attrelid = pg_class.oid AND
                      pg_attribute.attnum = ANY(pg_index.indkey) AND 
                      indisprimary LOOP

               key_arr := c.attname || ', ($1).' || c.attname;
               keys_arr := concat_ws(',', keys_arr, key_arr);
               END LOOP;
               keys_arr := '{' || keys_arr || '}';

        EXECUTE 'SELECT _where_clause_creator(VARIADIC ($1)::text[])' 
        INTO p_r USING keys_arr;
        -- raise notice 'tablica where: %', p_r;

        -- zapisz do tabeli audytowanej wszystkie usuniete wartosci
        query_op := 'INSERT INTO '||  t_name ||
                           ' SELECT NEXTVAL(''serial_audit_' 
                             ||  TG_TABLE_NAME ||'''::regclass),
                             CURRENT_USER, ''' ||  TG_OP || ''',
                             NULL,
                             NOW(),
                             ($1).*
                             FROM ' || TG_TABLE_NAME ||
                             ' WHERE  ' || p_r;
                EXECUTE query_op USING OLD;
        RETURN OLD;
    END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

触发器：

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

其他使用的功能：

    CREATE OR REPLACE FUNCTION _array_position(anyarray, anyelement)
      RETURNS integer AS
    $BODY$
    SELECT i
       FROM (SELECT generate_subscripts($1, 1) as i, unnest($1) as v) s
      WHERE v = $2
      UNION ALL
      SELECT 0 
      LIMIT 1;
    $BODY$
      LANGUAGE sql STABLE
      COST 100;


CREATE OR REPLACE FUNCTION _audit_table_creator(table_name name)
RETURNS void AS
$BODY$
DECLARE
query_create text; 

BEGIN
query_create := 'DROP TABLE IF EXISTS temp_insert; 
                 DROP TABLE IF EXISTS temp_insert_prepared';
EXECUTE query_create;

query_create := 'DROP SEQUENCE IF EXISTS serial_audit_' ||  table_name;
                 EXECUTE query_create;
query_create := 'CREATE SEQUENCE serial_audit_' || table_name || ' START 1; 
                 ALTER TABLE serial_audit_' || table_name || 
                 ' OWNER TO audit_owner;';
                 EXECUTE query_create;
query_create := 'CREATE TEMPORARY TABLE temp_insert_prepared ( '
                 || table_name || '_audit_id bigint DEFAULT 
                 nextval(''serial_audit_' || table_name || '''::regclass),
                 who_altered text DEFAULT CURRENT_USER,
                 alter_type varchar(6) DEFAULT ''INSERT'',
                 changed_columns text,
                 shift_time timestamp(0) without time zone DEFAULT NOW(),
                 PRIMARY KEY(' || table_name || '_audit_id )) ON COMMIT DROP';

EXECUTE query_create;

query_create := 'CREATE TEMPORARY TABLE temp_insert ON COMMIT DROP AS TABLE 
                ' || table_name;
                EXECUTE query_create;

                query_create := 'CREATE TABLE audit.' || table_name || 
                ' AS SELECT a.*, b.* FROM temp_insert_prepared a, temp_insert b 
                 WITH NO DATA';
                EXECUTE query_create;

    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;

CREATE OR REPLACE FUNCTION _where_clause_creator(VARIADIC keys_given text[])
RETURNS text AS
$BODY$
DECLARE
x text;
where_clause text;

BEGIN
FOREACH x IN ARRAY keys_given LOOP
IF ((SELECT _array_position(keys_given, x))%2) <> 0 THEN
where_clause := concat_ws(' AND ', where_clause, x);
ELSE
       where_clause := concat_ws(' = ', where_clause, x);
END IF;
END LOOP;
RETURN where_clause;
END;
$BODY$
  LANGUAGE plpgsql STABLE
  COST 100;



CREATE OR REPLACE FUNCTION audit_gen_triggers()
  RETURNS void AS
$BODY$
DECLARE
r record;
query_create text;
BEGIN
FOR r IN SELECT table_name
             FROM information_schema.tables
             WHERE table_schema = current_schema AND
                         table_type = 'BASE TABLE' LOOP

query_create := 'DROP TRIGGER IF EXISTS ' || r.table_name || '_delete_audit ON ' 
                 || r.table_name || ' CASCADE;
                 CREATE TRIGGER ' || r.table_name || '_delete_audit
                 BEFORE DELETE
                 ON ' || r.table_name || '
                 FOR EACH ROW
                 EXECUTE PROCEDURE audit_delete();';

EXECUTE query_create;
END LOOP;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

- Borys