March 21, 2013 at 9:07 am
L' Eomot Inversé (3/21/2013)
I guess if you are in a situation where you can't use foreign keys, which is what you appear to be saying, the question of using cascade doesn't arise at all - even I, an ardent supporter of cascade, wouldn't dream of suggesting you use cascade in that case; unlike many, I refuse to belive in or advocate impossible things (not even before breakfast).
I seem to recalll that very old versions of SQL Server (I believe prior to 6.0 or so) did not support foreign keys. If you wanted a foreign key relationship, you built a trigger to enforce it. And since you wrote the trigger code yourself, you had the choice on what to do on violations - rollback and error ("NO ACTION"), or attempt to fix ("SET NULL" / "SET DEFAULT" / "CASCADE").
If Marlon is in a similar situation, then:
1) I don't envy her. At all.
2) I see no problem in using triggers to enforce the business need to cascade on deletion from a specific table.
March 21, 2013 at 9:46 am
Hugo Kornelis (3/21/2013)
L' Eomot Inversé (3/21/2013)
I guess if you are in a situation where you can't use foreign keys, which is what you appear to be saying, the question of using cascade doesn't arise at all - even I, an ardent supporter of cascade, wouldn't dream of suggesting you use cascade in that case; unlike many, I refuse to belive in or advocate impossible things (not even before breakfast).I seem to recalll that very old versions of SQL Server (I believe prior to 6.0 or so) did not support foreign keys. If you wanted a foreign key relationship, you built a trigger to enforce it. And since you wrote the trigger code yourself, you had the choice on what to do on violations - rollback and error ("NO ACTION"), or attempt to fix ("SET NULL" / "SET DEFAULT" / "CASCADE").
If Marlon is in a similar situation, then:
1) I don't envy her. At all.
2) I see no problem in using triggers to enforce the business need to cascade on deletion from a specific table.
I think then that we agree on that much - when you have to enforce forign keys using triggers instead of constraints you have to use those triggers to enfoce whatever action you want, be it cascade or set null or set default or roll back or something else (if it's something else, you need triggers even if you can use constraints to enforce the foreign key).
Tom
March 21, 2013 at 11:04 am
Very nice straight-forward question. This is something that I am going to discuss with our developers as I am sure there are systems where these could be useful. We generally just flag rows as invalid instead of allowing deletions but this is a good option to keep in mind too. Certainly better than using triggers in my opinion! 😀
March 22, 2013 at 12:20 pm
Thanks Ron
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2013 at 4:13 pm
Came to the game late but got it right. Excellent discussion!
Not all gray hairs are Dinosaurs!
April 9, 2013 at 7:50 am
basic nice question
April 17, 2013 at 12:27 am
"ON DELETE CASCADE" was the basic of this question. Glad it had no tricks. 🙂
June 12, 2015 at 5:26 am
Nice, cascading.
It suprished me that in the Message Pane only :
(1 row(s) affected)
is shown.
June 15, 2015 at 11:43 am
Nice complicated question. I could do it easily putting two select statement (order & ordertable), before and after delete command. Scored 1 mark.
Thanks.
Viewing 9 posts - 46 through 53 (of 53 total)
You must be logged in to reply to this topic. Login to reply