table update trigger to send email ....problem with NULL?

Question

Post reply

table update trigger to send email ....problem with NULL?

john-157663

SSC Rookie

Points: 36
More actions
July 8, 2009 at 10:27 am

#223415

hi
using this forum and various other sources i have created the trigger below - which for the most part is working ok and as expected - however if a row in the table being queried has NULL values then the email sent is empty. After looking at this all day i think the cause of it is fields with NULL values
- can anyone help how to deal with the NULL values in the trigger?
USE [MYDB]
GO
/****** Object: Trigger [dbo].[UpdateMail] Script Date: 07/08/2009
15:00:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--- Create Trigger with name 'updatemail'
ALTER TRIGGER [dbo].[UpdateMail]
--
ON [dbo].[MYDB]
FOR UPDATE
AS
declare @OBJID varchar(10)
declare @Username varchar(50)
declare @Date varchar(50)
declare @Description varchar(350)
declare @SHAPE varchar (50)
declare @Lay1 varchar(100)
declare @Lay2 varchar(100)
declare @Lay3 varchar(100)
declare @Lay4 char(100)
declare @message varchar(max)
SELECT @OBJID = OBJID,
@Username = Username,
@Date = Date,
@Description = Description,
@SHAPE = SHAPE,
@Lay1 = Lay1,
@Lay2 = Lay2,
@Lay3 = Lay3,
@Lay4 = Lay4
FROM inserted
SET @message = 'New Entry in field OBJID = ' + @OBJID + '
' + 'New Entry in field Username = ' + @Username + '
' + 'New Entry in field Date = ' + @Date + '
' + 'New Entry in field Description = ' + @Description + '
' + 'New Entry in field SHAPE = ' + @SHAPE + '
' + 'New Entry in field Lay1 = ' + @Lay1 + '
' + 'New Entry in field Lay2 = ' + @Lay2 + '
' + 'New Entry in field Lay3 = ' + @Lay3 + '
' + 'New Entry in field Lay4 = ' + @Lay4
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MYDB MAIL',
@recipients = myemail@myemail.com,
@subject = 'MYDB table has been updated',
@body = @message

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

shahm10 SSCrazy Points: 2217 More actions · Answer 1

Instead of writing

SET ANSI_NULLS ON

write

SET ANSI_NULLS OFF

GSquared SSC Guru Points: 260824 More actions · Answer 2

shahm10 (7/14/2009)
Instead of writing
SET ANSI_NULLS ON
write
SET ANSI_NULLS OFF

ANSI_NULLS OFF is being deprecated. Microsoft recommends against using it.

Use either IsNull or Coalesce to handle the nulls in your string-build.

Would look like this:

' + 'New Entry in field Username = ' + isnull(@Username, '') + '

- 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

keyun SSCrazy Points: 2397 More actions · Answer 3

Is is possible to add INSERT INTO statement Before/After SEND_Mail to get updated ROW into audit table like who updated their reocords??

Please advice,

Jack Corbett SSC Guru Points: 184393 More actions · Answer 4

keyun (2/2/2010)
Is is possible to add INSERT INTO statement Before/After SEND_Mail to get updated ROW into audit table like who updated their reocords??
Please advice,

Yes, you can insert into an audit table either before or after sending an email.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184393 More actions · Answer 5

I'm also going to make my standard comment on most trigger posts. You do realize that your trigger will only work correctly when a single row is updated. If there is a multi-row update you'll only get the information from one row emailed out and you aren't guaranteed which row you will get. Here's a link[/url] to an article I wrote about triggers that explains why this is important.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

keyun SSCrazy Points: 2397 More actions · Answer 6

Jack - I will bother you last time.

Question - Does SQL 05 allow Temp (##) table when you create Trigger?

Please advice,

Jack Corbett SSC Guru Points: 184393 More actions · Answer 7

Yes, but I'm not sure it's a good idea.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

keyun SSCrazy Points: 2397 More actions · Answer 8

Jack - Thanks but I found my way around. Cool..Appriciated.