Oracle - Trigger erstellen Sie eine Geschichte auf Zeile aktualisieren

Okay, das ist ein rewrite. Was ich vermisste, als ich zuerst geantwortet, dass die Anwendung ist die Speicherung seiner Geschichte in der Haupt-Tabelle. Jetzt verstehe ich, warum @NickCraver ist so entschuldigend über den code.

Nun, das erste, was zu tun ist, auf die Jagd nach dem Täter dieses design, und stellen Sie sicher, dass Sie es nie wieder tun. Die Speicherung der Geschichte wie diese nicht skaliert werden, macht die normalen (nicht-historischen) Abfragen komplizierter und sabotiert relationale Integrität. Natürlich gibt es Szenarien, in denen keine von diesen Fragen, und vielleicht Ihre Website ist einer von Ihnen, aber im Allgemeinen ist dies eine sehr schlechte Umsetzung.

Der beste Weg, dies zu tun ist,Oracle 11g-Total Recall. Es ist eine elegante Lösung, mit einem völlig unsichtbar und effizienten Umsetzung, und - bei den standards von Oracle weitere kostenpflichtige extras - ganz preiswert.

Aber wenn "Total Recall" ist aus der Frage, und Sie wirklich tun müssen, es auf diese, nicht erlauben, updates. Eine änderung zu einem bestehenden KONTAKT-Datensatz ein insert. Um diese Arbeit zu machen, die Sie benötigen, um eine Sicht mit einem INSTEAD OF-trigger. Es ist immer noch yucky, aber nicht ganz so yucky wie das, was Sie jetzt haben.

Als von Oracle 11.2.0.4 Total Recall wurde umbenannt Flashback Archive und ist Bestandteil der Enterprise-Lizenz (obwohl geschoren, die komprimierte journal-Tabellen, es sei denn, wir kaufen Sie die Erweiterte option "Komprimieren").

Diese Großzügigkeit von Oracle sollte die FDA die normale Art und Weise der Speicherung der Geschichte: es ist effizient, es ist performativ, es ist eine Oracle built-in mit der standard-syntax zur Unterstützung von historischen Abfragen. Ach ich erwarten, um zu sehen, halbgare Implementierungen mit spatchcocked Trigger gebrochen Primärschlüssel und schreckliche Leistung für noch viele Jahre. Da journalling scheint eine von diesen Ablenkungen, die Entwickler erfreuen, trotz der Tatsache, dass es low-level-Sanitär -, das ist weitgehend irrelevant für 99,99% der gesamten Geschäftstätigkeit.

Den Fall, dass jemand die gleiche hochspezialisierte Fall machen wir (Linq Zugriff machen einzelne Tabelle Geschichte es ist viel Reiniger/einfacher, das ist, was ich am Ende tun, um zu vereinfachen, was wir haben, willkommen, keine Verbesserungen....das ist nur ein Skript, dass ausgeführt wird, wenn änderungen in der Datenbank, zur Regeneration der audit-Trigger, die wichtigste änderung wird PRAGMA AUTONOMOUS_TRANSACTION; Platzierung der Geschichte erzeugen eine autonome Transaktion und nicht die Sorge um die mutation (welche ist egal, wie wir das audit):

Declare
  cur_trig varchar(4000);
  has_ver number;
Begin
    For seq in (Select table_name, sequence_name 
              From user_tables ut, user_sequences us
              Where sequence_name = replace(table_name, '_','') || '_SEQ'
                And table_name Not Like '%$%'
                And Exists (Select 1
                            From User_Tab_Columns utc
                            Where Column_Name = 'ID' And ut.table_name = utc.table_name)
                And Exists (Select 1
                            From User_Tab_Columns utc
                            Where Column_Name = 'DATE_START' And ut.table_name = utc.table_name)
                And Exists (Select 1
                            From User_Tab_Columns utc
                            Where Column_Name = 'DATE_MODIFIED' And ut.table_name = utc.table_name))
    Loop
     --ID Insert Triggers (Autonumber for oracle!)
     cur_trig := 'CREATE OR REPLACE TRIGGER ' || seq.table_name || 'CR' || chr(10)
              || 'BEFORE INSERT ON ' || seq.table_name || chr(10)
              || 'FOR EACH ROW' || chr(10)
              || 'BEGIN' || chr(10)
              || '  SELECT ' || seq.sequence_name || '.NEXTVAL INTO :new.ID FROM DUAL;' || chr(10)
              || '  IF(:NEW.ENTITY_ID = 0) THEN' || chr(10)
              || '    SELECT sysdate, sysdate, ''31-DEC-9999'' INTO :NEW.DATE_CREATED, :NEW.DATE_START, :NEW.DATE_MODIFIED FROM DUAL;' || chr(10)
              || '  END IF;' || chr(10)
              || 'END;' || chr(10);

     Execute Immediate cur_trig;

     --History on update Triggers
     cur_trig := 'CREATE OR REPLACE TRIGGER ' || seq.table_name || '_HIST' || chr(10)
              || '  BEFORE UPDATE ON ' || seq.table_name || ' FOR EACH ROW' || chr(10)
              || 'DECLARE' || chr(10)
              || '  PRAGMA AUTONOMOUS_TRANSACTION;' || chr(10)
              || 'BEGIN' || chr(10)
              || '  INSERT INTO ' || seq.table_name || ' (' || chr(10)
              || '   DATE_MODIFIED ' || chr(10)
              || '   ,ENTITY_ID ' || chr(10);

       For col in (Select column_name
                 From user_tab_columns ut
                 Where table_name = seq.table_name
                   And column_name NOT In ('ID','DATE_MODIFIED','ENTITY_ID')
                 Order By column_name)
     Loop
       cur_trig := cur_trig || '   ,' || col.column_name || chr(10);
     End Loop;

     cur_trig := cur_trig || ') VALUES ( --ID is Automatic via another trigger' || chr(10)
                          || '   SYSDATE --DateModified Set' || chr(10)
                          || '   ,:old.ID --EntityID Set' || chr(10);

     has_ver := 0;
       For col in (Select column_name
                 From user_tab_columns ut
                 Where table_name = seq.table_name
                   And column_name NOT In ('ID','DATE_MODIFIED','ENTITY_ID')
                 Order By column_name)
     Loop
       cur_trig := cur_trig || '   ,:old.' || col.column_name || chr(10);
       If Upper(col.column_name) = 'VERSION' Then 
         has_ver := 1; 
       End If;
     End Loop;

     cur_trig := cur_trig || ');' || chr(10)
                          || ':new.DATE_MODIFIED := ''31-DEC-9999'';' || chr(10)
                          || ':new.DATE_START := SYSDATE;' || chr(10);
     If has_ver = 1 Then
       cur_trig := cur_trig || ':new.version := :old.version + 1;' || chr(10);
     End If;
     cur_trig := cur_trig || 'COMMIT;' || chr(10)
                          || 'END;' || chr(10);

     Execute Immediate cur_trig;
    End Loop;
End;
/

Wenn Sie verbessern können, fühlen Sie sich frei,...ich habe nur geschrieben, eine Handvoll von PL/SQL-Skripte, die müssen nicht auftreten oft...wahrscheinlich viel zu wünschen übrig es.

Antwort Kredit zu APC für immer mich zu sehen das ein bisschen schwieriger. Ich weiß nicht empfehlen diese Geschichte layout, sofern es seine übrigen Modell/Anwendung/stack sehr gut. Für diese Anwendung ist, dass wir immer wieder zeigen, eine Mischung aus Geschichte und aktuellen, und die Filterung ist wesentlich einfacher als die Kombination, wenn es um eine Linq-to-SQL-ähnlichen Zugriff. Danke für die ganzen Antworten Jungs, alle guten Vorschläge...und wenn ich mehr Zeit und bin nicht knirschte, indem ein release-Plan, das ist etwas, ich werde überdenken, um zu sehen, ob es weiter verbessert werden kann.

Wenn Sie wollen, entwickeln Sie eine generische Lösung, möchten Sie vielleicht werfen Sie einen Blick auf DBMS_SQL-Paket. Mit ihm konnte man die Entwicklung ein Paket/Prozedur, die eine Tabelle name als Eingabe und erstellt die updates auf dieser Grundlage durch die Untersuchung der Struktur einer Tabelle im dictionary und Gebäude die updates on-the-fly. Wäre es nicht trivial, sich vor der Entwicklung, aber viel weniger Wartung in der Zukunft, denn wenn die Struktur einer Tabelle ändert, wird der code würde spüren, dass und anzupassen. Würde diese Methode funktioniert für jede Tabelle, die Sie interessieren, es zu benutzen mit.

Die einzige Zeit, die ich empfehlen könnte, dass historische Belege werden in der gleichen Tabelle gespeichert wie die "aktuelle" Datensätze ist, wenn FK links zu den Aufzeichnungen müssen oder benötigen könnten, mit Ihnen zu verbinden. Z. B. eine Anwendung, die ich gesehen habe hatten einige FK links, würde der link zu der Aufzeichnung als ein "point in time", das heißt, wenn der Datensatz aktualisiert wurde, wird der FK würde noch den link zu den historischen Rekord - das war ein wichtiger Teil des design-und Trennung-Geschichte Datensätze in einer zweiten Tabelle haben würde, machte es eher unhandlich.

Abgesehen davon, würde ich es vorziehen, dass ein Unternehmen die Voraussetzung für die Verfolgung aller änderungen, die gelöst werden soll mit einer eigenen "Geschichte" Tabelle für jede Tabelle. Sicher, es bedeutet mehr, DDL -, aber es vereinfacht den code der Anwendung enorm und Sie werden auch profitieren von besserer performance und Skalierbarkeit.