[Solved] SQL Trigger on Update

Jhstephenson

I was wondering if someone might have an idea about how I could accomplish something with a trigger on a table I have created?

I have the following tables:
ClientAccount
ClientActivity

In ClientAccount I have an attribute that is a count of the number of ClientActivity records that a Client has. I use that count as a Badge on a tab.

Whenever a new activity is created the following trigger fires off:

USE [officemgt]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[RecountInsertedClientActivities] 
   ON  [dbo].[CLIENTACTIVITY]
   AFTER INSERT 
AS 
BEGIN

SET NOCOUNT ON;

UPDATE CLIENTACCOUNT
SET Count_ClientActivitiesAsClient=(SELECT COUNT(*) FROM ClientActivity WHERE ClientActivity.parent_Client_RID=CLIENTACCOUNT.ID)
FROM inserted
LEFT JOIN CLIENTACCOUNT ON CLIENTACCOUNT.ID=inserted.parent_Client_RID

END

I then have a similar trigger that fires off if a ClientActivity record is Deleted.

These two triggers work just fine.

The issue is that sometimes an activity may be changed from one client to another. So, I would need to update the counts for both the old client so it is reduced and then update the count for the new client the activity was assigned to.

How can I do that?

Jhstephenson

Got an answer on this from StackOverflow.

What I want to do can just be done with one Trigger (I hoped for that)...

USE [officemgt]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[RecountInsertedClientActivities] 
   ON  [dbo].[CLIENTACTIVITY]
   AFTER INSERT, UPDATE, DELETE

AS 
BEGIN
    SET NOCOUNT ON;

UPDATE CA SET
    Count_ClientActivitiesAsClient = (SELECT COUNT(*) FROM dbo.ClientActivity CV WHERE CV.parent_Client_RID = CA.ID)
FROM dbo.CLIENTACCOUNT CA
WHERE CA.ID in (SELECT I.parent_Client_RID FROM INSERTED I UNION ALL SELECT D.parent_Client_RID FROM DELETED D);
END;

Works for all cases of Insert, Update, or Delete.