Email Trigger in SQL 2005

Hot Topic

Report Builder: Is there a way to filter a filter?
SQL 2000 alongside SQL Express?
Problems creating a stored procedure in Management Studio
Flat file - row delimiter problem
Beginner T-SQL Question
Error message "Interface not registered"
Shifting past the interim SQL2K
Upgrade from RS 2000 to RS 2005 - client print problem 0x8007F304
SQL Express user permissions?
Cannot see SQL Server in my pc

SQL Server

Errors in High-level Relational Engine and OLAP storage Engine
Select Error in Case
Help with SOAP Lite client error problems - CGI code
How to run only certain tasks
Cascading Parameter issue with RS2005... It worked in RS2000
Why I cant not deploy my project?
Data Diagrams
SQL Server does not exist or access denied
SQL Server and Windows Update
SQL Reporting Services and SiteMinder (SSO product by Netegrity)

Email Trigger in SQL 2005

I am new to developing as will be evident from this post. Your help will be greatly appreciated.

I am developing an intranet for our company using ASP.NET with a SQL backend. I am currently working on a suggestion box form.

I would like to have an email sent to specific persons when a new entry is made in the suggestion table. I have been able to configure the trigger and generate the email (This was easy). Formatting the email has proven more difficult to resolve.

The format I would like is somewhat as follows:

F_NAME L_NAME submitted the following suggestion:

IDEA

BENEFIT

APPROVE | DECLINE

The items in RED are columns in the table and the Blue Underlines are hyperlinks to change the Status column in the table.

How can I generate the email to contain the data from the inserted record and in the above format.

Being new at this I only now how to send a static email advising that the entry has been made.

Any help creating the dynamic email form for this trigger will be greatly appreciated.

Lastly, what books are most helpful for SQL, ASP.NET, and VBScript referencing and examples

Thanks

Answer this question

Email Trigger in SQL 2005

  • EK4527

    Your trigger doesn't handle for multiple rows being affected by the DML statement or no rows affected or concurrency issues (use of IDENT_CURRENT). Instead of using IDENT_CURRENT, you need to query the inserted virtual table to get the inserted information. And you need to use a cursor loop to send email for each affected row for example.

  • StevePee

    In SQL 2k I was not a fan of triggering the email via a trigger becasue if the mail server was down there was a problem ,that the transaction could lock up all the other processes that were supposed to use the table, but as Service Broker was introduced to send mails, that is another thing for me, lets try this suggestion here to see if it works:

    DROP TABLE ProposedNames

    CREATE TABLE ProposedNames

    (

    ProposedNameId INT IDENTITY(1,1) PRIMARY KEY,

    Firstname VARCHAR(100),

    Lastname VARCHAR(100),

    Accepted BIT

    )

    CREATE TRIGGER INS_ProposedNames

    ON ProposedNames

    FOR INSERT

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @SomeHTMLText NVARCHAR(MAX)

    DECLARE @RowCount INT

    DECLARE @COUNTER INT

    SET @Counter = 1

    CREATE TABLE #Names

    (

    Counted INT IDENTITY(1,1),

    ProposedNameId INT,

    Firstname VARCHAR(100),

    Lastname VARCHAR(100),

    )

    INSERT INTO #Names

    (

    ProposedNameId ,

    Firstname,

    Lastname

    )

    SELECT

    ProposedNameId ,

    Firstname,

    Lastname

    FROM INSERTED

    SET @ROWCOUNT = @@ROWCOUNT

    WHILE @ROWCOUNT >= @Counter

    BEGIN

    SEt @SomeHTMLText = ''

    SELECT @SomeHTMLText = '<HTML><BODY>' +

    'The following names were proposed<br><br>' +

    @SomeHTMLText +

    'FirstName: ' + Firstname + '<br><br>' +

    'LastName: ' + Lastname + '<br><br>' +

    '<a href="http://www.someserver.com/Page.aspx Action=Accept&ID=' + CAST(ProposedNameId AS VARCHAR(10)) + '"> I Accept</a> | ' +

    '<a href="http://www.someserver.com/Page.aspx Action=Decline&ID=' + CAST(ProposedNameId AS VARCHAR(10)) + '"> I Decline</a>' +

    '</BODY></HTML>'

    FROM #Names

    WHERE Counted = @Counter

    EXEC msdb..sp_send_dbmail

    @recipients= 'TheRecipient@domain.com',

    @subject = 'Its up to you',

    @body = @SomeHTMLText,

    @body_format = 'HTML'

    SET @Counter = @Counter +1

    END

    DROP TABLE #Names

    END

    GO

    --Try it:

    INSERT INTO ProposedNames

    (Firstname,LastName)

    VALUES ('Jens', 'Susmeyer')

    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

  • Peter Scheffel

    I wish to thank Jens Suessmeyer for the helpful infomraiton. Although the information provided did not fully resolve my issue the information provided me with enough knowledge to research and discover the solutions provided below.

    I have created two triggers. The first trigger sends and e-mail message to the person in charge of reviewing the submission from the web form. The second trigger sends a confirmation e-mail to the person whom submitted the suggestion.

    CREATE Trigger [triggername]

    on [tablename]

    for insert

    as

    declare @text varchar(max)

    declare @name varchar(max)

    declare @idea varchar(max)

    declare @benefit varchar(max)

    set @text = ''

    set @name = ''

    set @idea = ''

    set @benefit = ''

    select @name = firstname + ' ' + lastname, @idea = idea, @benefit = benefit

    from tablename

    where id = ident_current('tablename')

    set @text = '<html><body>' + 'The following Bright Idea was submitted by ' +

    @name + ':<p>' + '<b>Idea: </b>' + @idea + '<p>' + '<b>Benefit: </b>' +

    @benefit + '<p>' + 'Please reveiw this idea as soon as possible.' +

    '</body></html>'

    exec msdb.dbo.sp_send_dbmail

    @profile_name = 'profilename',

    @recipients = 'recipientemailaddress',

    @subject = 'New Bright Idea Submitted',

    @body = @text,

    @body_format = 'HTML'

    CREATE Trigger [triggername]

    on [tablename]

    for insert

    as

    declare @text varchar(max)

    declare @name varchar(max)

    declare @idea varchar(max)

    declare @benefit varchar(max)

    declare @email varchar(max)

    set @text = ''

    set @name = ''

    set @idea = ''

    set @benefit = ''

    set @email = ''

    select @name = name, @idea = idea, @benefit = benefit, @email = email

    from tablename

    where id = ident_current('tablename')

    set @text = '<html><body>' + @name + ',<p>Thank you for submitting your idea ' +

    'using our web based service. Your suggestion has been received and will be reveiwed ' +

    'by our management staff in the next couple of weeks.<p>Below is a copy of the idea ' +

    'we received from you:<p>' + '<b>Idea: </b>' + @idea +

    '<p><b>Benefit: </b>' + @benefit + '<p>Once again we would like to thank you for ' +

    'submitting your idea.' +

    '</body></html>'

    exec msdb.dbo.sp_send_dbmail

    @profile_name = 'profilename',

    @recipients = @email,

    @subject = 'New Bright Idea Submitted',

    @body = @text,

    @body_format = 'HTML'
  • Email Trigger in SQL 2005
Answer this question