none
SQL mail triggers

    Question

  • I am using SQL 2008 R2.  

    I would like to setup a trigger to send an email after an email is added to a table.  The table name would be 'iPad' and the email column is 'text_box_3'.

    Any help would be very grateful.

    Thanks,

    Matt

    Saturday, January 25, 2014 9:03 PM

Answers

  • I am using SQL 2008 R2.  

    I would like to setup a trigger to send an email after an email is added to a table.  The table name would be 'iPad' and the email column is 'text_box_3'.

    Any help would be very grateful.

    Thanks,

    Matt

    Below is an example trigger that uses the default Database Mail profile to send an email for each inserted row.  You can configure Database Mail using SSMS or with a T-SQL script.  See http://weblogs.sqlteam.com/dang/archive/2009/05/17/Database-Mail-Configuration.aspx for an example script.

    CREATE TRIGGER TR_iPad
    ON dbo.iPad
    FOR INSERT
    AS
    DECLARE @email varchar(255);
    
    DECLARE inserted_emails CURSOR LOCAL FAST_FORWARD FOR
    SELECT text_box_3
    FROM inserted;
    
    OPEN inserted_emails;
    WHILE 1 = 1
    BEGIN
    	FETCH NEXT FROM inserted_emails INTO @email;
    
    	IF @@FETCH_STATUS = -1 BREAK;
    
    	EXECUTE msdb.dbo.sp_send_dbmail
    		@subject = 'Email address inserted',
    		@recipients = @email,
    		@query = 'Confirmation email';
    
    END;
    
    CLOSE inserted_emails;
    DEALLOCATE inserted_emails;
    
    GO


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, January 25, 2014 9:40 PM
  • A little more on that pattern.  You always must account for multi-row inserts.  Here you would use a CURSOR against the INSERTED virtual table to send an email for each inserted row.

    eg

    CREATE TRIGGER dbo.Insert_trigger
    ON dbo.ipad
    FOR INSERT
    AS
    BEGIN
        SET NOCOUNT ON;
    
        declare @profile_name sysname = 'MyMailProfile';
    
        declare c cursor local for 
            select email_to,email_subject,email_body --use your actual column names 
            from inserted;
    
        open c;
        declare @to nvarchar(255), @subject nvarchar(2000), @body nvarchar(max)
        
        fetch next from inserted into @to, @subject, @body
        while @@FETCH_STATUS = 0
        begin
            exec msdb.dbo.sp_send_dbmail
              @to, 
              @profile_name,
              @subject, 
              @body;
    
            fetch next from inserted into @to, @subject, @body;
        end
        close c;
        deallocate c;
    end
    GO

    David


    David http://blogs.msdn.com/b/dbrowne/

    Saturday, January 25, 2014 9:43 PM

All replies

  • 1) Setup the database mail first.

    2)

    CREATE TRIGGER dbo.Insert_trigger
    ON dbo.ipad
    FOR INSERT
    AS
    BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1 FROM inserted)
     BEGIN
      EXEC msdb.dbo.sp_send_dbmail
         @to, 
         @profile_name,
         @subject, 
         @body;
        END
    END
    GO

    Pass inputs to the procedure inside the trigger as required.

    This should do it.

    Good Luck :) Visit www.sqlsaga.com for more t-sql code snippets and BI related how to's.


    • Edited by sqlsaga Saturday, January 25, 2014 9:37 PM
    Saturday, January 25, 2014 9:36 PM
  • I am using SQL 2008 R2.  

    I would like to setup a trigger to send an email after an email is added to a table.  The table name would be 'iPad' and the email column is 'text_box_3'.

    Any help would be very grateful.

    Thanks,

    Matt

    Below is an example trigger that uses the default Database Mail profile to send an email for each inserted row.  You can configure Database Mail using SSMS or with a T-SQL script.  See http://weblogs.sqlteam.com/dang/archive/2009/05/17/Database-Mail-Configuration.aspx for an example script.

    CREATE TRIGGER TR_iPad
    ON dbo.iPad
    FOR INSERT
    AS
    DECLARE @email varchar(255);
    
    DECLARE inserted_emails CURSOR LOCAL FAST_FORWARD FOR
    SELECT text_box_3
    FROM inserted;
    
    OPEN inserted_emails;
    WHILE 1 = 1
    BEGIN
    	FETCH NEXT FROM inserted_emails INTO @email;
    
    	IF @@FETCH_STATUS = -1 BREAK;
    
    	EXECUTE msdb.dbo.sp_send_dbmail
    		@subject = 'Email address inserted',
    		@recipients = @email,
    		@query = 'Confirmation email';
    
    END;
    
    CLOSE inserted_emails;
    DEALLOCATE inserted_emails;
    
    GO


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, January 25, 2014 9:40 PM
  • A little more on that pattern.  You always must account for multi-row inserts.  Here you would use a CURSOR against the INSERTED virtual table to send an email for each inserted row.

    eg

    CREATE TRIGGER dbo.Insert_trigger
    ON dbo.ipad
    FOR INSERT
    AS
    BEGIN
        SET NOCOUNT ON;
    
        declare @profile_name sysname = 'MyMailProfile';
    
        declare c cursor local for 
            select email_to,email_subject,email_body --use your actual column names 
            from inserted;
    
        open c;
        declare @to nvarchar(255), @subject nvarchar(2000), @body nvarchar(max)
        
        fetch next from inserted into @to, @subject, @body
        while @@FETCH_STATUS = 0
        begin
            exec msdb.dbo.sp_send_dbmail
              @to, 
              @profile_name,
              @subject, 
              @body;
    
            fetch next from inserted into @to, @subject, @body;
        end
        close c;
        deallocate c;
    end
    GO

    David


    David http://blogs.msdn.com/b/dbrowne/

    Saturday, January 25, 2014 9:43 PM