Postgres審計觸發單個行

我想在 postgres 中創建一個基本的審計日志觸發器，我可以在各種表中使用它。

我唯一的要求是審計日志將每個更新的值顯示為單獨的條目。即，如果對INSERT具有 5 行的表執行an ，我會在審計日志表中收到 5 個條目（每個添加的值一個），或者如果從表中洗掉該行，我會收到 5 個條目（每個洗掉的值一個）

我查看了各種示例，但仍然無法正確獲取輸出，尤其是在操作為 UPDATE 時。

這是基本的觸發流程。

-- 1. Making the Audit Table

CREATE TABLE audit_table

-- The audit table which will show: 
-- Table, the ID of whats being changed, new columns, old columns, 
-- user, operation(insert update delete), and timestamp

(
"src_table" TEXT NOT NULL,
"src_id" INT NOT NULL,
"col_new"  TEXT,
"col_old" TEXT,
"user" TEXT DEFAULT current_user,
"action" TEXT NOT NULL,
"when_changed" TIMESTAMP
);

-- 2. Creating the base audit trigger function, the engine for processing changes --

CREATE OR REPLACE FUNCTION audit_trigger() 
 RETURNS trigger AS 
 
$$

BEGIN
    IF TG_OP = 'INSERT' --Only shows new values
    THEN
            INSERT INTO audit_table ( "src_table", "src_id", "col_new", "user", "action", "when_changed")
                VALUES(TG_TABLE_NAME, TG_RELID, row_to_json(NEW), current_user, TG_OP, current_timestamp);
        RETURN NEW;
        
    ELSIF TG_OP = 'UPDATE' --Shows new and old values
    THEN
            INSERT INTO audit_table ("src_table", "src_id", "col_new", "col_old", "user", "action", "when_changed")
                VALUES(TG_TABLE_NAME, TG_RELID, row_to_json(NEW), row_to_json(OLD), current_user, TG_OP, current_timestamp);
        RETURN NEW;
        
    ELSIF TG_OP = 'DELETE' --Only shows old values
    THEN
            INSERT INTO audit_table ("src_table", "src_id", "col_old", "user", "action", "when_changed")
                VALUES(TG_TABLE_NAME, TG_RELID, row_to_json(OLD), current_user, TG_OP, current_timestamp);
        RETURN OLD;
    END IF;
END

$$

LANGUAGE 'plpgsql';

-- 3. Basic logic for calling audit trigger on tables, works for any insert, update, delete

CREATE TRIGGER test_audit_trigger
 BEFORE INSERT OR UPDATE OR DELETE 
 
 ON test_table
 
 FOR EACH ROW EXECUTE PROCEDURE audit_trigger();

 

問題：

• row_to_json將整個舊的或新的有效負載作為一列回傳。我想將每個更改作為 audit_table 中的單獨條目行回傳，例如column_name old_value new_value用作模式。實作這一目標的最簡單方法是什么？將其設為 json 不是必需的。

也不需要：

• 讓它成為BEFORE關鍵字，如果AFTER或兩者的組合更好。
• 一個觸發器完成所有 3 個功能，它可以是一個單獨的觸發器或每個動作的函式
• 回傳未更改的值，例如，如果UPDATE僅更改 1 個值，我不需要日志中未更改的值。

uj5u.com熱心網友回復：

這是基于您的可能的解決方案。一個AFTER行觸發insert，update和delete。的結構audit_table有點不同。舊值和新值被保存為它們的文本表示。您可能希望更改 和 下的行為insert，delete因為它們似乎過于冗長。

不相關但根據您的要求，審計表必須實作 EAV（物體、屬性、值）反模式。

drop table if exists audit_table;
create table audit_table
(
 src_table text not null,
 col_name text not null,
 v_old text,
 v_new text,
 "user" text not null default current_user,
 action text not null,
 when_changed timestamptz not null default current_timestamp
);

create or replace function audit_tf() returns trigger language plpgsql as 
$$
declare 
  k text;
  v text;
  j_new jsonb := to_jsonb(new);
  j_old jsonb := to_jsonb(old);
begin
    if TG_OP = 'INSERT' then -- only shows new values
        for k, v in select * from jsonb_each_text(j_new) loop
            insert into audit_table (src_table, col_name, v_new, action) 
            values (TG_TABLE_NAME, k, v, TG_OP);
        end loop;      
    elsif TG_OP = 'UPDATE' then -- shows new and old values 
        for k, v in select * from jsonb_each_text(j_new) loop
            if (v <> j_old ->> k) then
                insert into audit_table (src_table, col_name, v_new, v_old, action) 
                values (TG_TABLE_NAME, k, v, j_old ->> k, TG_OP);
            end if;
         end loop;  
    elsif TG_OP = 'DELETE' then -- only shows old values
        for k, v in select * from jsonb_each_text(j_old) loop
            insert into audit_table (src_table, col_name, v_old, action) 
            values (TG_TABLE_NAME, k, v, TG_OP);
        end loop;      
    end if;
    return null;
end;
$$;
LANGUAGE 'plpgsql';

-- Demo

drop table if exists delme;
create table delme (x integer, y integer, z text, t timestamptz default now());

create trigger test_audit_trigger
after insert or update or delete on delme
for each row execute procedure audit_tf();

insert into delme (x, y, z) values (2, 2, 'two');
update delme set x = 1, y = 10 where x = 2;
delete from delme where x = 1;
select src_table, col_name, v_old, v_new, action from audit_table;

標籤：PostgreSQL的

上一篇：查詢的執行計劃

下一篇：從瀏覽器啟動Teams應用程式