[MS-SQL] Schending van Foreign Key constraint

[licensed]

Ik heb twee tabellen Docent en Aanbod met daartussen een Foreign Key relatie FK_Aanbod_Docent. Docent.DocentID is de Primary Key, Aanbod.DocentID is de Foreign Key. Omdat er een cyclische relatie in de database bestaat kan ik geen ON DELETE CASCADE gebruiken in de tabel Docent, daarom heb ik een trigger gebruik (zoals ik in [rml][ SQL Server] Trigger probleem / vraag[/rml] heb gelezen).
Mijn trigger ziet er zo uit:

Transact-SQL:

CREATE TRIGGER DeleteAanbod ON dbo.Docent 
FOR  DELETE 
AS
DELETE FROM Aanbod WHERE Aanbod.DocentID IN (SELECT DocentID FROM deleted);

Maar, helaas, het werkt niet:

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Aanbod_Docent'. The conflict occurred in database 'pwc02', table 'Aanbod', column 'DocentID'.

Waarom werkt het hier niet en in het genoemde topic wel?

[whoami]

Je gaat eerst je docent gaan verwijderen, en dan pas je records uit aanbod.
SQL Server kent geen 'before' triggers, maar wel instead of triggers.

Maak dus een instead of trigger op die docent tabel voor het delete commando.
Als er dus een delete op die tabel wordt uitgevoerd, dan zal je trigger worden uitgevoerd ipv het delete statement zelf.
In je trigger kan je dan dus eerst je records verwijderen uit de Aanbod - tabel, en dan het record verwijderen uit de docent tabel.

[P_de_B]

Als je de delete op de tabel docent uitvoerd met de trigger uitgeschakeld, krijg je dan toevallig ook de foutmelding?

[licensed]

P_de_B schreef op dinsdag 29 november 2005 @ 11:58:
Als je de delete op de tabel docent uitvoerd met de trigger uitgeschakeld, krijg je dan toevallig ook de foutmelding?

Ja, dan krijg ik dezelfde foutmelding.

whoami schreef op dinsdag 29 november 2005 @ 11:58:
Je gaat eerst je docent gaan verwijderen, en dan pas je records uit aanbod.
SQL Server kent geen 'before' triggers, maar wel instead of triggers.

Maak dus een instead of trigger op die docent tabel voor het delete commando.
Als er dus een delete op die tabel wordt uitgevoerd, dan zal je trigger worden uitgevoerd ipv het delete statement zelf.
In je trigger kan je dan dus eerst je records verwijderen uit de Aanbod - tabel, en dan het record verwijderen uit de docent tabel.

Wat is dan precies de betekenis van FOR?
De INSTEAD OF trigger werkt niet omdat de tabel Docent ook relaties heeft met andere tabellen. Dit zijn wel CASCADE ralaties.

[ Voor 19% gewijzigd door licensed op 29-11-2005 12:27 ]

[whoami]

Die FOR staat niet voor 'BEFORE'.
Als je nu FOR of AFTER specifieert, dat is hetzelfde in sql server.

Ik denk toch dat je het met een instead of moet oplossen; als je het zo doet:

code:

CREATE TRIGGER bliep ON docent
INSTEAD OF delete
AS
BEGIN
   delete from aanbod where docentId = deleted.id
   delete from docent where id = deleted.id
END

dan moet het toch werken ?

Wat is de exacte fout die je krijgt ?

Dit vind ik trouwens terug in de BoL:

AFTER triggers are never executed if a constraint violation occurs; therefore, these triggers cannot be used for any processing that might prevent constraint violations.

INSTEAD OF triggers are executed instead of the triggering action. These triggers are executed after the inserted and deleted tables reflecting the changes to the base table are created, but before any other actions are taken. They are executed before any constraints, so can perform preprocessing that supplements the constraint actions.

[ Voor 44% gewijzigd door whoami op 29-11-2005 12:23 ]

[Annie]

whoami schreef op dinsdag 29 november 2005 @ 12:23:
Ik denk toch dat je het met een instead of moet oplossen; als je het zo doet:

code:

CREATE TRIGGER bliep ON docent
INSTEAD OF delete
AS
BEGIN
   delete from aanbod where docentId = deleted.id
   delete from docent where id = deleted.id
END

dan moet het toch werken ?

Tuurlijk werkt dat. Ik heb dit al vaak genoeg zo toegepast.
Maar maak 'm wel meteen geschikt voor meerdere deletes.

[licensed]

Het probleem is dat Docent ook een 1 op 1 relatie met de tabel Persoon heeft, waardoor ik de volgende foutmelding krijg:

Cannot ALTER INSTEAD OF DELETE or UPDATE TRIGGER 'DeleteAanbod' on table 'dbo.Docent' because the table has a FOREIGN KEY with cascaded DELETE or UPDATE

Misschien kan ik de trigger beter op een andere plek in de cycle inbouwen.

[ Voor 16% gewijzigd door licensed op 29-11-2005 12:41 ]

[Verwijderd]

Als je toch al cascaded deletes gebruikt in je ontwerp, waarom wil je datzelfde effect hier dan via een trigger bereiken? Consequent zijn heeft namelijk zijn voordelen.

[licensed]

Verwijderd schreef op dinsdag 29 november 2005 @ 13:40:
Als je toch al cascaded deletes gebruikt in je ontwerp, waarom wil je datzelfde effect hier dan via een trigger bereiken? Consequent zijn heeft namelijk zijn voordelen.

Ik quote even mezelf:

licensed schreef op dinsdag 29 november 2005 @ 11:53:
Ik heb twee tabellen Docent en Aanbod met daartussen een Foreign Key relatie FK_Aanbod_Docent. Docent.DocentID is de Primary Key, Aanbod.DocentID is de Foreign Key. Omdat er een cyclische relatie in de database bestaat kan ik geen ON DELETE CASCADE gebruiken in de tabel Docent, daarom heb ik een trigger gebruik (zoals ik in [rml][ SQL Server] Trigger probleem / vraag[/rml] heb gelezen).
Mijn trigger ziet er zo uit:

Transact-SQL:

CREATE TRIGGER DeleteAanbod ON dbo.Docent 
FOR  DELETE 
AS
DELETE FROM Aanbod WHERE Aanbod.DocentID IN (SELECT DocentID FROM deleted);

Maar, helaas, het werkt niet:

[...]


Waarom werkt het hier niet en in het genoemde topic wel?

[Annie]

licensed schreef op dinsdag 29 november 2005 @ 12:39:
Het probleem is dat Docent ook een 1 op 1 relatie met de tabel Persoon heeft, waardoor ik de volgende foutmelding krijg:

[...]


Misschien kan ik de trigger beter op een andere plek in de cycle inbouwen.

Het is idd niet mogelijk om een INSTEAD OF trigger op een table te plaatsen wanneer deze ook een cascading actie heeft op een FK (heeft, afaik, te maken met de volgorde waarin cascades en triggers worden afgewerkt in een cascading tree).
Uit de BOL:

A table that has an INSTEAD OF trigger cannot also have a REFERENCES clause that specifies a cascading action. An AFTER trigger on a table targeted by a cascading action, however, can execute an INSERT, UPDATE, or DELETE statement on another table or view that fires an INSTEAD OF trigger defined on that object.

Je kan de CASCADE van de relatie tussen Persoon en Docent verwijderen en vervangen door een INSTEAD OF trigger om het op te lossen.

Ik ben overigens wel benieuwd naar het datamodel waardoor je zo in de knoei raakt met circular references en cascades op de FK's.

[kenneth]

Dat probleem hebben wij ook, met circular references ... bv. een tabel Personeel die in veel tabellen voorkomt (bij de klanten als accountmanager, bij contacthistorie als uitvoerder, enz).