Here is a link to the now infamous trigger: http://rafb.net/p/xYOA4j39.html [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 _________________________________________________________________ Get Hotmail on your mobile, text MSN to 63463! http://mobile.uk.msn.com/pc/mail.aspx =================================== This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
