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)

Share

Share

Rate

4.5 (6)