Technical Article

Send SMS from a trigger

,

-- **************************************************
-- given a sample table:
CREATE TABLE [dbo].[TestTriggerSMS] 
(
[test_field] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]

go


-- **************************************************
create trigger tr_Update_TestTriggerSMS on [dbo].[TestTriggerSMS] 
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 only if update 1 row
begin
if update(test_field)-- notification only if update [test_field] field
begin
print ' [test_field] field updated'
declare @old_value varchar(50)
, @new_value varchar(50)

declare @tab char(1)
set @tab = char(9)


declare @message varchar(555)

set @old_value = (select test_field from deleted)
set @new_value  = (select test_field from inserted)

set @message = 'TestTriggerSMS updated:' + char(13) + 
'Old Value ' + @tab  +  @tab + '[' + @old_value + ']'+ char(13) +
'New Value ' + @tab  +  @tab + '[' + @new_value + ']'

exec master.dbo.xp_sendmail @recipients = 'siccolo_mobile_management@yahoo.com'
, @message = @message
, @subject = 'TestTriggerSMS updated!'

-- send SMS to cell phone --
/*
Teleflip.com now provides SMS service. 
To use teleflip just email the SMS message to the following email address: 
<10 digit cell number>@teleflip.com
-- or --
T-Mobile: phonenumber@tmomail.net 
Virgin Mobile: phonenumber@vmobl.com 
Cingular: phonenumber@cingularme.com 
Sprint: phonenumber@messaging.sprintpcs.com 
Verizon: phonenumber@vtext.com 
Nextel: phonenumber@messaging.nextel.com 

where phonenumber = your 10 digit phone number 
*/exec master.dbo.xp_sendmail @recipients = '4108441212@cingularme.com'
, @message = @message
, @subject = 'TestTriggerSMS updated!'
end
end

set nocount off
go
-- more articles at <a href="http://www.siccolo.com/articles.html">Siccolo Articles</a>

Rate

4.5 (6)

You rated this post out of 5. Change rating

Share

Share

Rate

4.5 (6)

You rated this post out of 5. Change rating