Protokoll Trigger mit > 1 Datensätze

[mace]

Hallo zusammen!

Die Trigger machen mich kaputt.
Meine Aufgabe ist es, dafür zu sorgen, dass solch einer die Änderungen relevanter Felder einer Tabelle dokumentiert und in einer Protokolltab speichert.
Erst hatte ich damit angefangen, ihn so zu erstellen, das variabel die Felder erkennt + eingetragen werden. sah in etwa so aus, musste aber doch deutlich abgeändert werden
Damit konnt ich die Anforderungen dann aber schlussendlich nicht erfüllen und somit bin ich auf die konservative(?!) Methode umgestiegen und melde Feld für Feld an und dokumentiere es, wenn Änderungen vorhanden sind.

Sieht dann in etwa wie folgt aus:
(hab mal alle Stellen reinkopiert, ab dem rot gekennzeichneten wirds interessant)

SQL-Code:

ALTER TRIGGER [TR_Logxxx] ON [dbo].[xxx] 
FOR INSERT, UPDATE, DELETE 
AS

-- Felderdeklaration der Protokolltabelle für Insert, ausgenommen alter/neuer Wert
Declare @Username nvarchar(40)
Declare @UpdateDate datetime
Declare @TableName nvarchar(255)
Declare @Fieldname nvarchar(150)
.
.
.

-- statische Werte der Protokolltabellenfelder
Select @UserName = system_user
Select @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
.
.
.



-- Aktion bestimmen

if exists (select * from inserted)
begin
   if exists (select * from deleted)
   begin
      select @Aktion = 'Datensatz geändert'
      select @PersNr = deleted.PersID from deleted
      Select @ObjSchluessel = deleted.ID from deleted
      
      print @deleted
      print @Aktion

   end
   else
   begin
      select @Aktion = 'Datensatz eingefügt'
      Select @PersNr = inserted.PersID from inserted
      Select @ObjSchluessel = inserted.ID from inserted
   end
end
else
begin
   select @Aktion = 'Datensatz gelöscht.'
   select @PersNr = deleted.PersID from deleted
   select @ObjSchluessel = deleted.ID from deleted
end

select @deleted = inserted.geloescht from inserted
if (@deleted=1)
begin
   select @Aktion = 'Datensatz gelöscht'
end

ab hier wirds interessant

SQL-Code:

-- Deklaration der Felder
Declare @OldIDSeminar nvarchar(255)
Declare @OldAnmeldedatum nvarchar(255)
Declare @OldUnterlagenerledigt nvarchar(255)
Declare @OldTeilnehmerbestaetigung nvarchar(255)
Declare @OldBewirtschaftet nvarchar(255)
...
Declare @NewIDSeminar nvarchar(255)
Declare @NewAnmeldedatum nvarchar(255)
Declare @NewUnterlagenerledigt nvarchar(255)
Declare @NewTeilnehmerbestaetigung nvarchar(255)
Declare @NewBewirtschaftet nvarchar(255)
...

-- Zuweisen der alten Feldinhalte 
Select @OldIDSeminar = deleted.IDSeminar from deleted
Select @OldAnmeldedatum = convert(nvarchar, deleted.Anmeldedatum, 104) from deleted
Select @OldUnterlagenerledigt = convert(nvarchar, deleted.Unterlagenerledigt, 104) from deleted
Select @OldTeilnehmerbestaetigung = convert(nvarchar, deleted.Teilnehmerbestaetigung, 104) from deleted
Select @OldBewirtschaftet = deleted.Bewirtschaftet from deleted
...

-- Zuweisen der neuen Feldinhalte
Select @NewIDSeminar = inserted.IDSeminar from inserted
Select @NewAnmeldedatum = convert(nvarchar, inserted.Anmeldedatum, 104) from inserted
Select @NewUnterlagenerledigt = convert(nvarchar, inserted.Unterlagenerledigt, 104) from inserted
Select @NewTeilnehmerbestaetigung = convert(nvarchar, inserted.Teilnehmerbestaetigung, 104) from inserted
Select @NewBewirtschaftet = inserted.Bewirtschaftet from inserted
...

IF (@OldAnmeldedatum  <> @NewAnmeldedatum ) OR (@OldAnmeldedatum  is null and not @NewAnmeldedatum  is null) OR (not @OldAnmeldedatum  is null and @NewAnmeldedatum  is null)
   OR (not @OldIDSeminar is null and @deleted = 1)
begin
   Insert into tabProtokoll (Username, Logdate, OldValue, NewValue, TableName, FieldName, PersNr, ObjektSchluessel, Aktion, Maskenname)
   Values (Current_User, @UpdateDate, @OldAnmeldedatum , @NewAnmeldedatum , @TableName, 'Anmeldedatum', @PersNr, @ObjSchluessel, @Aktion, @Maskenname)
end

Einfach als Beispiel das Anmeldedatum genommen, dass wird dann für alle Felder gemacht.

Mein Problem ist dabei das Aktualisieren/Löschen mehrerer Datensätze.
Solang ich einen Datensatz inserten, updaten, deleten möchte alles einwandfrei.

Sobald es mehrere sind kommt:

Server: Nachr.-Nr. 512, Schweregrad 16, Status 1, Prozedur TR_Logxxx, Zeile 212
Die Unterabfrage gab mehr als einen Wert zurück. Das ist ungültig, wenn die Unterabfrage auf =, !=, <, <= , >, >= folgt oder als Ausdruck verwendet wird.
Die Anweisung wurde beendet.

Die Frage also, was macht er da, warum entsteht der Fehler und wie ist er zu beseitigen?

Viel Text, hoffentlich viel Hilfe, ich sag schonmal viel Danke! :D

Grüße,
mace

//Edit: Threadname geändert, um Missverständnisse zu vermeiden..

[Jelly]

Schickst due SQL Befehle an den Server, die mehrere Datensätze betreffen, so enthalten die im Trigger abgreifbare virtuelle Tabellen Deleted und Inserted mehrere Datensätze. Ein Befehl wie

SQL-Code:

Select @OldIDSeminar = deleted.IDSeminar from deleted

schlägt dann fehl, weil du nicht mehrere SeminarIds in einer int Variablen ablegen kannst. Du wirst also wohl oder über im Trigger mit Cursors arbeiten müssen, und jeden Datensatz in den beiden Tabellen einzeln durchlaufen müssen.

[mace]

Ok, das ist eine schnelle und gut erläuterte Antwort.
Nun stellt sich mir als "Neuling" die Frage, wie ich das mit Cursorn verwirklichen kann.
Wäre nett, wenn du da ein Programmbeispiel für mich aufs Brot schmieren könntest. :stupid:

[Jelly]

Könnte in etwa so aussehen. Ist jetzt einfach nur ein kopierter Trigger, aber es sollte das Arbeiten mit Cursor recht gut erläutern.

SQL-Code:

CREATE TRIGGER trManualID ON [dbo].[Property] 
FOR INSERT
AS
declare @ManualID int
declare @Conflicting int
declare @ID int

declare CR cursor for
select ID from Inserted

    open CR
    fetch next from CR into @ID

    while @@fetch_status = 0 BEGIN
        set @ManualID = (select ManualID from Inserted where ID = @ID)

        if not(@ManualID is null) BEGIN
             set @Conflicting = (select count(p.ID) as N from Inserted i left join Property p on p.ManualID=i.ManualID and p.ID <> @ID and p.ManualID = @ManualID)
             if @Conflicting > 0 begin
                update Property set ManualID = null where ID = @ID
             end
      end 
      fetch next from CR into @ID
   end

    close CR
    deallocate CR

[mace]

Ok, vielen Dank.
Werd ich mich nochmal durchkämpfen müssen. Der Sinn des Conflict-Abfangens ist mir noch nicht ganz ersichtlich bzw. vermut ich, dass ich das nicht brauchen werde, da ich ja nur dann updaten lasse, wenn eine Änderung stattgefunden hat.

Evtl. hilft ja schon die zurate Ziehung des eSQueL Buches zur Erläuterung. :)

[mace]

HILFE!

Der Trigger (im Anhang) funktioniert immernoch nicht!
An dieser Stelle:

Delphi-Quellcode:

DataSetSerienbriefeDokumente.Insert;
DataSetSerienbriefeDokumente.FieldByName('SerienbriefNameExtern').AsString:= cxTextEditName.Text;
DataSetSerienbriefeDokumente.FieldByName('AngelegtAm').AsDateTime:= Date();
...
DataSetSerienbriefeDokumente.Post;
briefID:= DataSetSerienbriefeDokumente.FieldByName('ID').AsInteger;  // => *1

DataSetSerienbriefeDokumente.Edit;
DataSetSerienbriefeDokumente.FieldByName('SerienbriefNameIntern').AsInteger:= briefID;  // Verküpfungsfeld zur Detailtabelle
DataSetSerienbriefeDokumente.Post;

leg also einen neuen Datensatz an und will an Punkt *1 die ID des aktuell angelegten Datensatzes ziehen, um diese an die Detailtabelle weiterzugeben. In dieser briefID steht aber die ID des letzten angelegten Datensatzes aus der Protokollierungstabelle.
Wenn ich in der Tabelle nachschau hab ich garkeine Eingabe in diesem Feld stehen also NULL.
In dem DataSet ist nur ein

SQL-Code:

SELECT * FROM tabSerienbriefe

.
Und bei jedem Post greift unten angehängter Trigger, mit dem es etwas zu tun haben muss!

Code:

Tabellenausschnitt:
ID______SerienbriefnameIntern__SerienbriefNameExtern
218......NULL..................TRIGGERTEST
219......NULL..................TRIGGERtest2
222......NULL..................TRIGGERTEST5

Meine Frage also: Warum wird da nicht der Wert dieser ID gespeichert von dem aktuell angelegten Datensatz?
Anmerkung: Als der Trigger in diesem Programmcode noch nicht existiert hat, hat es so immer fehlerfrei funktioniert!



// Anhang

SQL-Code:

CREATE TRIGGER [TR_LogtabSerienbriefe] ON [dbo].[tabSerienbriefe] 
FOR INSERT, UPDATE, DELETE AS 
Declare @Username nvarchar(40) 
Declare @UpdateDate datetime 
Declare @TableName nvarchar(255) 
Declare @Fieldname nvarchar(150) 
Declare @PersNr int 
Declare @ObjSchluessel nvarchar(50) 
Declare @ObjSchluesselBez nvarchar(50) 
Declare @Aktion nvarchar(50) 
Declare @Maskenname nvarchar(255) 
Declare @deleted bit 

Select @UserName = system_user 
Select @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114) 
Select @TableName = 'tabSerienbriefe' 
Select @Maskenname = 'Serienbriefmanager' 
-- Aktion bestimmen
if exists (select * from inserted) 
  begin 
     if exists (select * from deleted) 
        select @Aktion = 'Datensatz geändert' 
     else 
        select @Aktion = 'Datensatz eingefügt' 
  end 
else 
  Select @Aktion = 'Datensatz gelöscht.' 

Select @deleted = inserted.geloescht from inserted 
if (@deleted=1) 
  select @Aktion = 'Datensatz gelöscht' 

-- Variablen für alte und neue Feldinhalte der Tabelle anlegen
Declare @OldSerienbriefNameExtern nvarchar(255) 
Declare @NewSerienbriefNameExtern nvarchar(255) 
Declare @OldSeitenNummer nvarchar(255) 
Declare @NewSeitenNummer nvarchar(255) 

-- Testen auf Änderung, wenn ja - Cursor anlegen (für den Fall mehrerer Datensätze)
 IF (@Aktion = 'Datensatz eingefügt') OR (@Aktion = 'Datensatz geändert') OR (@Aktion = 'Datensatz gelöscht') 
  BEGIN 
    Declare iC CURSOR LOCAL FAST_FORWARD FOR 
    Select ID, SerienbriefNameExtern, SeitenNummer from inserted 
    OPEN iC 
    FETCH NEXT FROM iC INTO @ObjSchluessel, @NewSerienbriefNameExtern, @NewSeitenNummer
  END 
IF (@Aktion = 'Datensatz gelöscht.') OR (@Aktion = 'Datensatz geändert') OR (@Aktion = 'Datensatz gelöscht') 
  BEGIN 
    Declare dC CURSOR LOCAL FAST_FORWARD FOR 
    Select ID, SerienbriefNameExtern, SeitenNummer from deleted 
    OPEN dC 
    FETCH NEXT FROM dC INTO @ObjSchluessel, @OldSerienbriefNameExtern, @OldSeitenNummer
  END 

WHILE @@FETCH_STATUS = 0 
BEGIN 
  Select @OldSerienbriefNameExtern = deleted.SerienbriefNameExtern from deleted 
  Select @NewSerienbriefNameExtern = inserted.SerienbriefNameExtern from inserted 

-- Test auf Änderung dieses Feldes
  IF (@OldSerienbriefNameExtern <> @NewSerienbriefNameExtern) OR (@OldSerienbriefNameExtern is null AND not @NewSerienbriefNameExtern is null) 
    OR (not @OldSerienbriefNameExtern is null AND @NewSerienbriefNameExtern is null) OR (not @OldSerienbriefNameExtern is null AND @deleted = 1) 
  BEGIN 
   INSERT INTO tabtest (test) VALUES ( '1_ '+@ObjSchluessel) -- Test, welcher Wert hier steht. Es ist die richtige ID
   IF @Aktion = 'Datensatz gelöscht' 
     SET @NewSerienbriefNameExtern = NULL 
   Insert into tabProtokoll (Username, Logdate, OldValue, NewValue, TableName, FieldName, PersNr, ObjektSchluessel, Aktion, 
   Values (@UserName, @UpdateDate, @OldSerienbriefNameExtern, @NewSerienbriefNameExtern, @TableName, 'Serienbriefname', @PersNr, @ObjSchluessel, @Aktion, @Maskenname) 
   INSERT INTO tabtest (test) VALUES ( '2_ '+@ObjSchluessel) -- Test, welcher Wert hier steht. Es ist auch hier die richtige ID
  END 
-- Nächstes Feld zum Protokollieren
  Select @OldSeitenNummer = deleted.SeitenNummer from deleted 
  Select @NewSeitenNummer = inserted.SeitenNummer from inserted 

  IF (@OldSeitenNummer <> @NewSeitenNummer) OR (@OldSeitenNummer is null AND not @NewSeitenNummer is null) 
    OR (not @OldSeitenNummer is null AND @NewSeitenNummer is null) OR (not @OldSeitenNummer is null AND @deleted = 1) 
  BEGIN 
    IF @Aktion = 'Datensatz gelöscht' 
      SET @NewSeitenNummer = NULL 
     Insert into tabProtokoll (Username, Logdate, OldValue, NewValue, TableName, FieldName, PersNr, ObjektSchluessel, Aktion, Maskenname) 
    Values (@UserName, @UpdateDate, @OldSeitenNummer, @NewSeitenNummer, @TableName, 'SeitenNummer', @PersNr, @ObjSchluessel, @Aktion, @Maskenname) 
  END 
  IF (@Aktion = 'Datensatz eingefügt') OR (@Aktion = 'Datensatz geändert') OR (@Aktion = 'Datensatz gelöscht') 
    FETCH NEXT FROM iC INTO @ObjSchluessel, @NewSerienbriefNameExtern, @NewSeitenNummer 
  IF (@Aktion = 'Datensatz gelöscht.') OR (@Aktion = 'Datensatz geändert') OR (@Aktion = 'Datensatz gelöscht') 
    FETCH NEXT FROM dC INTO @ObjSchluessel, @OldSerienbriefNameExtern, @OldSeitenNummer 
  END 

-- Wenn Cursor erstellt wurde, hier wieder löschen
IF (@Aktion = 'Datensatz eingefügt') OR (@Aktion = 'Datensatz geändert') OR (@Aktion = 'Datensatz gelöscht') 
BEGIN 
  DEALLOCATE iC; 
END 
IF (@Aktion = 'Datensatz gelöscht.') OR (@Aktion = 'Datensatz geändert') OR (@Aktion = 'Datensatz gelöscht') 
BEGIN 
  DEALLOCATE dC; 

END