• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Email Trigger

As you will see in code below I want to send two emails when INSERT TRIGGER fires.  The first - "email" send works but the second - "oemail" doesnt.  Any advice gratefully received...

USE [ACMData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Trigger [dbo].[afterHBid]
ON [dbo].[HBids]
AFTER INSERT
AS
BEGIN
DECLARE @@body varchar(255),
      @@Head nvarchar(50),
      @@Title nvarchar(50),
      @@bid nvarchar(10),
      @@bidder nvarchar(50),
      @@lotno nvarchar(4),
      @@email varchar(50),
      @@oemail varchar(50)
SELECT @@bidder=Bidder,
      @@lotno=LotNo,
      @@head=header,
      @@bid=cast(bid as nvarchar),
      @@email=BEmail,
      @@oemail=BUEmail
FROM INSERTED
SET NOCOUNT ON;
IF (@@oemail = N'') SELECT @@oemail = NULL
If Len(@@email)>0
BEGIN
      SET @@body = @@bidder + CHAR(10) + 'The Organisers of the Care for Casualties Silent Auction confirm receipt of your bid of GBP' + @@bid + ' for Lot No ' + @@lotno + ' and remind you that the Auction closes at midnight on Saturday 8th September.' + CHAR(10) + CHAR(10) + 'The Committee of Care for Casualties.'
      SET @@Title = 'LotNo: ' + @@LotNo + ' ' + CHAR(45) + ' ' + @@head
      EXEC msdb.dbo.sp_send_dbmail @Profile_Name='PlumProf',
      @recipients = @@email,
      @subject =  @@Title,
      @body = @@body
END
If @@oemail <> Null
BEGIN
      SET @@body = 'The Organisers of the Care for Casualties Silent Auction advise that your bid has been exceeded and encourage you to bid again.' + CHAR(10) + CHAR(10) + 'The Committee of Care for Casualties.'
      SET @@Title = 'LotNo: ' + @@LotNo + ' ' + CHAR(45) + ' ' + @@head
      EXEC msdb.dbo.sp_send_dbmail @Profile_Name='PlumProf',
      @recipients = @@oemail,
      @subject =  @@Title,
      @body = @@body
END
END
0
eharford
Asked:
eharford
  • 2
1 Solution
 
sachitjainCommented:
I think your condition {If @@oemail <> Null} won't work

Try
If @@oemail is not Null
0
 
sachitjainCommented:
USE [ACMData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Trigger [dbo].[afterHBid]
ON [dbo].[HBids]
AFTER INSERT
AS
BEGIN
DECLARE @@body varchar(255),
      @@Head nvarchar(50),
      @@Title nvarchar(50),
      @@bid nvarchar(10),
      @@bidder nvarchar(50),
      @@lotno nvarchar(4),
      @@email varchar(50),
      @@oemail varchar(50)
SELECT @@bidder=Bidder,
      @@lotno=LotNo,
      @@head=header,
      @@bid=cast(bid as nvarchar),
      @@email=BEmail,
      @@oemail=BUEmail
FROM INSERTED
SET NOCOUNT ON;
IF (@@oemail = N'') SET @@oemail = NULL
If Len(@@email)>0
BEGIN
      SET @@body = @@bidder + CHAR(10) + 'The Organisers of the Care for Casualties Silent Auction confirm receipt of your bid of GBP' + @@bid + ' for Lot No ' + @@lotno + ' and remind you that the Auction closes at midnight on Saturday 8th September.' + CHAR(10) + CHAR(10) + 'The Committee of Care for Casualties.'
      SET @@Title = 'LotNo: ' + @@LotNo + ' ' + CHAR(45) + ' ' + @@head
      EXEC msdb.dbo.sp_send_dbmail @Profile_Name='PlumProf',
      @recipients = @@email,
      @subject =  @@Title,
      @body = @@body
END
If @@oemail is not Null
BEGIN
      SET @@body = 'The Organisers of the Care for Casualties Silent Auction advise that your bid has been exceeded and encourage you to bid again.' + CHAR(10) + CHAR(10) + 'The Committee of Care for Casualties.'
      SET @@Title = 'LotNo: ' + @@LotNo + ' ' + CHAR(45) + ' ' + @@head
      EXEC msdb.dbo.sp_send_dbmail @Profile_Name='PlumProf',
      @recipients = @@oemail,
      @subject =  @@Title,
      @body = @@body
END
END
0
 
eharfordAuthor Commented:
Brilliant - many thanks. Its always the silly little things that catch you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now