时间戳

首页 > 解决方案 > 使用触发器和函数进行用户创建转储

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

问题描述

由于 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();

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

标签： postgresqlrolescreation

解决方案

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

  1. 该user_tbl表只有一个标识列usersysid和username
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为空，这意味着这是一个插入，如果NEW.username <> OLD.username然后是一个更新。

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;

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

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

和user_audits包含 3 行的表（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)

推荐阅读