[Solved] SQL Trigger on Update

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
Jhstephenson
Posts: 297
Joined: Wed Apr 22, 2015 11:44 pm

[Solved] SQL Trigger on Update

Post by 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:

Code: Select all

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?
Last edited by Jhstephenson on Fri Mar 26, 2021 9:36 pm, edited 1 time in total.
Jhstephenson
Posts: 297
Joined: Wed Apr 22, 2015 11:44 pm

[Solved] SQL Trigger on Update

Post by Jhstephenson »

Got an answer on this from StackOverflow.

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

Code: Select all

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.
Post Reply