title: MS SQL. Cascade Delete and triggers
body: <p>Short question: "Trigger did not fire on DeliveryLine when I delete DeliveryHeader with cascade"</p>
  
  <p>DETAILS:</p>
  
  <p>I have a tables </p>
  
  <pre><code>CREATE TABLE [dbo].[DeliveryHeader](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      CONSTRAINT [PK_DeliveryHeader] PRIMARY KEY CLUSTERED 
      ([Id] ASC)
  ); 
  
  CREATE TABLE [dbo].[DeliveryLine](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [DeliveryHeaderId] [int] NOT NULL,
      CONSTRAINT [PK_DeliveryLine] PRIMARY KEY CLUSTERED 
      (   [Id] ASC )
  );
  </code></pre>
  
  <p>And foreign key cascade delete:</p>
  
  <pre><code>ALTER TABLE [dbo].[DeliveryLine]  WITH CHECK ADD  CONSTRAINT [FK_DeliveryLine_DeliveryHeader] FOREIGN KEY([DeliveryHeaderId])
  REFERENCES [dbo].[DeliveryHeader] ([Id])
  ON DELETE CASCADE;
  </code></pre>
  
  <p>Also I have a trigger on DeliveryLine</p>
  
  <pre><code>CREATE TRIGGER [dbo].[trg_DeliveryLine_DeleteLog]   
      ON [dbo].[DeliveryLine]
      AFTER DELETE
  AS 
  BEGIN
      SET NOCOUNT ON;
      -- some code    
  END
  </code></pre>
  
  <p>But trigger did not fire on DeliveryLine when I delete DeliveryHeader</p>
  
  <pre><code>DELETE FROM [dbo].[DeliveryHeader];
  </code></pre>
  
  <h2>Is some way to fire trigger with cascade delete ? I know only roundabout way:</h2>
  
  <ol>
  <li>Delete Cascade from foreign key.
  updated:</li>
  <li><p>Write another trigger :</p>
  
  <pre><code>CREATE TRIGGER [dbo].[trg_DeliveryHeader_DeleteLog]   
  ON [dbo].[DeliveryHeader]
  INSTEAD OF DELETE
  AS 
  BEGIN
      SET NOCOUNT ON;
  
      DELETE FROM [dbo].[DeliveryLine] WHERE [DeliveryHeaderId] IN (SELECT Id from deleted)
      DELETE FROM [dbo].[DeliveryHeader] WHERE [Id] IN (SELECT Id from deleted)
  END
  </code></pre></li>
  </ol>