CASCADE - 1

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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

  • 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! 😀

  • 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

  • Came to the game late but got it right. Excellent discussion!

    Not all gray hairs are Dinosaurs!

  • basic nice question

  • "ON DELETE CASCADE" was the basic of this question. Glad it had no tricks. 🙂

  • Nice, cascading.

    It suprished me that in the Message Pane only :

    (1 row(s) affected)

    is shown.

  • 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 54 (of 54 total)

You must be logged in to reply to this topic. Login to reply