<p>Suppose I have a table called Companies that has a DepartmentID column. There's also a Departaments table that has as EmployeeID column. Of course I have an Employee table as well. The problem is that I want to delete a company, so first i have to delete all the employees for every departament and then all the departaments in the company.
Cascade Delete is not an option, therefore i wish to use nested transactions. I'm new to SQL so I would appreciate your help.</p>

[{'answer_id': 58943, 'author': 'Vinko Vrsalovic', 'author_id': 5190, 'author_profile': 'https://Stackoverflow.com/users/5190', 'pm_score': 0, 'selected': False, 'text': "<p>I'm not answering your question, but foreign Keys <em>is</em> the way to go, why is it not an option?</p>\n\n<p>As for nested transactions they are:</p>\n\n<pre><code>BEGIN\n  delete from Employee where departmentId = 1;\n  BEGIN\n     delete from Department where companyId = 2;\n     BEGIN\n        delete from Company where companyId = 2;\n     END\n  END\nEND\n</code></pre>\n\n<p>Programmatically it looks different of course, but that'd depend on the platform you are using</p>\n"}, {'answer_id': 58974, 'author': 'Brannon', 'author_id': 5745, 'author_profile': 'https://Stackoverflow.com/users/5745', 'pm_score': 3, 'selected': True, 'text': "<p>I'm not sure why you need nested transactions here.  You only need one actual transaction:</p>\n\n<pre><code>BEGIN TRAN\n\nDELETE FROM Employee\n    FROM Employee\n    INNER JOIN Department ON Employee.DepartmentID = Department.DepartmentID\n    INNER JOIN Company ON Department.CompanyID = Company.CompanyID\n    WHERE Company.CompanyID = @CompanyID\n\nDELETE FROM Department\n    FROM Department\n    INNER JOIN Company ON Department.CompanyID = Company.CompanyID\n    WHERE Company.CompanyID = @CompanyID\n\nDELETE FROM Company\n    WHERE Company.CompanyID = @CompanyID\n\nCOMMIT TRAN\n</code></pre>\n\n<p>Note the double FROM, that is not a typo, it's the correct SQL syntax for performing a JOIN in a DELETE.</p>\n\n<p>Each statement is atomic, either the entire DELETE will succeed or fail, which isn't that important in this case because the entire batch will either succeed or fail.</p>\n\n<p>BTW- I think you had your relationships backwards.  The Department would not have an EmployeeID, the Employee would have a DepartmentID.</p>\n"}]
['https://Stackoverflow.com/questions/58916', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1360/']