Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

komplexe Überwachungsabfrage

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von Joerg, 7 Februar 2015.

  1. Joerg

    Joerg Benutzer

    Hallo, da meine SQL-Kenntnisse noch sehr eingeschränkt sind und mich die Problematik überfordert stelle ich mein Problem hier mal ein.

    Ich möchte regemäßig per sql-Script-Abfrage die Veränderungen eines Feldwertes (Wert = Datum oder NULL) bestimmter Datensätze (Feld3 = ABC) in einer Tabelle überwachen und in einer zweiten Datenbank protokollieren.
    In eine zweite Datenbank deshalb, weil die Datenbank1 eine komplexe Produktivdatenbankanwendung ist die ich nicht verändern möchte.
    Datenbank1, Tabelle1, Feld2 soll überwacht werden.
    Identifiziert wird jede Zeile über eine eindeutige ID (Feld1) und einen fixen Wert in Feld3 (z.B.: "ABC").
    Protokolliert wird in Datenbank2, Tabelle2, Feld1, Feld2 + Datumsfeld (Änderungsdatum).

    Beispiel:

    Tabelle1.JPG


    In Datenbank 1 ändert sich am 07.02.2015 bei ID2 und ID3 das Feld2, zusätzlich kommt Datensatz ID5 und ID6 dazu:

    Tabelle2.JPG

    Das Skript soll dies erkennen und in Datenbank2 folgende Änderungen / Ergänzungen vornehmen

    (natürlich nur bei/mit den "ABC"- Datensätzen):

    Tabelle3.JPG
    Meine angedachte Vorgehensweise wäre folgende:

    Zuerst (einmalig) in Datenbank2 die betreffende Tabelle anlegen und mit allen Daten aus Datenbank1 (Feld3 = ABC) füllen (Datum = 01.02.2015).
    Danach per Script regelmäßig alle betreffenden Datensätze aus Datenbank1 (Feld3 = ABC) in eine (ggf. temporäre) Tabelle3 in Datenbank2 kopieren (um nicht in 2 Datenbanken arbeiten zu müssen).
    Danach die "Überwachungsaktion" inkl. Ergänzung der neuen Datensätze in Tabelle2 durchführen. Anschließend alle Datensätze in der Tabelle3 löschen.

    Ziel ist wie gesagt, alle Veränderungen in Feld2 bei Datensätzen mit Feld3 = "ABC" zu protokollieren.
    Über eine gefilterte Abfrage (über Feld1) kann ich mir dann spezielle Datensätze heraussuchen.

    Das Erstellen der Datenbank / Tabelle bzw. das Füllen der (ggf. temporären) Tabelle ist nicht das Problem. Mein Problem ist die Umsetzung der Erkennung der Veränderung bzw. der neuen Datensätze.
    Wäre diese Vorgehensweise richtig oder gibt es einfachere Vorschläge (und Lösungen) ?
    Vielen Dank schon mal vorab.
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Dummer Ansatz. Datenbanken kennen TRIGGER. Diese 'feuern' bei Events wie INSERT, UPDATE und DELETE.

    Das wäre die eine Lösung, in PostgreSQL ab 9.4 käme noch eine andere Lösung, die noch deutlich effizienter wäre: logical Replication Slots. Das hat aber M$SQL nicht, falls dazu Du oder jemand anders Fragen hat kann ich das gern näher erleutern.
     
  3. Joerg

    Joerg Benutzer

    "Dummer Ansatz" akzeptiere ich kleinlaut - da ich es nicht besser weiß.
    Ansonsten hilft mir PostgreSQL nicht weiter ... und der Rest auch nicht.
    Sorry - aber trotzdem vielen Dank.
     
  4. akretschmer

    akretschmer Datenbank-Guru

    TRIGGER? Auch nicht?
     
  5. Joerg

    Joerg Benutzer

    Das man mit einem Trigger eine "Ereignisüberwachung" machen kann, ist mir bekannt.
    Wie ich es in diesem Fall aber speziell einsetze leider nicht so richtig.

    Mein Skript soll einmal täglich die Veränderungen erkennen und protokollieren.
     
  6. Joerg

    Joerg Benutzer

    Ich wollte in Datenbank2 alle identischen Einträge (ID + Feld1 + Feld2 identisch) in der temporären Tabelle3, die auch in Tabelle2 vorhanden sind löchen und anschließend die verbleibenden Datensätze in Tabelle 2 verschieben und das Änderungsdatum (Datum) setzen.
    Dafür müsste ich natürlich den ID noch mit übernehmen.
     
  7. Distrilec

    Distrilec Datenbank-Guru

    Du kannst dir auch einfach die ganze Arbeit ersparen und nen Trigger verwenden... Aber hey... Warum einfach wenns auch schwierig geht :)
    Wenn du die RICHTIGE Antwort nicht aktzeptieren willst, warum überhaupt erst fragen? ;)
     
    akretschmer gefällt das.
  8. akretschmer

    akretschmer Datenbank-Guru

    Ich zeig es mal ganz trivial, aber mit PostgreSQL.

    Code:
    test=# create table daten (id int primary key, val text);
    CREATE TABLE   
    Time: 8,623 ms   
    test=*# create table daten_log (id serial primary key, ts timestamp default now(), trigger text, daten_id int, daten_val text);
    CREATE TABLE   
    Time: 30,234 ms   
    test=*# create or replace function my_trigger() returns trigger as
    test-#   
    test-# $$begin   
    test$#  insert into daten_log(trigger, daten_id, daten_val)   
    test$#  select   
    test$#  TG_OP,   
    test$#  case   
    test$#  when TG_OP = 'DELETE' then old.id   
    test$#  when TG_OP = 'INSERT' then new.id   
    test$#  when TG_OP = 'UPDATE' then new.id   
    test$#  end,   
    test$#  case   
    test$#  when TG_OP = 'INSERT' then new.val   
    test$#  when TG_OP = 'UPDATE' then old.val || '---' || new.val
    test$#  when TG_OP = 'DELETE' then old.val   
    test$#  end;   
    test$#  return new;   
    test$# end; $$language plpgsql;   
    CREATE FUNCTION   
    Time: 0,610 ms   
    test=*# create trigger trg_log after insert or update or delete on daten for each row execute procedure my_trigger();
    CREATE TRIGGER   
    Time: 0,476 ms   
    
    Damit hast erst einmal 2 leere Tabellen und einen TRIGGER auf der Tabelle daten, der bei allen 3 Events feuert.

    Nun spielen wir damit:

    Code:
    test=*# select * from daten;   
     id | val   
    ----+-----   
    (0 rows)   
    
    Time: 0,468 ms
    test=*# select * from daten_log;
     id | ts | trigger | daten_id | daten_val
    ----+----+---------+----------+-----------
    (0 rows)   
    
    Time: 0,436 ms
    test=*# insert into daten values (1, 'eins');
    INSERT 0 1   
    Time: 0,866 ms   
    test=*# insert into daten values (2, 'zwei');
    INSERT 0 1   
    Time: 0,379 ms   
    test=*# select * from daten;   
     id | val
    ----+------
      1 | eins
      2 | zwei
    (2 rows)
    
    Time: 0,271 ms
    test=*# select * from daten_log;
     id |  ts  | trigger | daten_id | daten_val
    ----+----------------------------+---------+----------+-----------
      1 | 2015-02-07 15:12:26.394147 | INSERT  |  1 | eins
      2 | 2015-02-07 15:12:26.394147 | INSERT  |  2 | zwei
    (2 rows)
    
    Time: 0,265 ms
    test=*# update daten set val = 'zwei-neu' where id = 2;
    UPDATE 1
    Time: 0,513 ms
    test=*# select * from daten_log;
     id |  ts  | trigger | daten_id |  daten_val
    ----+----------------------------+---------+----------+-----------------
      1 | 2015-02-07 15:12:26.394147 | INSERT  |  1 | eins
      2 | 2015-02-07 15:12:26.394147 | INSERT  |  2 | zwei
      3 | 2015-02-07 15:12:26.394147 | UPDATE  |  2 | zwei---zwei-neu
    (3 rows)
    
    Time: 0,257 ms
    test=*# delete from daten where id=1;
    DELETE 1
    Time: 0,541 ms
    test=*# select * from daten_log;
     id |  ts  | trigger | daten_id |  daten_val
    ----+----------------------------+---------+----------+-----------------
      1 | 2015-02-07 15:12:26.394147 | INSERT  |  1 | eins
      2 | 2015-02-07 15:12:26.394147 | INSERT  |  2 | zwei
      3 | 2015-02-07 15:12:26.394147 | UPDATE  |  2 | zwei---zwei-neu
      4 | 2015-02-07 15:12:26.394147 | DELETE  |  1 | eins
    (4 rows)
    
    Time: 0,213 ms
    test=*#
    
    Das ist jetzt sehr einfach, es gibt umfangreichere Lösungen z.B. hier

    http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html
    http://www.postgresonline.com/journal/archives/330-Using-HStore-for-Archiving.html
     
  9. Joerg

    Joerg Benutzer

    Wenn ich es einfach bzw. per Trigger wüsste, würde ich nicht fragen. PostgreSQL verwende ich zwar nicht, werde aber versuchen es nachzuvollziehen.
    Wenn es aber nicht in MSSQL genutzt werden kann, nützt mir der Ansatz auch nichts.
    Trotzdem Danke für die Hilfe.
     
  10. akretschmer

    akretschmer Datenbank-Guru

    Der Ansatz ist in M$SQL exakt gleich, nur die Syntax wird etwas anders sein. Ich hab kein M$SQL.
     
  11. akretschmer

    akretschmer Datenbank-Guru

    http://bit.ly/1CExIQP
     
  12. Joerg

    Joerg Benutzer

    Ich glaube, dafür reichen meine Kenntnisse bei weitem nicht. Den Trigger müßte ich ja vermutlich direkt in der Datenbank anlegen. Das kann / möchte / darf ich aber nicht. Daher der Gedanke es per externen Scriptaufruf zu machen - auch wenn es umständicher und weniger professionell ist.
     
  13. Distrilec

    Distrilec Datenbank-Guru

    Hier mal ein paar Ansätze...

    Um deine Tabelle einmal täglich zu füllen:
    Code:
    Insert into Tabelle2@datenbank2 (Select * From tabelle1 Where änderungsdatum > sysdate - 1);
    Um deine Tabelle3 abzugleichen:
    Code:
    Insert Into tabelle2 (Select t.*, sysdate as änderungsadtum From (Select * From tabelle3 Minus Select * From tabelle2) t)
     
  14. ukulele

    ukulele Datenbank-Guru

    Wenn du Schreibrechte auf die Quell-Datenbank hast und sich die Tabellenstruktur nicht ändert wäre eine Trigger-Lösung definitiv die elegantere Wahl. Wenn das nicht geht, kannst du wie von Distrilec beschrieben auch einfach die Tabelle kopieren und die Datensätze mit Datum versehen.

    Das werden natürlich viele Datensätze, daher würde ich mit EXCEPT die Filtern, die zum Vortag unverändert geblieben sind. Wenn zu deinen zu protokolliernden "Veränderungen" auch das Löschen von Datensätzen in der Quell-Tabelle gehört, musst du das dann natürlich auch abbilden. Ich habe es bei uns genauso gemacht und bin nicht sehr begeistert weil es sehr komplex ist, denn aktuellen Stand aus den gesicherten Daten zu ermitteln.
     
  15. Joerg

    Joerg Benutzer

    Vielen Dank für die weiteren Anregungen und Unterstützung.
    Momentan bin ich in der Sache noch nicht weitergekommen - mangels Zeit.
    Lösen muss ich die Aufgabe aber noch.

    Löschen (an der Quelldatenbank) will ich nichts, die Zieldatenbank soll natürlich immer nur die Veränderungen speichern. Ob meine Rechte ausreichen um einen Trigger anzulegen, muss ich prüfen.
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden