Trigger nur geänderte Spalten

Probleme und Fragen zum Thema Trigger und Funktionen.
Antworten
Frank
Neuer Benutzer
Beiträge: 3
Registriert: Mo 6. Nov 2017, 10:10

Trigger nur geänderte Spalten

Beitrag von Frank » Mo 6. Nov 2017, 10:30

Hallo in das Forum,

mein Name ist Frank und starte gerade meine ersten Schritte mit Postgres, und mit SQL :-)
Die ersten Schritte um meine 'alten' DBF zu konvertieren und meine Programme anzupassen sind bis jetzt recht erfolgreich. Nun bin ich bei Triggern gelandet und hänge da an einem Problem, wo ich keine Lösung finde.
Ich möchte ein Audit Trigger erstellen, wo ich nur die tatsächlichen Änderungen mitspeichere. Mein Vorgehen bisher ist bis jetzt, die Tabellenstrukture auslesen und jeden Inhalt in der NEW und OLD vergleichen. Ist dieser unterschiedlich, später ein Text bilden der gespeichert wird.
Wie kann ich z.B. anstatt:
NEW.COL1 != OLD.COL1
ersetzen im LOOP mit dem Spaltennamen aus columns?


CREATE OR REPLACE FUNCTION public.adressen_update()
RETURNS trigger AS
$BODY$
DECLARE
columns RECORD;
n char(500);
o char(500);

BEGIN


FOR columns IN select column_name from information_schema.columns where table_name=TG_TABLE_NAME::TEXT

LOOP
n='NEW.'||columns.column_name; /// hier ist mein Problem, ich möchte jeden Wert vergleichen, und diesen dann in changes speicher.
o='OLD.'||columns.column_name;
if (n != o)
THEN
INSERT INTO auditlog (tablename,username,creation,action,changes)
VALUES (TG_TABLE_NAME::TEXT,session_user::TEXT,now(),TG_OP,n);
END IF;
END loop;


RETURN NEW;


return NEW;

EXCEPTION
WHEN data_exception THEN
RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN unique_violation THEN
RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN OTHERS THEN
RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.adressen_update()
OWNER TO postgres;


Danke für eure Hilfe,
Frank

akretschmer
Erfahrener Benutzer
Beiträge: 7306
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Trigger nur geänderte Spalten

Beitrag von akretschmer » Mo 6. Nov 2017, 11:29

Wilkommen bei PostgreSQL!

Zu Deinem Problem: n und o enthalten einen Text, Du willst diesen Text quasi als SQL interpretieren und ausführen. Dazu dient EXECUTE (im Kontext einer Stored Proc führt EXECUTE einen dynamisch erstellten String aus) Damit sollte Dein Problem soweit lösbar sein.


Beachte bitte: Deine Löung wird die Last auf dem Server vervielfachen, da im TRIGGER nun für jede Zeile eine ganze Latte von Aktionen auszuführen sind, noch dazu in einer Schleife. Der View information_schema.columns basiert selbst auf einem recht komplexen SQL. Deine Abfrage auf diesen View beachtet auch nicht, daß ein und derselbe Tabellennamen mehrfach in unterschiedlichen Schemas auftreten kann.

Für solche Zwecke gibt es erprobte Lösungen:

https://www.pgaudit.org/
etwas älter: https://andreas.scherbaum.la/blog/archi ... lelog.html (suche dort mal in den Kommentaren, da hat ein gewisser akretschmer noch auf eine andere Lösung hingewiesen...)

Mit pglogical könntest Du auch noch eine weitere Variante realisieren, indem das Transaction Log als Basis des Auditlogs dient.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

castorp
Erfahrener Benutzer
Beiträge: 1009
Registriert: Sa 27. Jun 2009, 22:24
Kontaktdaten:

Re: Trigger nur geänderte Spalten

Beitrag von castorp » Mo 6. Nov 2017, 12:13

A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Frank
Neuer Benutzer
Beiträge: 3
Registriert: Mo 6. Nov 2017, 10:10

Re: Trigger nur geänderte Spalten

Beitrag von Frank » Mo 6. Nov 2017, 12:20

Hallo

vielen Dank für die ausführliche Antwort. Ich werde mir diese Links durchlesen und wohl doch lieber alles speichern, anstatt nur die geänderten Daten. Muss erstmal sehen welche Datenmenge da auf einen zukommen.

Grüße Frank

Frank
Neuer Benutzer
Beiträge: 3
Registriert: Mo 6. Nov 2017, 10:10

Re: Trigger nur geänderte Spalten

Beitrag von Frank » Mo 6. Nov 2017, 12:24

Danke für die Links
hab wohl nicht das richtige gesucht in Google :-)

Genug zum studieren, für die Lernkurve :-)

castorp
Erfahrener Benutzer
Beiträge: 1009
Registriert: Sa 27. Jun 2009, 22:24
Kontaktdaten:

Re: Trigger nur geänderte Spalten

Beitrag von castorp » Mo 6. Nov 2017, 13:12

Frank hat geschrieben:
Mo 6. Nov 2017, 12:20
vielen Dank für die ausführliche Antwort. Ich werde mir diese Links durchlesen und wohl doch lieber alles speichern, anstatt nur die geänderten Daten. Muss erstmal sehen welche Datenmenge da auf einen zukommen.
Das macht die Sache etwas einfacher:

Code: Alles auswählen

CREATE TABLE audit_history
(
  event_time  timestamp,
  executed_by text, 
  old_value   jsonb,  
  new_value   jsonb 
);
 
CREATE OR REPLACE FUNCTION audit_function()
RETURNS TRIGGER AS $$
DECLARE
  l_new_data jsonb;
  l_old_data jsonb;
BEGIN
  if tg_op = 'UPDATE' then 
    l_new_data := to_jsonb(NEW);
    l_old_data := to_jsonb(OLD);
  elsif tg_op = 'INSERT' then 
    l_new_data := to_jsonb(NEW);
  else
    l_old_data := to_jsonb(OLD);
  end if;
        
  INSERT INTO audit_history 
    (event_time, executed_by, old_value, new_value)
  VALUES
    (CURRENT_TIMESTAMP, SESSION_USER, l_old_data, l_new_data);
    
  RETURN null; -- ignored
END;
$$ LANGUAGE plpgsql;
 

CREATE TRIGGER audit_test_trigger 
    AFTER INSERT OR UPDATE OR DELETE ON table_to_audit
    FOR EACH ROW EXECUTE PROCEDURE audit_function();
Damit werden für jede Änderung die Werte vorher und nachher gespeichert. Die Differenz kann man sich dann später immer noch anzeigen lassen.

Mit Postgres 10 könnte man das mit einem Statement Level Trigger etwas effizienter machen.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Antworten