|
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
|