Transaktionen (SQL Server)

Allgemein:
Eine Transaktion kann man als eine Arbeitseinheit betrachten, die ihre eingene Fehlerbehandlung und Fehlerroutine enthält.
Tritt während einer Transaktion kein Fehler auf, werden alle Datenänderungen übernommen und in der Datenbank gespeichert,
ansonsten (Fehlerfall) werden die Daten nicht geändert.
Wenn Sie Transaktionen verwenden, sollten Sie darauf achten, dass diese sehr kurz gehalten werden, da die DBMS sehr viele
Ressourcen dafür bereitstellen muss. Der Ablauf einer Transaktion erfolgt über mehrere Schritte. Als erstes prüft die Transaktion
die Konsistenz der Datenbank, danach werden die Daten tabellenweise! geändert. In diesem Stadium kann sich die Datenbank kurzzeitig
in einer inkonsistenten Lage befinden. Sind alle Daten erfolgreich geändert, und ist die Datenbank wieder in einem konsistenten Zustand
werden diese Änderungen dauerhaft in der Datenbank gespeichert.

Die drei wichtigsten Befehle für eine Transaktion sind:
BeginTrans, Commit und Rollback

Hier gibt es noch 3 (wichtige!) Unterschiede:
Autocommit-Transaktionen - Standardmodus
Explizite Transaktionen - BeginTrans, Commit und Rollback
Implizite Transaktionen - neue Transaktion wird gestarten, wenn die alte abgeschlossen ist
Je nach Anforderung ist es dem Programmierer überlassen, welche Transaktion er wählt.

In diesem Beispiel wird die explizite Transaktion verwendet.

Hier wird mit Hilfe des ADO - Command - Objektes in Visual Basic eine Stored Procedures aufgerufen, die eine Abfolge von
zwei anderen Procedures (INSERT, UPDATE) beinhaltet:

Dim cdA As ADODB.Command
Private Sub cmdStart_Click()
    Dim sCn As String
    Dim iErr As Integer

        On Error Goto Err_Trans

        Set cdA = New ADODB.Command

        sCn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=MyDB;Data Source=MySource"
        cdA.ActiveConnection = sCn
        cdA.CommandType = adCmdStoredProc

        cdA.CommandText = "sp_trans_dxd"

        cdA.Parameters.Append _
        cdA.CreateParameter("@F1", adInteger, adParamInput, 50, 2001)
        cdA.Parameters.Append _
        cdA.CreateParameter("@F2", adVarChar, adParamInput, 50, "Strasser")
        cdA.Parameters.Append _
        cdA.CreateParameter("@F3", adVarChar, adParamInput, 50, "Shinja")
        cdA.Parameters.Append _
        cdA.CreateParameter("@out1", adInteger, adParamOutput, 50, 0)

        cdA.Execute

        iErr = cdA.Parameters(3)
        'FEHLERBEHANDLUNG TRANSAKTION

End_Trans:
'FINALCODE
Exit Sub
Err_Trans:
'FEHLERBEHANDLUNG VB
Resume End_Trans
End Sub

Der dritte Parameter (Output-Parameter s. sp_trans_dxd) enthält eine Fehlerkonstante (hier 1 oder 0) mit der man
eine Fehlerbehandlungsroutine anstossen kann.

Die Procedure sp_trans_dxd enthält eine Update und eine Insert Anweisung, die in den Procedures
sp_TESTUPDATE , sp_TESTINSERT codiert wurden.

CREATE PROCEDURE sp_trans_dxd
( @f1 int,
@f2 varchar(50),
@f3 varchar(50),
@out1 int output
)

as
declare @err_update int
declare @err_ins int

begin tran go_update

  exec sp_TESTUPDATE @f1 , @f2 , @f3
  set @err_update = @@error

  exec sp_TESTINSERT 1, 'A' , 'B'
  set @err_ins = @@error

if @err_update = 0 and @err_ins = 0
               begin
   commit tran go_update
   set @out1 = 0
  end
else
  begin
   rollback tran go_update
   set @out1 = 1
  end

return 0
GO

Tritt hier ein Fehler auf wird in das VB - Programm der Wert 1 als dritter Parameter (output-Parameter) übergeben.
Wenn alles ordnungsgemäss ohne Fehler ausgeführt wird, übergibt die Procedures an das VB - Programm den Wert 0.
Hier könnte man auch den Fehlercode aus @@error übergeben: set @out1 = @@error

Die Procudure sp_TESTUPDATE aktualisiert Datensätze, die den Wert 2001 im Feld F1 enthalten.

CREATE PROCEDURE sp_TESTUPDATE
( @f1 int,
@f2 varchar(50),
@f3 varchar(50)
)
as
  update
   TESTTBL
  set
   F1 = @f1,
   F2 = @f2,
   F3 = @f3
  where
   F1 = 2001
GO

Die Procedure sp_TESTINSERT fügt einen neuen Datensatz in die Tabelle TESTTBL2 ein

CREATE PROCEDURE sp_TESTINSERT
(
@f1 int,
@f2 varchar(50),
@f3 varchar(50)
)
AS
INSERT INTO
  TESTTBL2 ( F1,
     F2,
     F3
    )
VALUES   ( @f1,
     @f2,
     @f3
    )
GO

Diese beinden Scripts erzeugen die Testtabellen TESTTBL und TESTTBL2:

CREATE TABLE [dbo].[TESTTBL]
(
[T_ID] [int] IDENTITY (1, 1) NOT NULL ,
[F1] [int] NOT NULL ,
[F2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[F3] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
)
ON [PRIMARY]
GO
CREATE TABLE [dbo].[TESTTBL2]
(
[T_ID] [int] IDENTITY (1, 1) NOT NULL ,
[F1] [int] NOT NULL ,
[F2] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[F3] [char] (10) COLLATE Latin1_General_CI_AS NULL
)
ON [PRIMARY]
GO

Um nun eine fehlgeschlagene Transaktion zu simulieren ändert man die Feldeigenschaft von T_ID in der Tabelle TESTTBL2 indem man
den IDENTITY (Autowert) herausnimmt.