| Author |
Topic |
|
Lenjaku
Starting Member
13 Posts |
 Posted - 2012-08-20 : 15:00:23
|
| I have lessons, subjects and questions.I want to delete all the questions that belong to the lessons which are under a specific subject I want to delete.That means I wanna delete the subject having hte lessons ids following by deleting the lessons adn those have the questions.each lesson has its own questions and I wanna delete them all since I delete the subject which is the root. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-20 : 15:04:03
|
| If you create your foreign keys with cascading delete, this will happen automagically.-Chad |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-20 : 17:08:13
|
quote:
Originally posted by chadmat
If you create your foreign keys with cascading delete, this will happen automagically.-Chad
Creating cascading deletes is not recommended from maintainability aspect as without proper documentation it will be hard for someone to maintain the system in future------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-08-20 : 17:27:57
|
quote:
Originally posted by visakh16
quote:
Originally posted by chadmat
If you create your foreign keys with cascading delete, this will happen automagically.-Chad
Creating cascading deletes is not recommended from maintainability aspect as without proper documentation it will be hard for someone to maintain the system in future
I disagree that they are not recommended. They sure do work great and save on delete code.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-08-21 : 04:35:22
|
| Another method is to create a stored procedure. Within the stored procedure create some DELETE code. Place the DELETE code in an Explicit transaction. This will manage your ROLLBACK or COMMIT.If you post the DDL and DML - ?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-08-21 : 09:25:27
|
| Another way to this is to create Triggers:They can do some good job if you program them well.look at this:http://msdn.microsoft.com/en-us/library/aa258254(v=sql.80).aspx--------------------------Joins are what RDBMS's do for a living |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-21 : 09:54:08
|
quote:
Originally posted by tkizer
quote:
Originally posted by visakh16
quote:
Originally posted by chadmat
If you create your foreign keys with cascading delete, this will happen automagically.-Chad
Creating cascading deletes is not recommended from maintainability aspect as without proper documentation it will be hard for someone to maintain the system in future
I disagree that they are not recommended. They sure do work great and save on delete code.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
i'm not commenting on their functionality. but in a large system without proper documentation/process in place they'll become very difficult to maintain in future.On more than one occasions we've had people complaining on losing data unintentionally from inherited systems which on analysis was found to be because of presence of ON CASCADE DELETEs which wasnever documented anywhere and nobody was aware that they were present. The developers who were part of original system implementation had all moved since then. So to avoid such situations I would suggest having very good supplimentary documentation to support this functionality if it is used. I myself have also used it quite a few times but I make it a point to properly document it so as not to give surprises to maintenance teams later  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-08-21 : 10:39:23
|
| I think the wider question is around how the system is built from the outset . What are the architectural decisions? Delete sql and cascade deletes , both valid under the right circumstances. Generally , I prefer the delete sql option , as there is more transparency - but ultimately , controls are required at all levels , to make sure mistakes don't occurJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-21 : 10:54:29
|
quote:
Originally posted by jackv
I think the wider question is around how the system is built from the outset . What are the architectural decisions? Delete sql and cascade deletes , both valid under the right circumstances. Generally , I prefer the delete sql option , as there is more transparency - but ultimately , controls are required at all levels , to make sure mistakes don't occurJack Vamvas--------------------http://www.sqlserver-dba.com
Yep.Exactly my point ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
how do I delete from 1 tb based on another?