modify von inserted bei Triggern

Question

Hallo,

ich habe ein Frage zu Triggern.

Gegeben ist folgende Tabelle : CREATE TABLE xyz (nr integer primary key, id int, txt ntext(100))

Ich möchte erreichen, dass wenn ich ein SQL Insert durchführe und keine nr übergeben, ein Wert aus einer Funktion ([dbo].nextval())genommen wird. Diese bildet ein Autoincrement nach. Wird nr übergeben, muss diese verwendet werden.

Bisher bin ich soweit gekommen:

 

CREATE TRIGGER tr_xyz _nextid
ON xyz
FOR INSERT AS
  DECLARE @C INTEGER
  SELECT @C = nr FROM inserted;
  IF @C is null  
  BEGIN  
    SET @C = [dbo].nextval('xyz');
    UPDATE inserted SET nr = @C;
    SELECT * INTO #inserted FROM inserted;
    UPDATE #inserted SET nr = @C;
    INSERT INTO xyz SELECT * FROM #inserted;
  END
  ELSE
    INSERT INTO xyz SELECT * FROM inserted;          
GO

 

Allerdings mag SQL Server das nicht, weil ntext felder hier nicht unterstützt werden.

Die Eigenschaft identity kann ich nicht nehmen, da diese mit meinen vorhandenen Daten kollidieren würde. Im System wird teilweise die ID geholt bevor ein Datensatz eingefügt wird. Dies würde dann zu Problemen führen.

Inserted darf ich auch nicht verändern. Nachträglich kann ich den Datensatz auch nicht ändern, da ggf. nr nicht übergeben wurde und damit der primary key fehlerhaft ist.

Hat jemand eine Idee wie man das prakmatisch hin bekommt?

Danke

Gruß
Martin

Answer 1

CREATE TABLE xyz (nr integer primary key, id int, txt nvarchar(100))
GO

CREATE TRIGGER tr_xyz_ins ON xyz
INSTEAD OF INSERT
AS
  INSERT INTO xyz(nr,id,txt)
  SELECT nr=(CASE WHEN nr IS NULL THEN [dbo].nextval('xyz') ELSE nr END),
			id,
			txt
	FROM inserted
GO

---

Gruß Yury

Answer 2

Hallo Martin,

bei den Tabellen inserted und deleted handelt es sich um Pseudotabellen,
die keinen direkten Bezug auf die eigentliche Tabelle haben.
Sie werden je nach SQL Server Version aus dem Protokoll erzeugt (bis 2000)
bzw. über die Zeilenversionsverwaltung (ab 2005).
Und stellen eine Kopie der bereits in der Tabelle befindlichen Daten dar.
Änderungen daran haben keine Auswirkung auf die Tabelle selbst.

Zudem mußt Du beachten, dass ein Insert-Trigger für mehrere eingefügte Zeilen
nur einmal aufgerufen wird, siehe Grundlegendes zu DML-Triggern

Was Deine weiteren Bedenken angeht:
Kollisionen mit einem Primär- oder eindeutigen Schlüssel kann ein Trigger nicht richten,
da er in so einem Falle gar nicht erst zum Zuge kommt.
Einschränkungen (wie NOT NULL; CHECK, PRIMARY KEY, UNIQUE, FOREIGN KEY)
müssen bereits vorher erfüllt sein.

Wenn Du eine ID bereits vor der Anlage verwenden willst, so sollte die Anwendung
durchgäng so konzipiert sein, dass sie die NextVal durchgängig verwendet,
um die ID zu erhalten. Und Fehler bei der Vergabe würden wie jeder andere behandelt.

Eine Alternative wären INSTEAD OF Trigger , die aber ebenfalls Einschränkungen
haben und IMO keine allgemeine Lösung für das Problem sind.

Gruß Elmar

 

Answer 3

Danke erstmal. Auch an Yury. Allerdings muss die Lösung so flexibel sein, dass ich jede beliebige Tabelle die das Feld Nr beinhaltet darüber behandeln kann.

Hintergrund ist, ich habe eine Anwendung, die sowohl Oracle, als auch SQL Server bedienen soll. Das tut sie bisher auch ganz gut. Es gibt eigene Parserklassen, die die SQL Statements umschreiben um den verschieden SQL Dialekten gerecht zu werden. Leider sind die Klassen in Delphi und mit der Umstellung auf C# benötigen wir eine neue Lösung. Und hier möglichst eine, bei denen nicht so viel Aufwand getrieben werden muss. Zur Abfrage der Daten könnte man ORM wie Linq oder ähnliches verwenden. Leider hab ich ein Problem mit dem einfügen.

Die oben genannte Funktion habe ich bereits bei Oracle als Trigger implementiert und sie funktioniert gut. Mit der Einschränkung, dass ich nur ein Zeile wirksam einfügen kann, könnte ich auch leben.

Vielleicht gibt es ja auch einen anderen Ansatz.

Wie gesagt, das Feld Nr als primary key muss optional sein. Wird dies nicht übergeben, muss der wert generiert werden.

danke nochmal

Gruß

Martin