You need to do always treat Triggers as set-theory stuff... (or if you
must, a cursor)
-- warning, in the absence of schema this was validated in GMAIL
CREATE TRIGGER updateContactCallTreeTotalsTrigger
ON CallTreeContacts
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
-- Subtract old counts
UPDATE Contacts
SET planleadercount = planleadercount - COUNT(cd.uid)
FROM deleted AS D
INNER JOIN calltreecontacts AS cd
ON cd.contactUid = D.contactuid
INNER JOIN CallTrees ctd
ON cd.calltreeuid = ctd.Uid
INNER JOIN plans AS pd
ON pd.Uid = ctd.planUid
AND pd.deleted = 'false'
-- Add new counts
UPDATE Contacts
SET planleadercount = planleadercount + COUNT(ci.uid)
FROM INSERTED AS I
INNER JOIN calltreecontacts AS ci
ON ci.contactUid = I.contactuid
INNER JOIN CallTrees cti
ON ci.calltreeuid = cti.Uid
INNER JOIN plans AS pi
ON pi.Uid = cti.planUid
AND pi.deleted = 'false'
END
--
"He uses statistics as a drunken man uses lamp-posts… for support
rather than illumination." Andrew Lang
Marc C. Brooks
http://musingmarc.blogspot.com
