Problem : Multiple cascading actions on tables

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem : Multiple cascading actions on tables

Author

Topic

bubix
Starting Member

24 Posts

Posted - 2005-11-15 : 15:58:07

Hello,

I want to do multiple cascading actions on tables like this:

I have those three tables :
Table_A (Col_A1 int primary key, Col_A2 char(20), Col_A3 int)
Table_B (Col_B1 int primary key, Col_B2 char(20))
Table_C (Col_C1 int primary key)

And now the multiple cascading actions:

/*Add a foreign key (Fk_Col_A1) in the Table A.
This foreign key make a relationship between
the column Col_B1 in Table_B and Col_A1 in Table_A*/

alter table Table_A
add constraint Fk_Col_A1 foreign key (Col_A1)
references Table_B (Col_B1) on update cascade
go

/*Add an another foreign key (Fk_Col_A3) in the Table A.
This foreign key make a relationship between
the column Col_C1 in Table_C and Col_A3 in Table_A*/

alter table Table_A
add constraint Fk_Col_A3 foreign key (Col_A3)
references Table_C (Col_C1) on update cascade
go

/* I want add another foreign key like this*/
alter table Table_B
add constraint Fk_ColB1 foreign key (Col_B1)
references Table_C (Col_C1) on update cascade

--But this statement don't execute and I receive this message error
/* Introducing FOREIGN KEY constraint 'Fk_ColA1' on table 'Table_A' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
*/

There is an error because I have cicular references.

But my code must do circular references, how can do circular references in another way ???

Thanks

Bye

Kristen
Test

22859 Posts

Posted - 2005-11-16 : 01:24:05

"how can do circular references in another way"

You could implement it in a trigger on the table. Or by using a Stored procedure - so you have to call the SProc to do the work, instead of just being able to access the table directly - which is bad news of "other things" will want to access the table directly!

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-11-16 : 02:31:44

call SPs in the sequence you would like the actions to occur

--------------------
keeping it simple...

bubix
Starting Member

24 Posts

Posted - 2005-11-16 : 02:38:21

Thanks,

I'll try with a trigger on the table.

Bye

bubix
Starting Member

24 Posts

Posted - 2005-11-16 : 02:46:41

Helle jen,

If I good understood, I apply a trigger on one table with option "after update" for my exemple and then in the trigger, I call other stored procedures which have to update other columns in other tables.

Is it like I explained ???

Thanks

Kristen
Test

22859 Posts

Posted - 2005-11-16 : 03:34:50

The AFTER trigger will run "after" the actual delete of the record has been done. That delete will fail if there are Foreign Keys in place (i.e. if the referential integrity is breached).

If the delete fails you will have to use an INSTEAD OF trigger. In that you can do:

Delete all children
Delete actual record

And yes, you can call SProcs to do the actual "Delete all children" parts of the trigger.

Kristen

luisfleitas
Starting Member

1 Post

Posted - 2009-06-22 : 19:42:20

What I do not understand is that I am able to create the relationship with the ID, but the operation fails when I use a script. Is therea setting that I am missing ?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources