none
Sending an sms through the SQL server trigger

    Question

  • Hello people

    Can someone help. I am trying to send an sms through a trigger in SQL 2005. The front end of the application is ms access 2003. I know that the trigger is after insert trigger. The problem is the sms does not go through. Not even sure what is wrong with my stored procedure.

    This is my trigger

    USE [DBTEST]

    GO

    /****** Object:  Trigger [dbo].[BulkSms]    Script Date: 2017/02/17 11:06:55 AM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER TRIGGER [dbo].[BulkSms]

    ON [dbo].[Customer] AFTER INSERT

     

    AS

    Set nocount on

                    DECLARE @RegistrationNum  VARCHAR(MAX)

                    DECLARE @Cellnumber   VARCHAR(100)

                    DECLARE @TradingName  VARCHAR(100)

                    DECLARE @DateCreated    DATETIME

     

    SET @RegistrationNum = (SELECT RegNum FROM INSERTED)

    SET @Cellnumber = (SELECT CellNum FROM UPDATED)

    SET @TradingName = (SELECT TradingNam FROM UPDATED)

     

     

    BEGIN

      DECLARE @SmsBody    VARCHAR(500)

    SET @SmsBody = 'Your application has been captured with application no as' + @RegNum + 'was generated. You will be notified accordingly of the outcome of your application'

    END;

    Friday, April 21, 2017 9:00 AM

All replies

  • The problem is the sms does not go through.

    Hello,

    Not clear what you mean; through what? Your trigger code only assign a value to a variable; nothing more, so what should happen here, beside nothing?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, April 21, 2017 9:06 AM
  • Hi Futhi,

    In addition, you should add the execute available stored procedure to send an sms at the end of your trigger. For example:

    CREATE TRIGGER t_Pers 
    ON Person.Person 
    AFTER INSERT, UPDATE, DELETE 
    AS 
       EXEC msdb.dbo.sp_send_dbmail 
                            @profile_name = 'ApexSQLProfile', 
                            @recipients = 'marko.radakovic@apexsql.com' , 
                            @body = 'Data in AdventureWorks2012 is changed', 
                            @subject = 'Your records have been changed' 
    GO

    Reference: Get an alert when a certain record changes in SQL Server

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 21, 2017 10:15 AM
  • Nope - this is a bad approach for many reasons. First and foremost is the basic mistake in your logic. You assume a single row is inserted, when there can be any number (including zero) rows inserted. More importantly, at some point you should expect the transaction in which this trigger executes fails and is rolled back. Yet you cannot "recall" your message after sending, so someone receives a message about a non-existant application. And varchar(max) for @registrationnum is just pure laziness.

    Notifications like this should occur AFTER the creation of your "application" has been completed successfully.

    Friday, April 21, 2017 2:22 PM