Datenlogik von Access in SQL übernehmen und damit Daten bearbeiten

Question

Hallo zusammen

Ich hoffe ich bin hier richtig. Erstmal zu meinen Fragen:

Ich habe auf einem SQL-Server eine Tabelle tbla mit den Spalten A, B, C, D und E. Ausserdem natürlich noch ein Primärschlüsselfeld und noch eine Spalte fb zum Verknüpfen. Siehe unten. Spalte E siehe weiter unten.
In allen Spalten sind Jahreszahlen drin (z.B. 1985, 2001 etc). In Spalte B steht immer eine Zahl, in A, C und D manchmal.

Jetzt habe ich eine weitere Tabelle tblb. Dort hat es (neben dem Primärschlüssel) ein weiteres Feld namens F, das immer gefüllt ist und Zahlen zwischen 1 und 999 drin stehen

Das sieht dann so aus:

select ta.A, ta.B,ta.c,ta.D, ta.E, tb.F
from tbla ta
inner join tblb tb on ta.fb = tb.pb

Spalte tbla.E wird mit dem Resultat folgender Prozedur gefüllt (von Access). Ich habe diese Logik im Access-FE bei Form_BeforeUpdate hinterlegt.

Function fctCalculate(A As Long, B As Long, C As Long, D As Long, F As Long) As Long

    Dim lngTriggerdatum As Long

10  If A <> 0 Then
20      lngTriggerdatum = A
30  ElseIf B <> 0 Then
40      lngTriggerdatum = Year(B)
50  ElseIf C <> 0 Then
60      lngTriggerdatum = C
70  Else
80      GoTo Exit_fctCalculate
90  End If

100 If D <> 0 Then
110     lngTriggerdatum = lngTriggerdatum + CLng(F) + CLng(D)
120 Else
130     lngTriggerdatum = lngTriggerdatum + CLng(F)
140 End If

150 fctCalculate = lngTriggerdatum

Exit_fctCalculate:

End Function

Jetzt kann es aber sein dass eine oder mehrere Zahlen in tblb.F ändert. In diesem Fall muss ich ja alle Daten von tbla.E neu berechnen, welche mit dem entsprechenden Datensatz tblb.pB verbunden sind.

Und hier stehe ich an. Mangels Wissen weiss ich nicht wie ich die Access-Funktion in SQL Abbilde und wie ich dann das Feld E berechnen kann

Ich hoffe ich habe meine Schwierigkeiten einigermassen verständlich erklärt. Sonst fragt nach, wo ich etwas verständlicher erklären kann.

Ich hoffe ihr könnt mir helfen. Schon 'mal vielen Dank.

---

Danke und Gruss Thomas

Answer 1

Ein haeufig empfehlenswerter Ansatz ist, diese Updates schon direkt innnerhalb einer Prozedur (und nicht ueber direkten Zugriff auf die Tabelle) abzubilden.

Das heisst die Berechnung wuerde innerhalb des Prozedurcodes laufen, und das entsprechende UPDATE dann auf die korrekten Tabellen und Spalten ausgefuehrt.

Eine Alternative, wenn man den Zugriiff ueber ausschliesslich so eine dafuer vorgesehene Prozedur ermoeglichen kann, waere der Einsatz eines Triggers, der dann beim Update auf Tabelle B Tabelle A updated. Empfehlenswert sind Trigger nur wenn es nicht anders geht.

Andreas

---

Andreas Wolter (Blog | Twitter)
Senior Program Manager SQL Server & Azure Security

MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

Answer 2

Du kannst es auch mit einer Passthru-Abfrage und einem Update-SQL durchführen:

update Tbla t1
set e = (
select
case
when a <> 0 then a
when b <> 0 then year(b)
:
:
end + f +
case when d <> 0 then 0 else f end
from tbla ta inner join tblb tb on ta.fb = tb.pb
)
where exists (select * from tblb tb where t1.fb = tb.pb)

Das ist jetzt nicht komplett, aber so ungefähr würde ich es tun.
Zusätzlich kann man die Where-Klausel auch noch

Trigger sind in SQL-Server nicht besonders empfehlenswert, da es eben seit über 30 Jahren immer noch keine Before-Trigger gibt und es bei Updates im After-Trigger zu unerwünschten Nebeneffekten kommen kann (Satzversionen).
Bei Datenbanken, die Before-Trigger haben (z.B. Oracle, DB2), sind solche Dinge dann ein Leichtes und geradezu das A&O von Geschäftslogik in der DB.

Answer 3

Es ist richtig, dass SQL Server keine BEFORE Trigger kennt. Es gibt aber neben den AFTER- auch INSTEAD OF Trigger. Groessere Probleme, die man nicht durch geschickten Code loesen kann, spielen eher nicht die Rolle.

Geschäftslogik via Trigger abzubilden ist in der Regel auch nicht empfehlenswert. Prozeduren ermoeglichen das, was man als BEFORE-Trigegr abbilden koennte und werden haeufig fuer Datennahe-Geschaeftlichloigik verwendet.

BEFORE-Trigger kommen bei Oracle meist zum Einsatz, wo es gilt "falsche Daten" zu verhindern. Dafuer kann man in SQL Server sogenannte CHECK CONSTRAINTS auch gut verwenden. Bei den Szenarien, die Abhaengigkeiten zwischen 2 Tabellen beinhalten kommen dann eben entweder AFTER-Trigger, INSTEAD OF-Trigger (Im Gegensatz zu BEFORE-Triggern auch auf Sichten anwendbar) oder eben Prozeduren zum Einsatz.

Constraints sind sehr effizient, aber nicht fuer alles ausreichend. Prozeduren sind dann das Mittel der Wahl, Trigger wie oben geschrieben nur unter Umnstaenden.

Andreas

---

Andreas Wolter (Blog | Twitter)
Senior Program Manager SQL Server & Azure Security

MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

Answer 4

Ansichtssache da Prozeduren insgesamt aufwändiger sind als eben Before-Trigger.
Trigger haben halt den Vorteil, dass man da nicht so ohne weiteres vorbei kommt während Prozeduren eben nicht aufgerufen werden müssen um Daten zu manipulieren.
Hier benötigt man dann Schutzrechte für die Tabellen und die Prozeduren dann höhere Rechte als der Aufrufer.
Also alles zusätzliche Aufwände um mal eben eine 2. Tabelle parallel mit fortzuschreiben.

Daher ist konsistente Geschäftslogik eben sicherer in Triggern als Prozeduren unterzubringen.
Gegen Constraints ist ja auch nichts einzuwenden.

Und Instead-Of ist wahrlich keine Alternative gegen die Einfachheit eines Before-Triggers, der sofort Zugriff auf die jeweilig benötigten Images (After-/Beforeimage) hat ohne noch mal weitere Abfragen an die Datenbank zu bemühen. Und der Insert/Update/Delete wird dann auch noch von der Datenbank gemacht und nicht vom Trigger initiiert.

Aber solche Diskussionen hatten wir hier schon längst.

Answer 5

...
1) Trigger haben halt den Vorteil, dass man da nicht so ohne weiteres vorbei kommt während Prozeduren eben nicht aufgerufen werden müssen um Daten zu manipulieren.

2) Hier benötigt man dann Schutzrechte für die Tabellen und die Prozeduren dann höhere Rechte als der Aufrufer.

3) Und Instead-Of ist wahrlich keine Alternative gegen die Einfachheit eines Before-Triggers, der sofort Zugriff auf die jeweilig benötigten Images (After-/Beforeimage) hat ohne noch mal weitere Abfragen an die Datenbank zu bemühen. 

4) Aber solche Diskussionen hatten wir hier schon längst.

zu

1) Das wollte ich oben auch damit ausgedrueckt haben

2) Ich bin mir nicht sicher, was hier "hoehere Rechte" gemeint ist.
Es gibt aber einen weiteren Unterschied zu Oracle, der ganz konkrete Auswirkungen auf Datenbank-Code hat: SQL Server kennt sogenannte Objekt-Besitzverkettung (beschrieben ua hier) Das bedeutet das man idealerweise ueberhaupt keine Rechte auf Tabellen direkt vergibt, sondern eben nur auf die Prozeduren. Bei Oracle funktioniert das eben so nicht. Dasher kann man nicht sagen, dass die Rechte bei SQL Server dann aufwaendiger waeren. Eher das Gegenteil ist der Fall, wenn man es entsprechend gut designt (siehe auch Schema-Design für SQL Server: Empfehlungen für Schema-Design mit Sicherheit im Blick)

3) INSTEAD OF-Trigger erlauben ebenso Zugriff auf die virtuellen Tabellen inserted und deleted, wie die AFTER-Trigger

4) Das ist moeglich. Wir verbieten Diskussionen ja auch nicht, sondern versuchen mit korrekten Informationen den Benutzern zu helfen

in diesem Sinne

Sehen wir mal, ob Thomas noch weitere Fragen zu diesem Thema hat

Andreas

---

Andreas Wolter (Blog | Twitter)
Senior Program Manager SQL Server & Azure Security

MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

Answer 6

Ganz vielen Dank erstmal für all Eure Antworten.

Ich vergass zu erwähnen dass die Neuberechnung der Daten oder Teile davon nur wenige Male im Jahr vorgenommen werden (ca. 4-6). Somit denke ich dass ein Trigger da überdimensioniert ist. Ausserdem sind es nur 1 oder 2 Personen, die die Daten in tblb.F ändern dürfen. Diesen Personen kann man dann auch die Verantwortung übergeben daran zu denken, eine Neuberechnung durchzuführen. 

Somit wäre aus meiner Sicht am ehesten der angefangene Code von bfuerchau am sinnvollsten. Allerdings erhalte ich da immer die Meldung dass "Nachricht 512, Stufe 16, Status 1, Prozedur fctUpdate, Zeile 3 [Batchstartzeile 0]
Die Unterabfrage hat mehr als einen Wert zurückgegeben. Das ist nicht zulässig, wenn die Unterabfrage auf =, !=, <, <=, > oder >= folgt oder als Ausdruck verwendet wird."

Ich habe es jetzt mal in eine Funktion einzufügen versucht:

ALTER proc [dbo].[fctUpdate] @a int, @c int, @d int
as
update Tbla
set tbla.E = (
select
case
when @a <> 0 then @a
when @c <> 0 then @c
when @d <> 0 then @d
end + ta.B +
case when @d <> 0 then + @d   end
+ tb.F
from tbla ta inner join tblb tb on ta.fb = tb.pb)

Vermutlich komplett falsch, oder? Wäre Toll, wenn ihr mir da noch weiterhelfen könntet.

---

Danke und Gruss Thomas

Answer 7

Dann hast du keine 1:1-Beziehung über den Join und must u.U. mit SUM(x) arbeiten.
Oder halt die On-Beziehung erweitern dass du nur eine 1:1 Beziehung hast.

Schau dir dazu mal das einfache Ergebnis eines 

select * from tbla ta inner join tblb tb on ta.fb = tb.pb

an, damit du siehst was ich meine.

Answer 8

Dann bleibt mir wohl nur die Variante Datensätze einzeln durchzugehen, oder?

Ich habe mich einmal mit einem Code mit Cursor versucht. Ich weiss dass die Performance dafür schlecht ist. Aber die Alternative wäre das Ganze ins Frontent (MS Access) was die Performance auch nicht erhöht. Und die zu bearbeitenden Datensätze sind <30'000.

Mein Code enthält aber noch Fragen und einen Fehler, den ich nicht wegkriege (siehe Kommentar im Code

Declare @WhichFieldTblB as int -- Diese Variable ist nur damit ich 'mal den Code testen kann
Declare @FieldA int, @FieldB int, @FieldC int, @FieldD int, @FieldID int
Declare @FieldF int

set @WhichFieldTblB = 4
Set @FieldF = (Select tb.F from tblb tb Where tb.pb = @WhichFieldTblB)

declare Update_Field cursor for select ta.pa, ta.A, ta.B, ta.C, ta.D from tbla ta where ta.fb = @WhichFieldTblB

open Update_Field
-- Wenn die Feldwerte NULL sind, was steht dann in den Variablen? Oder muss man die dann noch irgendwie umkonvertieren?
fetch next from Update_Field into @FieldID, @FieldA, @FieldB, @FieldC, @FieldD
while @@FETCH_STATUS = 0
begin
	update tbla
		set E=(
		select
			case
				when @FieldA <> 0 then @FieldA
				when @FieldC <> 0 then @FieldC
		end
		+ @FieldF + @FieldB
		case when @FieldD  <> 0, + @FieldD )	--Hier weiss ich nicht wie ich das @FieldD abfragen kann. Ich bekomme immer einen Syntaxfehler
	where tbla.pa = @FieldID

	fetch next from Update_Field into @FieldA, @FieldB, @FieldC, @FieldD

end
close Update_Field
deallocate Update_Field

Könnt ihr mir da weiterhelfen? Ganz herzlichen Dank.

---

Danke und Gruss Thomas

Answer 9

Da bist du ganz ab vom Schuss.
Innerhalb eines Updates kannst du keinen Cursor verwenden.

Analysiere noch mal dein Problem:

Du willst in TABLA das Feld E updaten mit Berechnungen aus TBLA und TBLB.
Da du aber keine eindeutige Beziehung zwischen den Tabellen hast, wäre jeder Update sinnlos, da ja nur der letzte gültig wäre.
Also benötigst du die Erkennung, welcher Satz aus TBLB eindeutig zu TBLA passt um mit dessen Inhalten upzudaten, also eine 1:1-Beziehung zu definieren.

Answer 10

Nun, vielleicht meinen wir das Selbe aber reden trotzdem nicht vom Gleichen. 

Meine relationale Datenbank ist meistens auf 1:n Beziehungen aufgebaut, so auch bei diesen beiden Tabellen. Ich kann nicht einfach plötzlich eine 1:1 Beziehung zwischen diesen Tabellen herstellen oder wüsste nicht, wie ich das bewerkstelligen sollte.

Deshalb benötige ich eine andere Art, mein Ziel zu erreichen. Und die Einzige, die ich sehe, ist, Zeile für Zeile abzuarbeiten, mit allen damit verbundenen Nachteilen. Ich weiss dass dies der Vorgehensweise von SQL widerspricht. Trotzdem finde ich meinen Versuch mit dem Curser nicht so sehr auf dem Holzweg, um die Spalte tbla.E wie gesagt Zeile für Zeile abzuarbeiten, auch wenn es so wie ich versucht habe nicht funktioniert

Also weiss ich mittlerweile was alles nicht geht (1:n passt nicht, ein Cursor passt nicht) aber mir ist immer noch nicht klar was denn eine denkbare Möglichkeit ist. Aber vielleicht nimmt sich nochmal jemand die Zeit um mir das näher zu bringen.

---

Danke und Gruss Thomas

Answer 11

Wenn die Beziehungen letztlich egal sind, kannst du einen Cursor mit Join machen:

Cursor:
select a, b, c, d, e, pa from tabla inner join tblb ....

Schleife:
Fetch ....
berechnen: @ergebnis = ....
update tbla set e = @ergebnis where pa=@pa

Die Berechnung findet mit den normalen Befehlen im SQL statt.
tbla.e wird dann halt n-Mal mit tblb verrechnet. Was halt in meinen Augen keinen Sinn macht.