How to Create trigger to Update the NoofOrgs Count under each category in Category Table based on Temp Table in Sql Server

--creating a trigger

CREATE TRIGGER [dbo].UPDATEORGSCOUNTINCATEGORY_AFTERINSERT_FROMORGANISATIONS]

  ON

--here we mention the table name

  [dbo].[Organisations]

--here we can have insert, update, delete as trigger point

--I want to execute my trigger when a record is inserted in organizations table (step-1)

--then get the count of orgs in each category from organisationcategories table and save it in temp table(step-2)

--then update the category table with orgs count in each category(step-3)

  AFTER INSERT

  AS

  BEGIN

  --we are updating the count bcz a listing or nonprofi can be approved or rejected then the count changes.

--creating a temp table with two columns of int type

DECLARE @temp TABLE

(

CategoryId INT,

NoofOrgs INT

)

--inserting values into temp table

INSERT INTO @temp

SELECT * from (

        --here we are selecting these two columns

select CategoryId,ISNULL(NoofOrgs , 0) AS 'NoofOrgs' from (

    --here we are trying to get each categotyId, nooforgs count from organisationcategories and

                    --if no orgs found under category then we are returning 0(zero)

                    select cats.Id as CategoryId,(SELECT ISNULL(COUNT(*) , 0) AS 'NoofOrgs'

    FROM Categories cc

    inner join OrganisationCategories oc

    on

    cc.id=oc.categoryid

    inner join Organisations oo

    on

    oo.Id=oc.orgid

    where oo.isapproved=1 and oo.isactive=1 and cc.Id=cats.Id

    GROUP BY

    cc.Id) NoofOrgs from Categories cats

) NoofOrgsCount

)as OrgsCountInCategories

        --declaring two feilds to assign values from temp table

DECLARE @CategoryId int

DECLARE @NoofOrgs INT

        --assigning temp table to cursor for looping

DECLARE cur CURSOR FOR SELECT CategoryId, NoofOrgs FROM @temp

OPEN cur

        --getting indiuval record from cursor

FETCH NEXT FROM cur INTO @CategoryId, @NoofOrgs

WHILE @@FETCH_STATUS = 0 BEGIN

                --updating the categories table with nooforgs count

Update Categories SET NoofOrgs=@NoofOrgs where Id=@CategoryId

                --getting the next record after updation

FETCH NEXT FROM cur INTO @CategoryId, @NoofOrgs

END

CLOSE cur    

DEALLOCATE cur

  END

GO