Help with UPDATE trigger

[silversubey]

I am trying to setup a trigger that sends an email if a field is changed to specific data. The trigger works when ever the field is changed, but I only need an email if the field is changed to 'In Review'
Any help is greatly appreciated.

-- Create the trigger
CREATE TRIGGER reviewntc

--indicate which table the trigger is to be executed on
ON CltDue

--indicate that this an UPDATE Trigger
FOR UPDATE
AS

IF UPDATE(CDSTATUS )
BEGIN
--holds the changes
declare @CDStatus varchar(40), @CDClientName varchar (40), @CDEventDesc varchar (40)
--grabs the data that we need
SELECT @CDStatus = CDStatus, @CDClientName = CDClientName, @CDEventDesc = CDEventDesc
FROM inserted
declare @rc int, @mymessage nvarchar(4000), @mysubject varchar (4000)
SET @mymessage = N'The '+@CDClientName +' "'+@CDEventDesc +'" project has been changed to '+@CDStatus+''
exec @rc = master.dbo.xp_s mtp_sendmail
@FROM = N'sender@domain .com',
@FROM_NAME = N'sender',
@TO = N'rcpt@domain.c om',
@subject = N'A project has been changed to "In review"',
@message = @mymessage,
@type = N'text/plain',
@server = N'email serverl'
select RC = @rc
END

go

[debasisdas]

You need to check for the updated value.

[silversubey]

I'm sorry, but I am new to SQL.
Where and how do I insert CHECK

[silversubey]

Got it.

I added an IF
Here is the code I have if anyone needs

-- Drop the trigger if it already exists
IF EXISTS(
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[reviewntc]') AND
OBJECTPROPERTY( id, N'IsTrigger') = 1)
DROP TRIGGER [reviewntc]
GO

-- Create the trigger
CREATE TRIGGER reviewntc

--indicate which table the trigger is to be executed on
ON CltDue

--indicate that this an UPDATE Trigger
FOR UPDATE
AS

IF UPDATE(CDSTATUS )
BEGIN
set nocount on
--holds the changes
declare @CDStatus varchar(40), @CDClientName varchar (40), @CDEventDesc varchar (40)
--grabs the data that we need
SELECT @CDStatus = CDStatus, @CDClientName = CDClientName, @CDEventDesc = CDEventDesc
FROM inserted
IF @CDStatus = 'In Review'
BEGIN
declare @rc int, @mymessage nvarchar(4000), @mysubject varchar (4000)
SET @mymessage = N'The '+@CDClientName +' "'+@CDEventDesc +'" project has been changed to '+@CDStatus+''
exec @rc = master.dbo.xp_s mtp_sendmail
@FROM = N'sender@domain ',
@FROM_NAME = N'sender',
@TO = N'rcpt@domain',
@subject = N'A project has been changed to "In review"',
@message = @mymessage,
@type = N'text/plain',
@server = N'email server'
select RC = @rc

END
END
go