title: Trigger - is it necessary BEGIN / COMMIT TRAN
body: <p>I want to make INSTEAD OF trigger like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So, I can be sure, that these actions is one atomic action. But it make sense or TRIGGER always execute inside transaction?</p>
  
  <p>Also, what happens if company will be deleted inside another TRIGGER like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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?</p>
  
  <p>PS. I can't set "CASCADE DELETE" because DB has some relationships like it:</p>
  
  <p><img src="https://i.stack.imgur.com/An926.jpg" alt="enter image description here"></p>
  
  <p>so, try to set CASCADE DELETE will throw error like it:</p>
  
  <blockquote>
    <p>Introducing FOREIGN KEY constraint 'FK_Persons_Areas' on table
    'Persons' may cause cycles or multiple cascade paths. Specify ON
    DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY
    constraints. Could not create constraint or index. See previous
    errors.</p>
  </blockquote>