本文介绍了触发器 - 是否有必要 BEGIN/COMMIT TRAN的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想像这样制作 INSTEAD OF 触发器:

I want to make INSTEAD OF trigger like this:

CREATE TRIGGER [dbo].[DeleteCompany]
ON [dbo].[Company]
 INSTEAD OF DELETE
  AS
  DECLARE @CompanyID int
  SELECT @CompanyID = deleted.CompanyID FROM deleted

  BEGIN TRAN
  DELETE FROM Project WHERE CompanyID = @CompanyID
  DELETE FROM CompanyPerson WHERE CompanyID = @CompanyID
  UPDATE PersonCompany SET CompanyID = null WHERE CompanyID = @CompanyID

  DELETE [Company]
     FROM DELETED D
     INNER JOIN [Company] T ON T.CompanyID = D.CompanyID
  COMMIT TRAN

所以,我可以肯定,这些动作是一个原子动作.但它是有意义的还是 TRIGGER 总是在事务内部执行?

So, I can be sure, that these actions is one atomic action. But it make sense or TRIGGER always execute inside transaction?

另外,如果公司在另一个 TRIGGER 中被删除会发生什么:

Also, what happens if company will be deleted inside another TRIGGER like this:

CREATE TRIGGER [dbo].[DeleteSecurityLevel]
ON [dbo].[SecurityLevel]
 INSTEAD OF DELETE
  AS
  DECLARE @SecurityLevelID int
  SELECT @SecurityLevelID = deleted.SecurityLevelID FROM deleted

  BEGIN TRAN
  DELETE FROM Company WHERE SecurityLevelId = @SecurityLevelID
  DELETE FROM CompanyRole WHERE SecurityLevelId = @SecurityLevelID
  ....

  DELETE SecurityLevel
     FROM DELETED D
     INNER JOIN SecurityLevel T ON T.SecurityLevelID = D.SecurityLevelID
  COMMIT TRAN

因此,触发器DeleteSecurityLevel 正在删除公司并调用DeleteCompany 触发器.如果每个触发器都有 BEGIN/COMMIT TRAM ,它将在一个事务中?如果每个触发器都没有它?

so, trigger DeleteSecurityLevel is deleting Company and call DeleteCompany trigger. It would be in one transaction if each trigger has BEGIN/COMMIT TRAM ? if each trigger does not have it?

附注.我无法设置CASCADE DELETE",因为 DB 有一些类似的关系:

PS. I can't set "CASCADE DELETE" because DB has some relationships like it:

所以,尝试设置 CASCADE DELETE 会抛出这样的错误:

so, try to set CASCADE DELETE will throw error like it:

在表上引入 FOREIGN KEY 约束 'FK_Persons_Areas'人"可能会导致循环或多个级联路径.指定开启DELETE NO ACTION 或 ON UPDATE NO ACTION,或修改其他 FOREIGN KEY约束.无法创建约束或索引.见上一个错误.

推荐答案

所有 DML 语句都在一个事务中执行.触发器中的 DML 将使用触发触发器的语句的事务上下文,因此触发器内部和外部的所有修改都是单个原子操作.

All DML statements are executed within a transaction. The DML within the trigger will use the transaction context of the statement that fired the trigger so all modifications, inside the trigger and out, are a single atomic operation.

这篇关于触发器 - 是否有必要 BEGIN/COMMIT TRAN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-28 21:33
查看更多