嵌套SQL Server事务执行级联删除

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.

假设我有一个名为Companies的表,它有一个DepartmentID列。还有一个Departaments表,其中包含EmployeeID列。当然我也有一张员工表。问题是我想删除一家公司,所以首先我必须删除每个部门的所有员工,然后删除公司的所有部门。 Cascade Delete不是一个选项,因此我希望使用嵌套事务。我是SQL的新手,所以我很感激你的帮助。

2 个解决方案

#1

I'm not sure why you need nested transactions here. You only need one actual transaction:

我不确定你为什么需要嵌套交易。您只需要一个实际交易:

BEGIN TRAN

DELETE FROM Employee
    FROM Employee
    INNER JOIN Department ON Employee.DepartmentID = Department.DepartmentID
    INNER JOIN Company ON Department.CompanyID = Company.CompanyID
    WHERE Company.CompanyID = @CompanyID

DELETE FROM Department
    FROM Department
    INNER JOIN Company ON Department.CompanyID = Company.CompanyID
    WHERE Company.CompanyID = @CompanyID

DELETE FROM Company
    WHERE Company.CompanyID = @CompanyID

COMMIT TRAN

Note the double FROM, that is not a typo, it's the correct SQL syntax for performing a JOIN in a DELETE.

注意双重FROM,这不是拼写错误,它是在DELETE中执行JOIN的正确SQL语法。

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.

每个语句都是原子的,要么整个DELETE都会成功或失败,这在这种情况下并不重要,因为整个批处理会成功或失败。

BTW- I think you had your relationships backwards. The Department would not have an EmployeeID, the Employee would have a DepartmentID.

顺便说一句 - 我认为你的关系倒退了。该部门没有EmployeeID,员工将拥有DepartmentID。

#2

I'm not answering your question, but foreign Keys is the way to go, why is it not an option?

我没有回答你的问题,但是外键是要走的路,为什么不能选择?

As for nested transactions they are:

至于嵌套交易,它们是:

BEGIN
  delete from Employee where departmentId = 1;
  BEGIN
     delete from Department where companyId = 2;
     BEGIN
        delete from Company where companyId = 2;
     END
  END
END

Programmatically it looks different of course, but that'd depend on the platform you are using

以编程方式,它看起来当然不同,但这取决于您使用的平台

#1