Laden...

Update und Delete auf denselben Datensatz in einer Transaktion

Erstellt von Paschulke vor 6 Jahren Letzter Beitrag vor 6 Jahren 2.552 Views
P
Paschulke Themenstarter:in
69 Beiträge seit 2011
vor 6 Jahren
Update und Delete auf denselben Datensatz in einer Transaktion

verwendetes Datenbanksystem: SQL Server 2008 R2 über Entity Framework 6.1

Hintergrund:
Wir möchten protokollieren, welcher Benutzer eine Änderung an einer Tabelle durchgeführt hat.
Dazu besitzen unsere Tabellen ein Feld "NutzerLetzteAenderung" in die die Id aus der Nutzertabelle eingetragen wird.
Alle Tabellen besitzen zudem einen Trigger, der diesen Wert benutzt, um nach jeder Änderung in einer Protokolltabelle festzuhalten, welcher Benutzer wann etwas geändert hat.

Ist vielleicht nicht die eleganteste Lösung, funktioniert aber so weit ganz gut.

Problem:
Beim Löschen steht natürlich nicht der Benutzer in der Tabelle, der das Löschen ausgelöst hat, sondern der, der zuletzt etwas an dem Datensatz geändert hat.

Das Setzen der NutzerId in das Feld "NutzerLetzteAenderung" erfolgt vor dem SaveChanges des DbContexts. An dieser Stelle wollte ich vor dem Löschen ein Update auf den Datensatz einfügen (siehe Code). Leider optimiert EF offensichtlich die Operationen, sodass das "überflüssige" Update nicht ausgeführt wird.

Wichtige Zusatzinfo: Es existiert eine Transaktion, die die Änderungen mit einem Commit abschließt.

Frage: Ist es möglich EF dazu zu bringen, das Update vor dem Delete auszuführen?

Hier der Code, wie er aktuell aussieht:


private void SetUserIdAndPerformUpdateBeforeDelete(DbContext dbContext)
{
    IList<DbEntityEntry> deletedEntities = dbContext.ChangeTracker.Entries()
        .Where(e => e.Entity is IUserEditableEntity && e.State == EntityState.Deleted)
        .ToList();

    if (deletedEntities.Any())
    {
        foreach (var entry in deletedEntities)
        {
            entry.State = EntityState.Modified;
            ((IUserEditableEntity) entry.Entity).NutzerLetzteAenderungId = _auditSessionData.UserId;
        }
        dbContext.SaveChanges();
        foreach (var entry in deletedEntities)
        {
            entry.State = EntityState.Deleted;
        }
        dbContext.SaveChanges();
    }
}
T
2.219 Beiträge seit 2008
vor 6 Jahren

Wenn der Eintrag gelöscht wird, wird er doch aus der DB entfernt.
Was bringt dir dann auch ein Update vorher?
Wird dort dann der Trigger ausgelöst, der einen Eintrag in eine History Tabelle schreibt oder was macht der Trigger?

Ich würde für solch eine Nachverfolgung direkt im Code einen Eintrag in eine History Tabelle mit gleichem Aufbau eintragen, egal bei welcher Operation.
Dies würde ich aber nicht über einen Trigger lösen, sondern direkt im Code abbilden.
Dies dürfte auch ein Update vor dem Delete ersparen, was auch nur nötig ist da du mit Triggern arbeitest.
Aber gerade für History Einträge würde ich keine Trigger verwenden, wenn ich dann mit Hacks wir Updats vor einem Delete arbeiten muss.
Ist kein sauberer Code und Entwicklern ohne Wissen über die Trigger können mit dem Code nichts anfangen.

Entsprechend solltest du deinen Code umbauen um das Problem im Code zu lösen und beim Löschein einen History Eintrag in eine History Tabelle zu schreiben.
Ist dann les- und zukünftig auch wartbarer.

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

16.806 Beiträge seit 2008
vor 6 Jahren

Es gibt Erweiterungsframework wie EF-Plus, die Auditing hinzufügen als Feature.
Da gibt es Pre-Actions auf den Context. Du könntest Dir da was abschauen.

Wenn Du die Möglichkeit aber hast, das Vorgehen und die Umsetzung zu ändern, dann tu das.
Ich versteh allein den Mehrwert nicht, wenn man nicht alle historischen Vorgänge hat im Sinne eines Audits, sondern nur den letzten.
In vielen rechtlichen Bereichen ist das auch überhaupt nicht zulässig (sofern das überhaupt für euch eine Rolle spielt / spielen könnte).

P
Paschulke Themenstarter:in
69 Beiträge seit 2011
vor 6 Jahren

Vielen Dank für Eure Antworten!

Wird dort dann der Trigger ausgelöst, der einen Eintrag in eine History Tabelle schreibt oder was macht der Trigger?

Genau!

Ich würde für solch eine Nachverfolgung direkt im Code einen Eintrag in eine History Tabelle mit gleichem Aufbau eintragen, egal bei welcher Operation.
Dies würde ich aber nicht über einen Trigger lösen, sondern direkt im Code abbilden.

Der Gedanke war, dass ein Datenbank-Trigger auch Änderungen protokolliert, die direkt auf der Datenbank erfolgen, damit auch dort niemand etwas unbemerkt manipullieren kann.
Dazu wird in der Protokolltabelle der Datenbank-User, der die Änderungen durchgeführt hat, mit protokolliert. Leider benutzen wir in der Anwendung technische User. Deshalb haben wir uns dieses Workarounds mit der zusätzlichen Spalte "NutzerLetzteAenderung" bedient, der uns mitteilt wer die Änderung durchgeführt hat.

Ich versteh allein den Mehrwert nicht, wenn man nicht alle historischen Vorgänge hat im Sinne eines Audits, sondern nur den letzten.

Die Trigger protokollieren jede Änderung in der Protokolltabelle. Die Spalte "NutzerLetzteAenderung" hilft uns lediglich die Information aus dem Programm in die Protokolltabelle zu transportieren.

Haltet ihr es für irrelevant die Änderungen, die direkt auf der Datenbank stattfinden, zu protokollieren? Das ist etwas, was bei uns bisher nicht diskutabel war. Aber vielleicht habt ihr ja gute Argumente... 😉

Ansonsten noch eine andere Idee: Der Trigger protokolliert ja, wie gesagt, auch den angemeldeten DB-Nutzer. Kann man bei der Anmeldung an den SQL Server irgendwelche Metadaten aus dem Programm mitgeben, die der Trigger auslesen könnte. Dann könnte ich ggf. hierüber den tatsächlichen Nutzernamen mitgeben.

Und einen "Schalter", der Entity Framework dazu bringt, auch unnötige Updates durchzuführen, gibt es definitiv nicht?

D
985 Beiträge seit 2014
vor 6 Jahren

Eine Datenbank sollte man nicht zur Betriebsh*re machen, wo jeder mal so daran herumfingern kann wie er lustig ist.

Aus diesem (guten) Grund werden Datenbanken mit einem Service gekapselt (vor eben diesen Fingern geschützt). Die Admin-Zugangsdaten zu dem Server (Service/Datenbank) selber kommen in einen versiegelten Umschlag und wenn das Siegel gebrochen ist, dann ist auch die Garantie flöten.

Ab jetzt gehen nur noch Zugriffe von der Anwendung (oder von wem auch immer) nur noch kontrolliert über den Service und der kann dann protokollieren.

Wenn es jetzt noch für irgendwelche Benutzer Bedarf an einem direkten Zugriff auf die Datenbank gibt, dann ausschließlich ReadOnly.

F
10.010 Beiträge seit 2004
vor 6 Jahren

Schön das Ihr alle immer garantieren könnt das niemand an der DB herummanipuliert der es nicht soll.

Unsere Kunden haben fast ausschließlich eigene SQL-Server, wo man uns erlaubt dann eine/mehrere DB's zu benutzen.
Und es ist schon oft vorgekommen das so ein Admin meinte da mal nachzuschauen ob er nicht den versehentlich gelöschten Datensatz wiederherstellen kann.

So etwas sieht man dann nur im Trigger ( wenn der Admin nicht weiß das da einer ist ).

T
2.219 Beiträge seit 2008
vor 6 Jahren

@FZelle
Dann besteht aber, wie Sir Rufo schon anmerkt, aber keine saubere Trennung zwischen der Anwendung samt deren Benutzern und den DB Benutzern.
I.d.R. sollte kein DB Benutzer an der DB rumspielen oder Daten "wiederherstellen".
Wenn möglich, sollten die Daten in der Anwendung dann neu erzeugt werden in sofern dies den möglich ist.

Wir nutzen bei uns in den Anwendungen auch extra Klassen/Ableitungen um diese History Tabellen ohne Trigger zu befüllen.
Wenn jemand direkt in der DB rumspielt, gibt es zwischen den IDs der Anwendungs- und DB Benutzern keine direkte Beziehung.
Diese kann dann nur indirekt hergestellt werden, weshalb die Trigger ihren nutzen teilweise verfehlen.
Auch wenn diese in diesem Zusammenhang wohl der "beste" Weg sind, halte ich den Ansatz für sehr gewagt.

Wer fremden Benutzern das rumspielen in der DB erlaubt, ignoriert auch die Rechte der DB Nutzer bzw. gibt ihnen sogar vorsätzlich das Recht etwas "kaputt" zu machen.
Gerade aus diesen Gründen sollte man Anwendungs- und DB Benutzern schon trennen und die Rechte der DB Nutzer soweit wie möglich einschränken, damit diese keine Daten anlegen/löschen/verändern können.

Wer dies vorsätzlich macht, muss dann eben mit solchen Problemen rechnen.
Diese zu umschiffen mit Triggern löst nicht das Ursprungsproblem bei DB Benutzern von falschen Rechten!

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

D
985 Beiträge seit 2014
vor 6 Jahren

Und wenn du dem Admin das erste Mal auf die Finger gehauen hast, dann weiß er beim nächsten Mal, was er wieder löschen muss und du schaust dumm aus der Wäsche.

Und dann?

Ich habe solche Diskussionen schon mehrfach geführt, auch mit Leuten, mit denen ich privat gut befreundet bin. Trotzdem lasse ich die nur per API in das System schreiben, denn nur dafür übernehme ich die Garantie - für sonst nix.

Ein bestehender SQL-Server ist ja schön und gut, aber jeder muss sich darüber im Klaren sein, dass man hier keine 100% Garantie übernehmen kann, weil man nicht 100% Kontrolle hat. Das ist aber eher ein organisatorisches und kein technisches Problem.

16.806 Beiträge seit 2008
vor 6 Jahren

....dann nimmt man ein SQL Feature namens Always Encrypted, entzieht dem Kunden bei Direktzugriff jegliche Gewährleistung.

Mit der Aussage, dass der Kunde doch hier immer alles direkt auf der DB machen will und man deswegen Logik in die DB packen muss; damit macht man es sich natürlich einfach.

P
Paschulke Themenstarter:in
69 Beiträge seit 2011
vor 6 Jahren

OK, ich habe hier hausintern nochmal über die Möglichkeit diskutiert, die Protokollierung komplett in die Anwendung zu verlagern. Das scheint nicht diskutabel zu sein. Eure Argumente sind sicherlich sinnvoll. Aber es müssten sehr viele Prozesse geändert werden, damit wir wirklich sagen könnten, dass jede Änderung auch ohne Trigger zuverlässig protokolliert wird.

Momentan habe ich deshalb keine andere Idee, als beim Löschen einfach aus der Anwendung heraus einen zusätzlichen Protokolldatensatz zu schreiben. Dann hätten wir bei jedem Löschen 2 Datensätze in der Protokolltabelle. Gefällt mir gar nicht, aber etwas anderes fällt mir nicht mehr ein.

D
985 Beiträge seit 2014
vor 6 Jahren

Es gibt natürlich noch die Möglichkeit statt dem Löschen den Datensatz per Flag als gelöscht zu markieren (also eben nicht wirklich löschen).

Wenn ihr eine ordentliche Trennung der Schichten habt, dann ist es auch kein Problem diese „gelöschten“ Daten aus den normalen Abfragen herauszuhalten und es sich trotzdem so anfühlt als ob die tatsächlich gelöscht sind.

T
2.219 Beiträge seit 2008
vor 6 Jahren

@Sir Rufo
Wäre dann auch die bessere Möglichkeit.
Man sollte nur diese Daten irgendwann, durch einen Task o.ä. dann auch tatsächlich löschen damit diese Altdaten nicht das System aufblähen.
Je nachdem wieviele Daten gelösscht werden, kann dies relativ schnell gehen.
Ebenfalls sollte man dann ggf. auch genutzt Indizies der Tabelle erweitern um nur die Einträge im Index zu halten, die nicht gelöscht sind.
Also ein Index mit entsprechender Bedingung wie Geloscht = false.
Dies hält dann die Indizies klein + man spart sich extra Suchen in der Tabelle wegen dem Gelöscht Flag des Eintrags.

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

D
985 Beiträge seit 2014
vor 6 Jahren

@T-Virus

Mit Partitions (eine für die aktiven, die andere für die gelöschten) auf den Tabellen fällt das nicht wirklich ins Gewicht.

D
985 Beiträge seit 2014
vor 6 Jahren

@Paschulke

Da ihr ja an dem aktuellen System festhalten wollt:

Eigentlich könnt ihr euch den ganzen Aufwand mit dem Update-Vor-Delete doch sparen, wenn ihr einen Delete-Trigger auf den Tabellen erstellt.

Der protokolliert dann auch sauber mit, wenn jemand „per Hand“ den Datensatz aus der Tabelle löscht.

Oder habe ich gerade einen Denkfehler?

P
Paschulke Themenstarter:in
69 Beiträge seit 2011
vor 6 Jahren

Eigentlich könnt ihr euch den ganzen Aufwand mit dem Update-Vor-Delete doch sparen, wenn ihr einen Delete-Trigger auf den Tabellen erstellt.
...

Das Problem ist, dass ich nicht weiß wer die Änderung durchgeführt hat, da der angemeldete DB-Nutzer ein technischer Nutzer ist. Deshalb benötige ich die Information aus der Spalte "NutzerLetzteAenderung", die mir den Bezug zum tatsächlichen Nutzer liefert. Und diese Information bekomme ich m. E. nur indem ich sie per Update vor dem Löschen in die Tabelle schreibe.