CREATE TRIGGER dbo.StageChanged ON CM.dbo.tLEAD AFTER UPDATE
AS
SET NOCOUNT ON
DECLARE @Stage nvarchar(50)
DECLARE @Subject nvarchar(100)
DECLARE @ProjectNumber nvarchar(15)
DECLARE @GP nvarchar(15)
DECLARE @Job nvarchar(4000)

SELECT 
@Subject = l.Subject,
@ProjectNumber =ISNULL(pn.ProjectNumber, 'blank'),
@GP= CONVERT(varchar(15),CONVERT(money, ISNULL(tc.TotalCost,0))-CONVERT(money,ISNULL(sc.Subcontracted,0)),1),
@Stage=s.LeadStageName
FROM CM.dbo.tLead l (nolock) 
LEFT JOIN 
(SELECT MAX(pn2.FieldValue) As [ProjectNumber] , pn2.ObjectFieldSetKey
FROM 
CM.dbo.tFieldValue pn2 (nolock) 
INNER JOIN CM.dbo.tFieldDef fd2  (nolock) ON fd2.FieldDefKey=pn2.FieldDefKey
WHERE fd2.FieldName= 'Opportunity_UD_Project_Number'
GROUP BY pn2.ObjectFieldSetKey) pn
ON l.CustomFieldKey=pn.ObjectFieldSetKey
LEFT JOIN 
	(SELECT MAX(tc2.FieldValue) As [TotalCost] , tc2.ObjectFieldSetKey
	FROM 
	CM.dbo.tFieldValue tc2 (nolock) 
	INNER JOIN CM.dbo.tFieldDef fd5 (nolock) ON fd5.FieldDefKey=tc2.FieldDefKey
	WHERE fd5.FieldName= 'Opportunity_UD_Client_total'
	GROUP BY tc2.ObjectFieldSetKey) tc
ON l.CustomFieldKey=tc.ObjectFieldSetKey
LEFT JOIN 
	(SELECT MAX(sc2.FieldValue) As [Subcontracted] , sc2.ObjectFieldSetKey
	FROM 
	CM.dbo.tFieldValue sc2 (nolock) 
	INNER JOIN CM.dbo.tFieldDef fd6 (nolock) ON fd6.FieldDefKey=sc2.FieldDefKey
	WHERE fd6.FieldName= 'Opportunity_UD_Subcontracted'
	GROUP BY sc2.ObjectFieldSetKey) sc
ON l.CustomFieldKey=sc.ObjectFieldSetKey
INNER JOIN CM.dbo.tLeadStage s ON l.LeadStageKey= s.LeadStageKey
INNER JOIN INSERTED i (nolock) ON i.LeadKey=l.LeadKey

Set @Job = 'The following opportunity has been changed to '+ @Stage+': '+ char(13)+ char(10)+'Subject: '+ @Subject + char(13) + char(10)+'Project Number: '+@ProjectNumber+char(13)+char(10)+ 'Estimated GP: '+@GP

If Update(LeadStageKey)
BEGIN
If EXISTS (SELECT i.LeadStageKey FROM INSERTED i INNER JOIN DELETED d ON d.LeadKey=i.LeadKey 
WHERE
( i.LeadStageKey<>d.LeadStageKey AND i.LeadStageKey=867)
)
BEGIN
EXEC master.dbo.xp_sendmail @recipients = 'jcantlon@bighrm.com', @subject = 'It''s a job!', @message = @Job
END
END