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
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', 'Sü?meyer')
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
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 + ':
' + '<b>Idea: </b>' + @.idea + '
' + '<b>Benefit: </b>' +
@.benefit + '
' + '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 + ',
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.
Below is a copy of the idea ' +
'we received from you:
' + '<b>Idea: </b>' + @.idea +
'
<b>Benefit: </b>' + @.benefit + '
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'
|||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.
