使用触发器和函数进行用户创建转储

Question

因为PostgreSQL不转储对象创建日期，所以我想使用触发器和函数手动转储用户创建日期。我已经创建了触发器和函数，但它不起作用。

CREATE TABLE user_audits (
   usesysid INT GENERATED ALWAYS AS IDENTITY,
   usename varchar NOT NULL,
   created_on TIMESTAMP(6) NOT NULL
);

============================
CREATE OR REPLACE FUNCTION user_creation()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    IF NEW.usename <> OLD.usename THEN
         INSERT INTO user_audits(usesysid,usename,created_on)
         VALUES(usesysid,usename,now());
    END IF;

    RETURN NEW;
END;
$$
=================================
CREATE TRIGGER user_creation
  BEFORE UPDATE
  ON user
  FOR EACH ROW
  EXECUTE PROCEDURE user_creation();

这对于审计来说很重要，因为现在我使用日志文件来检查创建日期，但它会在一段时间后轮换。请建议更好的方法在表中转储用户创建日期，以便我可以随时检索信息。谢谢

Answer 1

我用下面的表格创建了一个类似的练习：

1. 仅包含标识列usersysid和username

的user_tbl表

CREATE TABLE user_tbl (
   usersysid INT GENERATED ALWAYS AS IDENTITY,
   username varchar NOT NULL
);

1. user_audits表，稍微修改了一下:我在其中添加了一个id标识字段。我从usersysid字段中删除了标识(因为它将由来自user_tbl)

的标识填充

CREATE TABLE user_audits (
   id INT GENERATED ALWAYS AS IDENTITY,
   usersysid INT,
   username varchar NOT NULL,
   created_on TIMESTAMP(6) NOT NULL
);

现在函数，我检查OLD.username是否为空，这意味着这是一个insert，如果NEW.username <> OLD.username是一个update。

CREATE OR REPLACE FUNCTION user_creation()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    IF OLD.username is null OR NEW.username <> OLD.username THEN
         INSERT INTO user_audits(usersysid,username,created_on)
         VALUES(NEW.usersysid,NEW.username,now());
    END IF;

    RETURN NEW;
END;
$$
;

最后是触发器，它在INSERT或UPDATE上都会触发

CREATE TRIGGER user_creation
BEFORE INSERT OR UPDATE
ON user_tbl
FOR EACH ROW
EXECUTE PROCEDURE user_creation();

现在，如果我创建两个新行，并用以下代码更新一行

insert into user_tbl (username) values('Carlo');
insert into user_tbl (username) values('Gianni');
update user_tbl set username='Giorgio' where usersysid=1;

最后，我得到了包含2个预期行的user_tbl

defaultdb=> select * from user_tbl;
 usersysid | username 
-----------+----------
         2 | Gianni
         1 | Giorgio
(2 rows)

包含3行的user_audits表(2行用于插入，1行用于更新)

defaultdb=> select * from user_audits;
 id | usersysid | username |         created_on         
----+-----------+----------+----------------------------
  1 |         1 | Carlo    | 2021-06-04 13:57:44.810889
  2 |         2 | Gianni   | 2021-06-04 13:58:14.680878
  3 |         1 | Giorgio  | 2021-06-04 13:58:44.702364
(3 rows)