Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Cyclonik
Posting Yak Master
114 Posts |
 Posted - 2002-01-07 : 19:29:12
|
| I noticed something odd when viewing one of my tables in design mode in SQL Server 2k. An FK on a recursive relationship won't allow me to set the relationship to cascade (update or delete). Is this by design or is something wrong here?Christian-=:SpasmatiK:=- |
|
|
davidpardoe
Constraint Violating Yak Guru
324 Posts |
Posted - 2002-01-08 : 05:00:42
|
I posted what I think may be a similar question...http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=10641...but got no replies!I think that if there is any kind of looped relationships then the cascade constraint is not enforced despite it being logically OK. I think this may be an anomaly with SQL 2000. In any case SQL 2000 is the first release that includes cascade update and delete constraints.We ended up getting around the problem with triggers - a little like this...CREATE TRIGGER TRG_management_file_update ON Dictionary_file_type_name FOR UPDATE AS DECLARE @numrows int SET @numrows=@@rowcount IF UPDATE (file_type) IF @numrows=1 UPDATE Dictionary_report_management SET file_type=(SELECT file_type FROM inserted) FROM Dictionary_report_management AS a INNER JOIN deleted AS d ON a.file_type=d.file_typeELSE IF @numrows>1 BEGIN RAISERROR('Updates to more than one row are not allowed',10,1) ROLLBACK TRANSACTIONENDgo============The Dabbler! |
 |
|
|
|
|
|
FK Cascading oddity