[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • 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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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