I got round my problem by getting the totals via subqueries in my select statement.Much better than trying to update via triggers SELECT c.uid, c.surname, c.firstName, (SELECT count(cc.uid) FROM calltreecontacts cc WHERE cc.contactUid = c.uid and cc.calltreecontactroleid = 1) as 'Plan Leader Count', (SELECT count(cc.uid) FROM calltreecontacts cc WHERE cc.contactUid = c.uid and cc.calltreecontactroleid = 2) as 'Plan Deputy Count', (SELECT count(cc.uid) FROM calltreecontacts cc WHERE cc.contactUid = c.uid and cc.calltreecontactroleid = 3) as 'Principal Count', (SELECT count(cc.uid) FROM calltreecontacts cc WHERE cc.contactUid = c.uid and cc.calltreecontactroleid = 4) as 'Caller Count' FROM Contacts c ORDER BY c.surname, [EMAIL PROTECTED]> Date: Tue, 12 Feb 2008 16:28:20 +0000> From: [EMAIL PROTECTED]> Subject: Re: [ADVANCED-DOTNET] Trigger and multiple values in the inserted table> To: ADVANCED-DOTNET@DISCUSS.DEVELOP.COM> > > HiThe min and max effect will not cause an error but the main problem is that I have multiple entries in the inserted table and i am trying to pull one value.Here is my trigger at the moment and it is an obscenity. I am really stuck what to do on this one:> USE ncontinuity2> IF EXISTS (SELECT name FROM sysobjects WHERE name = 'updateContactCallTreeTotalsTrigger' AND type = 'TR')> DROP TRIGGER updateContactCallTreeTotalsTrigger> GO> CREATE TRIGGER updateContactCallTreeTotalsTrigger> ON CallTreeContacts> AFTER INSERT, UPDATE, DELETE> AS> SET NOCOUNT ON> DECLARE @roleType int,> @contactUid uniqueidentifier> > IF EXISTS (SELECT * FROM Inserted) AND NOT EXISTS (SELECT * FROM Deleted)> BEGIN> IF((SELECT COUNT(*) FROM inserted) > 0)> RETURN;> SET @roleType = (SELECT calltreecontactroleid FROM inserted i)> SET @contactUid = (SELECT contactuid FROM inserted i)> END> IF EXISTS (SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted)> BEGIN> IF((SELECT COUNT(*) FROM inserted) > 0)> RETURN;> SET @roleType = (SELECT calltreecontactroleid FROM inserted i)> SET @contactUid = (SELECT contactuid FROM inserted i)> END> IF NOT EXISTS (SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted)> BEGIN> IF((SELECT COUNT(*) FROM inserted) > 0)> RETURN;> SET @roleType = (SELECT calltreecontactroleid FROM deleted d)> SET @contactUid = (SELECT contactuid FROM deleted)> END> IF (@roleType = 1)> BEGIN> UPDATE Contacts> SET planleadercount = > (SELECT count(cc.uid) > FROM calltreecontacts cc > INNER JOIN CallTrees ct> ON cc.calltreeuid = ct.Uid> INNER JOIN plans p> ON p.Uid = ct.planUid> WHERE cc.contactUid = @contactUid and cc.calltreecontactroleid = 1 and p.deleted = 'false')> END> ELSE IF (@roleType = 2)> BEGIN> UPDATE Contacts> SET planleadercount = > (SELECT count(cc.uid) > FROM calltreecontacts cc > INNER JOIN CallTrees ct> ON cc.calltreeuid = ct.Uid> INNER JOIN plans p> ON p.Uid = ct.planUid> WHERE cc.contactUid = @contactUid and cc.calltreecontactroleid = 2 and p.deleted = 'false')> END> ELSE IF (@roleType = 3)> BEGIN> UPDATE Contacts> SET planleadercount = > (SELECT count(cc.uid) > FROM calltreecontacts cc > INNER JOIN CallTrees ct> ON cc.calltreeuid = ct.Uid> INNER JOIN plans p> ON p.Uid = ct.planUid> WHERE cc.contactUid = @contactUid and cc.calltreecontactroleid = 3 and p.deleted = 'false')> END> ELSE IF (@roleType = 4)> BEGIN> UPDATE Contacts> SET planleadercount = > (SELECT count(cc.uid) > FROM calltreecontacts cc > INNER JOIN CallTrees ct> ON cc.calltreeuid = ct.Uid> INNER JOIN plans p> ON p.Uid = ct.planUid> WHERE cc.contactUid = @contactUid and cc.calltreecontactroleid = 4 and p.deleted = 'false')> END> [EMAIL PROTECTED]> Date: Tue, 12 Feb 2008 11:00:43 -0500> From: [EMAIL PROTECTED]> Subject: Re: [ADVANCED-DOTNET] Trigger and multiple values in the inserted table> To: ADVANCED-DOTNET@DISCUSS.DEVELOP.COM> > Hi Paul,> > You could try something like this:> > Select> @RoleType = Min(CallTreeContactRoleId)> From> CallTreeContacts CTC> Join Inserted I On CTC.Uid = I.UID> > A few points I have to make:> - You may get multiple role types - without knowing more about your trigger,> I cannot help you.> - If you can eliminate the use of a variable, and do an insert/select, for> multiple role types, that may be better.> - Experiment with Max and Distinct instead of the Min function, and think> about the semantic differences.> - In statements are expensive, using a join will improve performance, hence> reducing the time for the insert statement to complete.> > If you can provide the source for your trigger, I might be able to help you> more.> > Richard> -----Original Message-----> From: Discussion of advanced .NET topics.> [mailt> ===================================> This list is hosted by DevelopMentor® http://www.develop.com> > View archives and manage your subscription(s) at http://discuss.develop.com _________________________________________________________________ Who's friends with who and co-starred in what? http://www.searchgamesbox.com/celebrityseparation.shtml =================================== This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
