Trigger, der abhängige Zeilen der gleichen Tabellen ändert, wenn sich bestimmte Felder ändern

Question

Hallo,

ich habe eine Tabelle, in der es Datensätze gibt, die auf einen anderen Datensatz der Tabelle referenzieren - Spalte RefID beim Ziel NULL bei den referenzierenden mit der ID des Ziels befüllt. Jetzt hat sich die fachliche Notwendigkeit ergeben die so voneinander abhängigen Datensätze bezüglich bestimmter Spalten synchron zu halten. Das wiederum auch nur, wenn sich in den bestimmten Spalten bestimmte Änderungen ergeben.

ID - PK
RefID - Referenz auf ID
ColA varchar -> eine Änderung des Wertes von WertA auf WertB oder von WertB auf WertA muss synchroniert werden, Änderungen von oder zu WertC dürfen nicht synchronisiert werden
ColB bit NOT NULL -> Änderungen müssen syncronisiert werden
ColC bit NOT NULL -> Änderungen müssen synhronisiert werden, wenn einer der zusammengehörigen Datensätze in ColA den WertC beinhaltet, muss die Änderung verhindert werden

Es ist möglich, dass der referenzierte oder der referenzierende Datensatz geändert werden
Es gibt auch Datensäte ohne anhängende andere Datensätze

Das Ganze müsste per Trigger gelöst werden - leider habe ich bisher keinen vielversprechenden Ansatz gefunden. Problematisch ist aus meiner sicht vor allem das Konstrukt mit ColA und wie man ohne globale Flags zu setzen die Rekursion des Triggers verhindert.

Danke im Voraus
Andreas

Answer 1

hi,

Nimm einen INSTEAD OF UPDATE-Trigger.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 2

ColB bit NOT NULL -> Änderungen müssen syncronisiert werden
ColC bit NOT NULL -> Änderungen müssen synhronisiert werden, wenn einer der zusammengehörigen Datensätze in ColA den WertC beinhaltet, muss die Änderung verhindert werden

Hallo Andreas,

ich versuche gerade mal ein Script für diese Aufgabenstellung - aber die beiden obigen Punkte verstehe ich - auch bei tieferer Sinnbefreiung - nicht wirklich! Was meinst DU mit WertA, WertB und WertC. Sind das "konstante" Werte und keine anderen Werte sind möglich?

---

Uwe Ricken

MCIT Database Administrator 2005
MCIT Database Administrator 2008
MCTS SQL Server 2005
MCTS SQL Server 2008, Implementation and Maintenance
db Berater GmbH
http://www-db-berater.de

Answer 3

hi,

das hilft mir irgendwie nicht wirklich weiter.

Im Grunde fhlt mir in diesem Fall die grundlegende Vorgehensweise

Die Probleme im einzelnen sind ja:

- Zeilenweise Verarbeitung (da ich für jeden Datensatz einzeln feststellen muss welche Änderungen durchgeführt wurden und welche davon weiterzugeben sind)
- Prüfung der Änderungen
- Verhindern/Zurücksetzen des Speicherns in dem einen beschriebenen Fall
- Rekursion verhindern (der Endlosfall dürfte ja erledigt sein, wenn man die Prüfungen der Spaltenänderungen um zu prüfen, ob bzw. wie und welche Datensätze angepasst werden müssen...)

Die Tabelle hat natürlich noch jedem Menge weiterer Spalten. Um betroffene Datensätze leichter zu erkennen könnte ich die RefID auch bei der Haupt-Zeile setzen...

Answer 4

Rekursive Trigger sind anscheinend doch global abgeschaltet also schon mal ein Problem weniger

Answer 5

Beantworte doch bitte mal meine Frage! Eventuell kann man dann ja doch helfen.

Es geht nicht um die anderen Spalten - sondern es geht "mir" um die Werte A B C...

---

Uwe Ricken

MCIT Database Administrator 2005
MCIT Database Administrator 2008
MCTS SQL Server 2005
MCTS SQL Server 2008, Implementation and Maintenance
db Berater GmbH
http://www-db-berater.de

Answer 6

hi,

die Vorgehensweise ist simple, wobei ich Uwe recht geben muss, deine Beschreibung ist schon arg schwammig.

Eine einfacher Ansatz, der nicht alles umsetzt, nimm's als Denkanstoss:

USE tempdb ;
GO

CREATE TABLE Hierarchy
  (
   ID INT NOT NULL
       PRIMARY KEY ,
   RefID INT NULL ,
   ColA VARCHAR(32) NULL ,
   ColB BIT NOT NULL ,
   ColC BIT NOT NULL ,
   CONSTRAINT FK_SelfRef FOREIGN KEY ( RefID ) REFERENCES Hierarchy ( ID )
  ) ;
GO

CREATE TRIGGER tr_Hierarchy ON Hierarchy
  INSTEAD OF UPDATE
AS
  SET NOCOUNT ON ;

  DECLARE @ID INT ;
  SELECT @ID = ID
  FROM  Deleted ;

  WITH  cte
       AS ( SELECT  A.*
          FROM   Hierarchy A
          WHERE  ID = @ID
          UNION ALL
          SELECT  C.*
          FROM   Hierarchy C
              INNER JOIN cte P ON P.ID = C.RefID
         )
    UPDATE H
    SET   ColA = ( SELECT ColA
             FROM  Inserted
            )
    FROM  Hierarchy H
        INNER JOIN cte ON cte.ID = H.ID
    WHERE  H.ColA IN ( 'A', 'B' ) ;
GO

INSERT INTO Hierarchy
VALUES ( 1, NULL, 'Root', 0, 0 ),
    ( 2, 1, 'A', 0, 0 ),
    ( 3, 1, 'B', 0, 0 ),
    ( 4, 1, 'C', 0, 0 ),
    ( 5, 1, 'D', 0, 0 ),
    ( 6, 2, 'E', 0, 0 ),
    ( 7, 3, 'F', 0, 0 ),
    ( 8, 2, 'A', 0, 0 ),
    ( 9, 3, 'B', 0, 0 ),
    ( 10, 8, 'B', 0, 0 ) ;

UPDATE Hierarchy
SET   ColA = 'B'
WHERE  ID = 2 ;

SELECT *
FROM  Hierarchy ;
GO

DROP TABLE Hierarchy ;
GO

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 7

Hallo Uwe,

meine Antwort war auf den ersten Hinweis von Stefan gemeint, deinen eintrag hatte ich da irgendwie übersehen... Sorry.

Also WertA, WertB und WertC sind die derzeit fachlich möglichen Werte für die Spalte ColA. Ich habe das Ganze mit neutralen Spaltennamen und Inhalten beschrieben, um die fachlichkeit außen vor zu lassen. Dabei war ich anscheinend nicht deutlich genug.

Also es gibt die drei Spalten ColA (varchar - eigentlich nur drei Werte), ColB (bit), und ColC (bit).
Diese müssen zwischen den Verknüpften Zeilen eigentlich synchron gehalten werden und die weiterzugebende Änderung kann sowohl die "Parent" als auch die "Child" Zeile treffen.

Es kommen halt nur noch ein paar fachliche besonderheiten dazu, die das Ganze noch verkomplizieren.

Bezüglich ColA sind das:
Änderung des Wertes -> gewünschte Reaktion des Triggers
WertA zu WertB -> Setzen der verknüpften Datensätze WertB in ColA
WertB zu WertA -> Setzen der verknüpften Datensätze WertA in ColA -> Es sei den es gint schon einen beteiligten DS mit WertC, dann muss ein Fehler erzeugt werden oder die Änderung Zurückgesetzt werden
WertA zu WertC -> Fachlich nicht möglich, daher keine Reaktion
WertB zu WertC -> Fachlich keine Weitergabe der Änderung gewünscht

Da die Zeilen beim INSERT immer mit WertA angelegt werden, ist die Spalte ColA bei zusammengehörigen Datensätzen also immer wie folgt befüllt: Alle WertA oder alle WertB oder (Einige WertC einige WertC)

Bezüglich ColC
Die Änderung des bit-Feldes soll immer weitergegeben werden

Bezüglich ColC
Die Änderung des bit-Feldes soll immer weitergegeben werden, es sein denn irgendeiner der beteiligten Datensätze hat in Spalte ColA den WertC, dann muss ein Fehler erzeugt werden oder die Änderung Zurückgesetzt werden

@Stefan: danke für deinen Lösungsvorschlag - den schaue ich mir jetzt erstmal an, mal sehen ob er weiterhilft

 

 

Answer 8

Zusatzinformation zum Verständnis:
Es ahndelt sich dabei nicht um eine tiefer verschactelte Hierarchie sondern es gibt nur zwei Ebenen normaler Eeintrag - und dazu gibt es entweder keine oder genau zwei zugeordnete Einträge

Das Ganze müsste natürlich auch multiple-updates abdecken - und sollte nur wirklich in den entsprechenden Feldern geänderte Datensätze berücksichtigen...

Answer 9

Hallo as79

as79 wrote:

Im Grunde fhlt mir in diesem Fall die grundlegende Vorgehensweise

Grundsätzlich: Wenn ein Trigger mehrere Abhängige Zeilen in der gleichen Tabelle ändern soll, liegt es nahe, dass das Datenmodell nicht optimal gewählt wurde. Diese voneinander abhängigen Zeilen/Attribute gehörten vermutlich in eine Child Tabelle.

In Deinem Fall würde ich auf direkte Updates verzichten und statt dessen eine Stored Procedure verwenden, die für die Updates aufgerufen wird. Dort kannst Du dann solche komplexen Dinge einfach erledigen und weisst zudem immer genau, wo was abläuft.

Gruss
Henry

Answer 10

Da die anderen ca 25. Spalten übereinstimmen, denke ich schon, dass die Daten in die gleiche Tabelle gehören. Die Daten sind halt nur fachlich voneinander abhängig - es geht ja auch nicht darum die 3 Spalten genau identisch zu halten... Eine grundlegende Änderung des Datenmodells und eine Umstellung auf Stored Procedure ist in diesem Falle nicht (oder nur mit massivstem Aufwand) möglich und schließt sich daher aus.

Answer 11

hi,

Die Übereinstimmung von Spalten ist kein Anzeichen dafür, das Daten in die selbe Tabelle gehören.

Tabellen stellen entweder Entitäten oder Relationen dar. Deine "übereinstimmenden Spalten" gehören nur dann in eine Tabelle, wenn die Daten zur gleichen Entität oder Relation gehören - "gleich" im Sinne von Identität.

Du sagst: Die Daten sind halt nur fachlich voneinander abhängig.
Gerade darum geht es in der Datenbanktheorie, wenn ein normalisiertes Model erstellt wird. Es sollen ja gerade kein funktionalen Abhängigkeiten in ein und derselben Tabelle gespeichert werden. Da dies unweigerlich zu Anomalien führt.

Just My 2 Cent...

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 12

Vielleicht habe ich das mit den Spalten etwas misverständlich ausgedrückt, aber es sind die gleichen Entitäten und mir sind die Grundlagen der Datenbankmodellierung durchaus bekannt. Ich möchte hier auch keine Grundsatzdiskussionen über Datenbankmodellierung führen - die bringen mich auch nicht weiter.

Im Endeffekt ist klar, dass ich die von den Änderungen betroffenen Datensätze identifizieren muss und ggf. Änderungen nachzuvollziehen sind. Es dreht sich jetzt um die Frage, wie es möglich ist die spezifischen Änderungen am effizientesten aus INSERTED und DELETED zu extrahieren, die anzupassenden Datensätze zu identifizieren und die Ändeurngen dann mit den Sonderbedingungen weiterzugebenen und das möglichst Performant mit möglichst wenigen Statements.

 

Answer 13

hi,

Ja, was ist an meinem diesbezüglichen Beispiel das Problem?

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 14

z.B. 

- berücksichtigt das Beispiel nur eine Spalte
- funktioniert nur beim Uodate von einzelnen Datensätzen
- berücksichtigt nicht, die fachlichen Besonderheiten

Answer 15

Aha. Du willst also das ich mich da einarbeite, mir die notwendigen Transitionstabellen aus den Fingern sauge und das ganze ohne Tabellenerstellungsskripte mit korrekten Beispieldaten?

- berücksichtigt das Beispiel nur eine Spalte

Jo. Ist ja wohl nicht so schwere deine Logik hier einzubauen.

- funktioniert nur beim Uodate von einzelnen Datensätzen

Hier war zu diesem Zeitpunkt deine Beschreibung der Anforderungen nicht vollständig. btw, ist sie im übrigen immer noch nicht.

- berücksichtigt nicht, die fachlichen Besonderheiten

Wie gesagt, ohne Transitionstabellen macht das auch keinen Sinn, das ich mir da was ausdenke.

Sorry, auch wenn Montag ist, welcher Teil von Denkanstoss ist das Problem?

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 16

Ich habe jetzt mal was (nur für Spalte ColA) geschrieben - es basiert nicht auf dem vorgeschlagenen, bildet für die Spalte die Anforderungen ab. Ist das so anwendbar oder gibt es Kritik an der Vorgehensweise? Habe mich an Stefans Test-Tabellenvorschlag gehalten

CREATE TABLE Hierarchy
  (
   ID INT NOT NULL
       PRIMARY KEY ,
   RefID INT NULL ,
   ColA VARCHAR(32) NULL ,
   ColB BIT NOT NULL ,
   ColC BIT NOT NULL ,
   CONSTRAINT FK_SelfRef FOREIGN KEY ( RefID ) REFERENCES Hierarchy ( ID )
  ) ;
GO

CREATE TRIGGER tr_Hierarchy ON Hierarchy
  FOR UPDATE 
AS 
BEGIN
	SET NOCOUNT ON;

declare @colA VARCHAR(32);

-- Status Updates
	IF (UPDATE([ColA]))
	BEGIN
		Select Top 1 @colA = [ColA] from inserted;
		IF (@colA= 'WertA')
		BEGIN
		 -- Fehler werfen, wenn ColA auf WertA gesetzt werden soll wo referenzierte mit WertC vorhanden 
		 IF EXISTS (SELECT p.ID FROM DELETED d 
			INNER JOIN INSERTED i ON i.ID= d.ID			
			INNER JOIN Hierarchy p ON d.RefID = p.RefID AND p.[ColA] = 'WertC'
			WHERE d.[ColA] = 'WertB' AND i.[ColA] = 'WertA' AND d.ID!= p.ID)
		 BEGIN
			RAISERROR 53320 'Änderung nicht zulässig!'; 
			ROLLBACK TRANSACTION;
			RETURN;
		 END
		 ---- WertB -> WertA
		 UPDATE Hierarchy SET [ColA] = 'WertA'
		 WHERE ID IN (SELECT p.ID FROM DELETED d 
			INNER JOIN INSERTED i ON i.ID = d.ID
			INNER JOIN Hierarchy p ON d.RefID = p.RefID and p.[ColA] != i.[ColA]
			WHERE d.[ColA] = 'WertB' AND i.[ColA] = 'WertA' AND d.ID != p.ID)		 
		END --status WertA

		IF (@colA = 'WertB')
		BEGIN
		 -- WertA -> WertB
		 UPDATE Hierarchy SET [ColA] = 'WertB'
		 WHERE ID IN (SELECT p.ID FROM DELETED d 
			INNER JOIN INSERTED i ON i.ID = d.ID
			INNER JOIN Hierarchy p ON d.RefID = p.RefID and p.[ColA] != i.[ColA]
			WHERE d.[ColA] = 'WertA' AND i.[ColA] = 'WertB' AND d.ID != p.ID)
		END
	END
END
GO

INSERT INTO Hierarchy
VALUES ( 1, 1, 'WertA', 0, 0 ),
    ( 2, 1, 'WertA', 0, 0 ),
    ( 3, 1, 'WertA', 0, 0 ),
    ( 4, 4, 'WertA', 0, 0 ),
    ( 5, 4, 'WertA', 0, 0 ),
    ( 6, 4, 'WertA', 0, 0 ),
    ( 7, NULL, 'WertA', 0, 0 ),
    ( 8, NULL, 'WertA', 0, 0 ),
    ( 9, NULL, 'WertA', 0, 0 ),
    ( 10, NULL, 'WertA', 0, 0 ) ;

UPDATE Hierarchy
SET   ColA = 'WertB'
WHERE  ID = 2 ;

SELECT *
FROM  Hierarchy ;
GO

UPDATE Hierarchy
SET   ColA = 'WertA'
WHERE  ID = 2 ;

SELECT *
FROM  Hierarchy ;
GO

UPDATE Hierarchy
SET   ColA = 'WertB'
WHERE  ID = 2 ;
UPDATE Hierarchy
SET   ColA = 'WertC'
WHERE  ID = 2 ;

SELECT *
FROM  Hierarchy ;
GO

begin try

UPDATE Hierarchy
SET   ColA = 'WertA'
WHERE  ID = 1 ;

end try
begin catch
	SELECT 
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_MESSAGE() as ErrorMessage;
end catch

DROP TABLE Hierarchy ;
GO

 

Answer 17

hi,

Kannst du die Logik mal in eine Transitionstabelle packen? So sieht es zwar plausible aus, aber auch unübersichtlich.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 18

hi,

vielleicht stehe ich gerade auf dem Schlauch weil Montag ist, aber die Begrifflichkeit Transitionstabelle sagt mir gerade nix...

Answer 19

hi,

klassisch ist es ein Begriff aus der Automatentheorie, z.B. die Turing-Maschine oder Endliche Automaten:

http://ais.informatik.uni-freiburg.de/teaching/ws06/info1/material/15_turing/15_turing-4up-new.pdf

http://kontext.fraunhofer.de/haenelt/kurs/folien/Haenelt_FSA_Akzeptoren.pdf

http://www.kuertz.name/files/AutomatenLogikenSpiele.pdf

Und in deinem Beispiel: Eine saubere tabellarische Darstellung der Übergänge in Boolescher Schreibweise.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann