[MSSQL] Trigger voor bijhouden aantallen

[licensed]

Ik heb de volgende twee tabellen:

code:

---------------       ---------------
|   TabelA    |       |   TabelB    |
---------------       ---------------
| TabelAID    |---    | TabelBID    |
| AantalB     |   ----| TabelAID    |
---------------       | Waarde      |
                      ---------------

TabelA.TabelAID en TabelB.TabelBID zijn de primary keys.

Nu heb ik op TabelB de volgende 3 triggers gemaakt:

code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.TabelBDelete 
   ON  dbo.TabelB 
   AFTER DELETE
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE TabelA SET AantalB = AantalB - (SELECT Count(*) FROM Deleted) WHERE TabelAID IN (SELECT TabelAID FROM Deleted)
END
GO

code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.TabelBInsert
   ON  dbo.TabelB 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE TabelA SET AantalB = AantalB + (SELECT Count(*) FROM Inserted) WHERE TabelAID IN (SELECT TabelAID FROM Inserted)
END
GO

en

code:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.TabelBUpdate 
   ON  dbo.TabelB
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE TabelA SET AantalB = AantalB - (SELECT Count(*) FROM Inserted JOIN Deleted ON Inserted.TabelBID = Deleted.TabelBID) WHERE TabelAID IN (SELECT Deleted.TabelAID FROM Inserted JOIN Deleted ON Inserted.TabelBID = Deleted.TabelBID);
    UPDATE TabelA SET AantalB = AantalB + (SELECT Count(*) FROM Inserted JOIN Deleted ON Inserted.TabelBID = Deleted.TabelBID) WHERE TabelAID IN (SELECT Inserted.TabelAID FROM Inserted JOIN Deleted ON Inserted.TabelBID = Deleted.TabelBID);
END
GO

Het is dus de bedoeling om met die triggers het veld AantalB consistent te houden. Het lukt mij alleen niet om een goede update trigger te schrijven. Degene die hier boven staat klopt niet. Heeft iemand een suggestie over hoe ik dit aan kan pakken? Het is belangrijk om er rekening mee te houden dat er meerdere rijen in één transactie geupdate kunnen worden.

*edit: ook de insert en delete trigger blijken niet te werken bij het toevoegen of verwijderen van meerdere records met verschillende ID's uit tabelB.

[ Voor 4% gewijzigd door licensed op 26-01-2006 20:21 ]

[Annie]

Voorbeeld van de insert trigger. Niet getest, maar volgens mij moet dit wel (ongeveer ) kloppen.
De rest kan op dezelfde wijze worden opgelost.

SQL:

CREATE TRIGGER TabelBInsert ON TabelB
AFTER INSERT
AS
BEGIN
  SELECT TabelAID, 'Aantal' = COUNT(*) 
  INTO #temp FROM inserted GROUP BY TabelAID
  
  UPDATE TabelA
    SET AantalB = AantalB + t.Aantal
  FROM #temp t, TabelA a
  WHERE a.TabelAID = t.TabelAID
END

[licensed]

Ik ga dit principe even uitproberen. Bedankt alvast.

[Annie]

Ik kreeg nog een ander ideetje, dus heb m'n msde even aangezwengeld en heb nog wat geprutst. Dit werkte, behalve bij bij een update waarbij dezelfde waarde werd 'teruggeplaatst'. Damn

Heb het nu maar even opgelost met een extra tablevar en een sum van de eerste. Maar je zou natuurlijk ook de update twee keer kunnen uitvoeren: een keer voor positieve waarden en een keer voor negatieve waarden.

SQL:

CREATE TRIGGER TabelBCounter ON TabelB
AFTER DELETE, INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON

  DECLARE @counter TABLE (TabelAID INT, Aantal INT)
  DECLARE @amount TABLE (TabelAID INT, Aantal INT)

  INSERT INTO @counter (TabelAID, Aantal)
  SELECT TabelAID, 'Aantal' = COUNT(*) 
  FROM inserted GROUP BY TabelAID

  INSERT INTO @counter (TabelAID, Aantal)
  SELECT TabelAID, 'Aantal' = COUNT(*) * -1
  FROM deleted GROUP BY TabelAID
  
  INSERT INTO @amount (TabelAID, Aantal)
  SELECT TabelAID, 'Aantal' = SUM(Aantal)
  FROM @counter GROUP BY TabelAID

  UPDATE TabelA
    SET AantalB = AantalB + t.Aantal
  FROM @amount t, TabelA a
  WHERE a.TabelAID = t.TabelAID
END