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 = '[email protected]'
				, @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: [email protected] 
			Virgin Mobile: [email protected] 
			Cingular: [email protected] 
			Sprint: [email protected] 
			Verizon: [email protected] 
			Nextel: [email protected] 
			
			where phonenumber = your 10 digit phone number 
			*/
			exec master.dbo.xp_sendmail @recipients = '[email protected]'
				, @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 [email protected]    Greg Dubinovsky 2006
or share your thoughts at Siccolo Blog

web sponsor - siccolo.com. well being sponsor - Enabling clinical and operational value across the continuum of care.