Trigger not sending email on current update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger not sending email on current update

Author

Topic

gjkiker
Starting Member

1 Post

Posted - 2009-08-06 : 12:15:35

Please help. It is not grabbing the most recent update, but does grab the last record in the DB. not sure what I am doign wrong.
thanks

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

ALTER TRIGGER [dbo].[EMAIL_VP_updated]
ON [dbo].[Media_request]

FOR UPDATE
AS

IF (SELECT COUNT(*) FROM inserted) = 1
BEGIN

declare @CommentID varchar(10)
declare @msg varchar(3000)
DECLARE @FN nvarchar(200)
DECLARE @LN nvarchar(200)
DECLARE @WAV nvarchar(200)
DECLARE @WAVTITLE nvarchar(200)
DECLARE @WAVVP nvarchar(200)
DECLARE @WAVVPEMAIL nvarchar(200)
DECLARE @STATUS_ nvarchar(200)

SELECT @CommentID = Media_rec_no,
@FN = media_first_name,
@LN = media_last_name,
@WAV = Media_justification,
@WAVVP = media_vp_fname,
@WAVVPEMAIL = media_vp_email,
@STATUS_ = media_approved

FROM Media_request

SET @msg = 'Dear ' + @WAVVP + ',

You have ' + @STATUS_ + 'ed the requested exception for the removable media lockdown for ' + @FN + ' ' + @LN + '

Thank you,

Information Security Governance Department
Information Risk Management Team'

--xp_sendmail is the extended sproc used to send the mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Waivers',
@recipients = 'gjkiker@cvty.com',
@subject = 'Waiver Request',
@body = @msg

Greg

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-08-06 : 12:23:47

shouldn't you be selecting from the inserted pseudotable and not from the whole table?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources