[SQL Server] Trigger probleem / vraag

[Verwijderd]

Ik ben behoorlijk newby in SQL server 2000 en heb de volgende situatie (een beetje ingekort):

Ik ben bezig om een forum te bouwen. In SQL server heb ik o.a. de volgende tabellen aangemaakt :

USERS
FORUMMAIN
FORUMSUB
FORUMPOST

Nou heb ik een trigger geschreven voor de tabel USERS. Als ik een record (user) uit de tabel USERS verwijder, dan moeten gerelateerde records (gerelateerde users) in de tabellen FORUMMAIN, FORUMSUB en FORUMPOST) mee verwijderd worden:

code:

CREATE TRIGGER [TRIGGER_USER_DELETE] ON dbo.USERS 
FOR DELETE 
AS
  DECLARE @delete SMALLINT
  SELECT @delete  = (SELECT user_id FROM Deleted)

  DELETE FROM FORUMMAIN WHERE FORUMMAIN.forummain_starterid = @delete
  DELETE FROM FORUMSUB WHERE FORUMSUB.forumsub_starterid = @delete
  DELETE FROM FORUMPOST WHERE FORUMPOST.forumpost_userid = @delete

Goed, bovenstaande trigger werkt prima, en gerealateerde records worden netjes gedelete.

Maar ook in FORUMSUB (en FORUMPOST) heb ik triggers. Deze triggers moeten records updaten in andere tabellen, zoals de datum van de laatste post, bijvoorbeeld. Zo heb ik de volgende trigger gemaakt voor de tabel FORUMSUB:

code:

CREATE TRIGGER [DELETE_UPDATEFORUMMAIN] ON dbo.FORUMSUB 
AFTER  DELETE
AS
  DECLARE @delete SMALLINT
  DECLARE @records SMALLINT
  DECLARE @datum SMALLDATETIME
  DECLARE @replies SMALLINT  

  SELECT @delete = (SELECT forumsub_mainid FROM deleted)
  SELECT @records = (SELECT COUNT(*) FROM FORUMSUB WHERE FORUMSUB.forumsub_mainid = @delete)
  SELECT @replies = (SELECT SUM(FORUMSUB.forumsub_replies) FROM FORUMSUB WHERE FORUMSUB.forumsub_mainid = @delete)
  
  IF @replies <> 0 
  BEGIN
    SELECT @datum = (SELECT MAX(FORUMSUB.forumsub_lastpost) FROM FORUMSUB WHERE FORUMSUB.forumsub_mainid = @delete)
  END
  ELSE
  BEGIN
    SET @datum = NULL
  END

  UPDATE FORUMMAIN SET FORUMMAIN.forummain_topics = @records, FORUMMAIN.forummain_posts = @replies, FORUMMAIN.forummain_lastpost = @datum WHERE FORUMMAIN.forummain_id = @delete

Als ik bovenstaande trigger naast de trigger op de tabel USERS activeer, en vervolgens een user wil verwijderen die meerdere records heeft in forumsub, dan krijg ik de volgende foutmelding:

Subquery returned more than one value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.

Als ik een record delete in de tabel users, dan zal de trigger op de tabel users ervoor zorgen dat gerelateerde records in de tabel forumsub mee verwijderd worden. Op dit moment activeerd natuurlijk ook de trigger op de tabel forumsub, en hier zal het probleem wel ergens zitten.

Wie weet wat er fout gaat en hoe ik het op zou kunnen lossen?

[gorgi_19]

Waarom leg je geen relaties en laat je die afdwingen met cascading update / delete?

[Verwijderd]

Omdat dat volgens mij niet kan in dit geval. Ik heb namelijk al constraints tussen de tabellen FORUMMAIN, FORUMSUB en FORUMPOST. Ik kan daarnaast niet (ook) nog eens relaties leggen naar alle 3 de tabellen vanaf de tabel users, want dan zou ik ongeveer onderstaande fout krijgen:

Introducing FOREIGN KEY constraint 'FK_FORUMSUB_FORUMMAIN' on table 'FORUMSUB' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

En microsoft zegt er ook iets over..

[Shir]

Meerdere forumsub_mainid's in regel 9?

[Annie]

Zorg ervoor dat je triggers geschikt zijn voor meerdere deletes. In het geval van de eerste trigger is dat simpel.

code:

CREATE TRIGGER [TRIGGER_USER_DELETE] ON dbo.USERS 
FOR DELETE 
AS
  DELETE FROM FORUMMAIN 
  WHERE FORUMMAIN.forummain_starterid IN (SELECT user_id FROM deleted)
  
  DELETE FROM FORUMSUB 
  WHERE FORUMSUB.forumsub_starterid IN (SELECT user_id FROM deleted)
  
  DELETE FROM FORUMPOST 
  WHERE FORUMPOST.forumpost_userid IN (SELECT user_id FROM deleted)

In het geval van de 2e trigger zal je wat meer werk moeten verrichten. Maar misschien dat je daar met een UPDATE ... FROM ... query een heel eind kan komen (zie de Books Online voor meer info).
Een schot in het donker, geen idee of dit überhaupt kan werken heb even geen sql tot m'n beschikking, maar misschien is het een zet in de goede richting.

code:

CREATE TRIGGER [DELETE_UPDATEFORUMMAIN] ON dbo.FORUMSUB 
AFTER  DELETE
AS
  UPDATE FORUMMAIN 
  SET 
    FORUMMAIN.forummain_topics = COUNT(*), 
    FORUMMAIN.forummain_posts = SUM(FORUMSUB.forumsub_replies), 
    FORUMMAIN.forummain_lastpost = 
      CASE WHEN SUM(FORUMSUB.forumsub_replies) <> 0
        THEN MAX(FORUMSUB.forumsub_lastpost)
        ELSE NULL
      END
  FROM FORUMSUB
  WHERE FORUMMAIN.forummain_id IN (SELECT forumsub_mainid FROM deleted)

[curry684]

Waarom delete je uberhaupt users? Doen we op GoT ook niet

_{en ja dit is serieus, deleten van useraccounts is eigenlijk zelden nuttig/nodig}

[ Voor 3% gewijzigd door curry684 op 06-06-2004 00:44 ]

[Verwijderd]

Thanks!! Het is zo simpel.. maar ik was flink gaar toen ik deze code aan het schrijven was...

Maarruh, ik ben niet van plan om users te deleten, maar het moet eventueel wel mogelijk zijn. En aangezien ik sql server nog niet echt goed ken, moet ik zorgen dat ik alles wel goed doortest om eventuele problemen zoveel mogelijk te voorkomen.