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
