Re: on delete set null
From: Elmar Boye (ElmarB_at_gmx.net)
Date: 03/11/05
- Next message: Philipp Schumann: "Re: on delete set null"
- Previous message: Philipp Schumann: "Re: on delete set null"
- In reply to: Philipp Schumann: "on delete set null"
- Next in thread: Philipp Schumann: "Re: on delete set null"
- Reply: Philipp Schumann: "Re: on delete set null"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 11 Mar 2005 23:53:49 +0100
Hallo Philipp,
Philipp Schumann <phil@mokka.org> schrieb ...
> leider mußte ich feststellen, daß der (mir ansonsten sehr
> sympathische) SQL Server ON DELETE SET (NULL|DEFAULT) bei einem
> FOREIGN KEY nicht unterstützt.
>
> Nun versuche ich das ganze mit einem Trigger zu simulieren (auf SProcs
> möchte ich unbedingt verzichten). Problem hierbei: meine ganze
> Architektur ist schon ziemlich darauf ausgerichtet, zusammenhängende
> SQL-Befehle als einen Batch-Befehl abzusetzen (ohne EXECUTE, USE, GO
> etc., eher simpel mit dem SqlCommand in ADO.NET). SQL Server verlangt
> aber scheinbar, daß CREATE-TRIGGER-Anweisungen die ersten in einer
> Batch-Anweisung sind - so daß ich diesen nicht die entsprechenden
> CREATE TABLE voranstellen kann, in denen die Tabellen erzeugt werden,
> auf die sich der Trigger beziehen soll.
Soviel zu den erwähnten in der .NET NG erwähnten Kleinigkeiten...
> Muß ich etwa allen Ernstes deswegen zwei Befehle einzeln
> hintereinander absetzen?
... und einfache Architektur ist leichter gesagt als getan...
Der SQL Server möchte einen eigenen Batch haben, da gilt im übrigen
für CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE,
CREATE TRIGGER und CREATE VIEW - siehe "Batches" in der SQL Server
Dokumentation.
Praktisch sollte man allerdings jede Anweisung in einen eigenen Batch
packen, also für dort unten jedes CREATE TABLE und das CREATE Trigger
-> 3 * ExecuteNonQuery() ist besser.
Denn so funktioniert es auch bei einem Nicht-Batch-fähigen Treiber
(wie z. B. Jet/Access) durchgängiger.
> Warum?
Da diese Befehle mehrere SQL Anweisungen enthalten können.
Und der Parser eben keine ";" suchen will ;-).
Lösung sind also mehrere ExecuteNonQuery()
Und für die Architektur: In den SQL Text wie beim SQL Server,
die zu trennenden Batches in der Datei mit "GO" kennzeichnen.
Und dies via StreamReader trennen lassen - kleines C# Beispiel
unten dran.
Eine "richtige" Implementation könnte natürlich auch das ";"
verwenden, müsste aber auch den Inhalt parsen (um Semikola in
Zeichenketten aus dem Weg zu gehen).
> Oder hab ich einen Fehler drin
> irgendwo, und SQL Server weigert sich lediglich, mir diesen genauer
> zu erläutern?
>
> CREATE TABLE T (
> ID UNIQUEIDENTIFIER PRIMARY KEY);
>
> CREATE TABLE C(
> ID UNIQUEIDENTIFIER PRIMARY KEY,
> Foo UNIQUEIDENTIFIER,
> FOREIGN KEY (Foo) REFERENCES T (ID));
> -- (normally, ON DELETE SET NULL would be sufficient here...) :(
>
> CREATE TRIGGER TEST ON T FOR DELETE AS UPDATE C SET Foo = NULL FROM C
> AS CC JOIN T AS TT ON CC.Foo = TT.ID;
Und eine Mischung von FOREIGN KEY und Trigger funktioniert architektonisch
nicht. Denn der FOREIGN KEY wird immer zuerst geprüft und ist die dadurch
definierte Bedingung verletzt, wird ein Fehler erzeugt.
Der Trigger Code kommt gar nicht mehr zum Zuge, vergleiche:
INSERT INTO T (ID) VALUES (@guid)
INSERT INTO C (ID, Foo) VALUES (NEWID(), @guid)
DELETE FROM T WHERE ID = @guid
Lösung wäre hier: Alles via Trigger und der DELETE Trigger sollte
eher so aussehen:
CREATE TRIGGER TEST ON dbo.T
FOR DELETE
AS
SET NOCOUNT ON
UPDATE dbo.C
SET Foo = NULL
FROM dbo.C AS CC
INNER JOIN deleted ON CC.Foo = deleted.ID;
GO
Und das "dbo" sollte man nicht nur der Schönheit halber ansehen,
sondern in seiner Architektur berücksichtigen, das es Datenbanken
mit Besitzern/Schemata gibt und simple (wie die Jet) die das nicht
kennen. Tipp: unten etwas mehr einbauen und oben %SCHEMAOWNER%.C
schreiben.
Gruss
Elmar
--
public static void SqlExecuteScriptResource(
SqlConnection connection,
string resourceName)
{
System.Reflection.Assembly assembly = null;
System.IO.StreamReader streamScript = null;
try
{
assembly = System.Reflection.Assembly.GetExecutingAssembly();
streamScript = new System.IO.StreamReader(
assembly.GetManifestResourceStream(resourceName),
true);
SqlExecuteScriptStream(connection, streamScript);
}
finally
{
if (streamScript != null)
streamScript.Close();
}
}
public static void SqlExecuteScriptFile(
SqlConnection connection,
string scriptFilename)
{
// Optimist: Unicode oder ANSI (Windows) Codierung
System.IO.StreamReader streamScript = null;
try
{
streamScript = new System.IO.StreamReader(scriptFilename, true);
SqlExecuteScriptStream(connection, streamScript);
}
finally
{
if (streamScript != null)
streamScript.Close();
}
}
public static void SqlExecuteScriptStream(
SqlConnection connection,
StreamReader streamScript)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder(2048);
string line;
System.Data.ConnectionState IsClosed = connection.State;
try
{
if (IsClosed == System.Data.ConnectionState.Closed)
connection.Open();
try
{
while(streamScript.Peek() > -1)
{
line = streamScript.ReadLine();
if (String.Compare(line.TrimStart(), 0, "GO", 0, 2, true) == 0)
{
if (sb.Length > 0)
{
SqlExecuteBatch (connection, sb.ToString());
sb.Length = 0;
}
}
else
{
sb.Append(line);
sb.Append("\r\n");
}
}
if (sb.Length > 0)
SqlExecuteBatch (connection, sb.ToString());
}
catch (Exception e)
{
// Console.WriteLine(e.ToString());
throw e;
}
finally
{
streamScript.Close();
}
}
finally
{
if (IsClosed == System.Data.ConnectionState.Closed
&& connection.State == System.Data.ConnectionState.Open)
connection.Close();
}
}
public static int SqlExecuteBatch(
SqlConnection connection,
string CommandText)
{
int recordsAffected = 0;
if (CommandText == null || CommandText.Length == 0) return recordsAffected;
System.Data.ConnectionState IsClosed = connection.State;
try
{
if (IsClosed == System.Data.ConnectionState.Closed)
connection.Open();
using (SqlCommand cmd = new SqlCommand(CommandText, connection))
{
cmd.CommandTimeout = 0;
recordsAffected = cmd.ExecuteNonQuery();
}
}
catch(SqlException e)
{
// foreach (SqlError err in e.Errors)
// Console.WriteLine ("Zeile {0}, Fehler {1}: {2}", err.LineNumber, err.Number, err.Message);
throw e;
}
finally
{
if (IsClosed == System.Data.ConnectionState.Closed
&& connection.State == System.Data.ConnectionState.Open)
connection.Close();
}
return recordsAffected;
}
- Next message: Philipp Schumann: "Re: on delete set null"
- Previous message: Philipp Schumann: "Re: on delete set null"
- In reply to: Philipp Schumann: "on delete set null"
- Next in thread: Philipp Schumann: "Re: on delete set null"
- Reply: Philipp Schumann: "Re: on delete set null"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
