title: How to create delete trigger in SQL on deletion of one record from child table updates the records count in parent table?
body: <p>I have created two tables .First is of magzine</p>
  
  <pre><code>CREATE TABLE [dbo].[Magzine](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](350) NULL,
  [MagzineType] [int] NOT NULL,
  [TotalPages] [int] NOT NULL,
  [DateCreated] [datetime] NOT NULL,
  [Active] [bit] NOT NULL, CONSTRAINT [PK_tblMagzine] PRIMARY KEY CLUSTERED (
  [ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] GO
  </code></pre>
  
  <p>which has a column name TotalPages which holds for the count of total pages in the magzine</p>
  
  <p>and here is the MagzinePage Table.</p>
  
  <pre><code>CREATE TABLE [dbo].[MagzinePage](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [MagzineID] [int] NOT NULL,
  [Title] [nvarchar](255) NULL,
  [SubTitle] [nvarchar](255) NULL,
  [Photo] [nvarchar](255) NULL,
  [Description] [nvarchar](2000) NULL,
  [DateCreated] [datetime] NOT NULL,
  [CreatedBy] [nvarchar](25) NOT NULL,
  [Active] [bit] NOT NULL,
  [IsMapped] [bit] NULL, CONSTRAINT [PK_tblPage] PRIMARY KEY CLUSTERED (
  [ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] GO
  </code></pre>
  
  <p>I have created a trigger on the insertion of data in magzinePage table which can take care of the total number of records inserted in the table against an ID
   here is the Trigger</p>
  
  <pre><code>Alter TRIGGER trgAfterInsert ON [dbo].[MagzinePage] 
      FOR INSERT
  
      AS
      Declare @MagzineID int;
      Declare @TotalPages int;
      Set @MagzineID= (Select Top 1 MagzineID From MagzinePage Order By ID Desc)
      Set @TotalPages =  (Select Count(MagzineID) from MagzinePage Where MagzineID=@MagzineID Group By MagzinePage.MagzineID) 
  
          Update Magzine Set TotalPages=@TotalPages Where ID= @MagzineID
  
          PRINT 'AFTER INSERT trigger fired.'
      GO  
  </code></pre>
  
  <p>Now I want to create a trigger for the purpose that can help decrement the total number of pages in the Magzine table whenever a record from the magzinePage table is deleted.</p>
  
  <p>kindly do me favour to figure a way out for that.
  Thanks. </p>