Update Trigger +where clause

Hi.

I have an issue with my tsql - I want to create a trigger based on change to a certain column change equal a certain field, then a take a field from the updated row and use it to send an email to.

Would any one like help me or make a suggestion on how to do it?

Any Help would be appreciated.

I have revised my code, please point out where I am still going wrong.

Thanks,

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER trigger [tr_NewLeadSMS_Unvers] on [dbo].[lead]
for update
as
set nocount on

declare @rows int
select @rows = count(*) from inserted

-- send notification if TestTriggerSMS table is being updated

if @rows=1 -- notification for all the rows update
begin

declare @eventtypecode varchar(10)
declare @cellnumber varchar(13)

if update(eventtypecode)
begin
SET @eventtypecode = (select eventtypecode from inserted)

if @eventtypecode = 'versys'
begin
SET @cellnumber = (select cellno from inserted)

DECLARE @msg varchar(200)
SET @msg = '1440,' + @cellnumber + ',Thanks for...'

--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail
@recipients = N'bulksms@',
@copy_recipients= N'ctaljard@',
@body = @msg,
@subject = @cellnumber,
@profile_name = 'DBMailProfile'
end
END
set nocount off

No worries, got it eventually.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author: <Cameron Taljard>
-- Create date: <17 July 2008,,>
-- Description: <SMS to customer when the new lead is --inserted into the database>
-- =============================================

CREEATTE TRIGGER [tr_sms_versys] ON [dbo].[lead] AFTER INSERT AS

SET NOCOUNT ON

declare @rows int
select @rows = count(*) from inserted

IF @rows=1
BEGIN
IF update(eventtypecode)
BEGIN

DECLARE @eventcode varchar(20)
SET @eventcode = (select eventtypecode from inserted)

IF @eventcode = 'VERSYS'
begin
DECLARE @cellnumber varchar(20)
SET @cellnumber = (select cellno from inserted)

--DECLARE @I INT
--SET @I = CHARINDEX (' ' , @cellnumber )
--SET @cellnumber = SUBSTRING(@cellnumber,1,@i-1) + --LTRIM(SUBSTRING(@cellnumber,@I,len(@cellnumber)))
--PRINT @cellnumber

--DECLARE @II INT
--SET @II = CHARINDEX (' ' , @cellnumber )
--SET @cellnumber = SUBSTRING(@cellnumber,1,@II-1) + --LTRIM(SUBSTRING(@cellnumber,@II,len(@cellnumber)))
--PRINT @cellnumber


SET @cellnumber = '+27' + right(@cellnumber,9)
PRINT @cellnumber

DECLARE @msg varchar(200)
--db_mail message
--SET @msg = '1440,' + @cellnumber + ',Thank you for --choosing.....';

--table mail message
SET @msg = 'Thank you for choosing...'

INSERT [172.23.0.105].mdb.dbo.CA_SMSLogout
(Cellno,messagestring,log_time,notificationStatus)
VALUES('@cellnumber',@msg,getdate(),0)

--// CHANGE THE VALUE FOR @recipients
-- EXEC msdb.dbo.sp_send_dbmail
--@recipients = N'bulksms@...',
--@copy_recipients= N'ctaljard@...',
--@body = @msg,
--@subject = @cellnumber,
--@profile_name = 'DBMailProfile'
end
end
END

Thanks anyway.