February 15, 2008 at 8:10 am
The trigger below will send the correct e-mail twice for Yes and an empty e-mail twice for No:
CREATE TRIGGER [APPROVAL2] ON dbo.Results_Appr1 FOR INSERT AS IF (SELECT COUNT(*) FROM inserted) = 1 BEGIN declare @Commentid varchar(1000) declare @Approvedid varchar(1000) declare @CPEid varchar(1000) declare @body varchar(3000) SELECT @Commentid = GG_ID, @Approvedid = Approved, @CPEid = CPE
FROM inserted
IF @Approvedid = 'Yes'
SET @body = 'New Green Geer Exception Request has been Level 1 approved. To view the submittal go to: http://web-cpc-03.pwrutc.com/mpe/green_geer_exception_request_appr2.asp?ID=' + @CommentID + '&CPE=' + @CPEid EXEC master..xp_sendmail
@recipients = @CPEid,
@subject = 'Green Geer Exception Request',
@message = @body
END
IF @Approvedid = 'No'
SET @body = 'New Green Geer Exception Request has been not approved. To view the submittal go to: http://web-cpc-03.pwrutc.com/mpe/GreenGeerExceptionRequestApprovals.asp?ID=' + @CommentID EXEC master..xp_sendmail
@recipients = 'Eleanor.vanace@pwr.utc.com',
@subject = 'Green Geer Exception Request',
@message = @body
February 15, 2008 at 8:14 am
try
CREATE TRIGGER [APPROVAL2] ON dbo.Results_Appr1
FOR INSERT
AS
IF ( SELECT COUNT(*)
FROM inserted
) = 1
BEGIN
declare @Commentid varchar(1000)
declare @Approvedid varchar(1000)
declare @CPEid varchar(1000)
declare @body varchar(3000)
SELECT @Commentid = GG_ID
, @Approvedid = Approved
, @CPEid = CPE
FROM inserted
IF @Approvedid = 'Yes'
BEGIN
SET @body = 'New Green Geer Exception Request has been Level 1 approved. To view the submittal go to: http://web-cpc-03.pwrutc.com/mpe/green_geer_exception_request_appr2.asp?ID='
+ @CommentID + '&CPE=' + @CPEid
EXEC master..xp_sendmail @recipients = @CPEid,
@subject = 'Green Geer Exception Request',
@message = @body
END
IF @Approvedid = 'No'
BEGIN
SET @body = 'New Green Geer Exception Request has been not approved. To view the submittal go to: http://web-cpc-03.pwrutc.com/mpe/GreenGeerExceptionRequestApprovals.asp?ID='
+ @CommentID
EXEC master..xp_sendmail @recipients = 'Eleanor.vanace@pwr.utc.com',
@subject = 'Green Geer Exception Request',
@message = @body
END
END
Your original version was executing the xp_sendmail twice, it is better to lay out code to see how things are nested.
Regards,
Andras
February 15, 2008 at 8:24 am
The 'Yes' section now works perfectly, but the 'No section send tqo empty e-mails.
Also, the last END statement had to be removed in order to pass the syntax text.
HELP
February 15, 2008 at 8:39 am
eleanor.vanace (2/15/2008)
The 'Yes' section now works perfectly, but the 'No section send tqo empty e-mails.Also, the last END statement had to be removed in order to pass the syntax text.
HELP
Hm, interesting, the statement I sent does need the extra end, did you paste the whole content? Could you post your full trigger please? (It is worth to lay out the code and match up blocks, so you know which parts are part of an if statement, and which parts are executed unconditionally).
Regards,
Andras
February 15, 2008 at 8:42 am
It looks to me like the No section might send an empty e-mail if the inserted CommentID is null. Check for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 15, 2008 at 8:48 am
You are absolutely correct. I had missed one of the BEGIN's. It works perfectly now. I can't tell you how greatful I am! Thanks, E!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply