问题描述
我想像这样制作 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!