Mobile Management For SQL Server(s!) - Siccolo - SQL Management Tool
Main
How to Notify User via SMS from SQL trigger (send EMail/Text Message):
You can send SMS messages using a SQL Server database server with the help of SQL queries. It is interesting that often, without any external application, you can create powerful SMS enabled solutions. You can do this with the help of database triggers or stored procedures. With this approach you can easily set up SMS to e-mail gateway.
 
For this article, test table was created:
	CREATE TABLE [dbo].[TestTriggerSMS] 
	(
		[test_field] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
	) ON [PRIMARY]
	

Side Note:
Trrigers - are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view.
Triggers are powerful tools that sites can use to enforce their business rules automatically when data is modified.
Triggers can extend the integrity checking logic of SQL Server constraints, defaults, and rules, although constraints and defaults should be used instead whenever they provide all the needed functionality.
Tables can have multiple triggers.
The CREATE TRIGGER statement can be defined with the FOR UPDATE, FOR INSERT, or FOR DELETE clauses to target a trigger to a specific class of data modification actions. When FOR UPDATE is specified, the IF UPDATE (column_name) clause can be used to target a trigger to updates affecting a particular column.

In this article, FOR UPDATE trigger is used:
	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
	

Article keywords: create trigger, xp_sendmail, trigger, if update(), inserted, deleted, for update, SMS, SQL Server, INSERT INTO, UPDATE, set nocount on


Back To Articles Page

Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to siccolo_mobile_management@yahoo.com    Greg Dubinovsky 2006
or share your thoughts at Siccolo Blog

Web being sponsor - Mid-Atlantic Processing. Well being sponsor - Clarity MediSpa. Hairless sponsor - Clarity MediSpa Laser Hair Removal.