Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Triggers that use sp_send_dbmail (SQL Server 2005)

Author  Topic 

VPeters
Starting Member

20 Posts
Posted - 2007-01-19 : 15:58:58
Someone help! I am writing an 'AFTER INSERT, UPDATE' trigger on a table called mpi_Client. I created a table called mpi_HoldClientID. In my trigger, I am trucating this table and then saying:

INSERT INTO dbo.mpi_HoldClientID
SELECT i.ID
FROM inserted AS i

After that, I am using the @query argument of sp_send_dbmail to evaluate the data in the appropriate record in mpi_Client by joining on the mpi_HoldClientID.ID field and send an email with the results.

My problem is that SQL Server does not seem to like the INSERT INTO statement combined with the rest of the code. I can truncate and insert separately and it does exactly what it should. I can comment out the truncate and insert section and run the rest of the trigger, and I get the email with the expected query results. But I cannot run it all together. Can anyone help?

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-01-19 : 16:38:27
You haven't provided the code, so it's hard for us to imagine what the problem is.

Tara Kizer
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-01-19 : 19:53:52
Sending an email from a trigger sounds like a really bad idea.



CODO ERGO SUM
Go to Top of Page

VPeters
Starting Member

20 Posts
Posted - 2007-01-22 : 08:45:36
Below is some of the code. There are a few other IF statements that are evaluated, but aside from slight differences in @query (e.g., returning different fields), it's all the same.

Like I said, the trigger sends the email fine if I'm not inserting a record into the table first. It also inserts the record into the table as long as I'm not trying to send an email.

If sending an email via a trigger is not a good idea (why isn't it?), then what would be a good alternate? This is supposed to be a temporary fix for when our customers go online and fill out a form to request information. When a new record is inserted in the table, I need to send an email to the staff who will handle the request.

Thanks!


DECLARE
@DBName nvarchar(128),
@ProfileType int

SET @DBName = db_name()
SET @ProfileType =
(
SELECT cl.ProfileType
FROM dbo.mpi_Client AS cl
JOIN dbo.mpi_HoldClientID AS t ON cl.ID = t.ID
)


IF @DBName = 'AdvanceStore'
BEGIN

IF @ProfileType = 1 -- Contact Us form
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'vicki.peters@merion.com',
@subject = 'Custom Promotions - Email Sign Up Request',
@query = 'SELECT
[Date Entered] = pr.CreateDate,
[Profile Type] = ''Contact Us'',
[First Name] = cl.FirstName,
[Last Name] = cl.LastName,
[Company Name] = ISNULL(cl.CompanyName,''''),
[Department] = ISNULL(cl.Department,''''),
[Job Title] = ISNULL(cl.JobTitle,''''),
[Phone Number] = ISNULL(cl.PhoneNumber,''''),
[Phone Extension] = ISNULL(cl.PhoneExtension,''''),
[Email] = cl.Email,
[Preferred Contact Method] =
CASE
WHEN cl.ContactMethodID IS NULL THEN ''(none)''
WHEN cl.ContactMethodID = 1 THEN ''Phone''
ELSE ''Email''
END,
[Referral Method] = rm.[Name],
[Wants Mailing List] =
CASE
WHEN cl.MailingList = 1 THEN ''Yes''
ELSE ''No''
END
FROM AdvanceStore.dbo.mpi_HoldClientID AS hc
JOIN AdvanceStore.dbo.mpi_Client AS cl ON hc.ID = cl.ID
LEFT JOIN AdvanceStore.dbo.mpi_ClientXProfileResponse AS cpr
ON cl.ID = cpr.ClientID
LEFT JOIN AdvanceStore.dbo.mpi_ProfileResponse AS pr
ON cpr.ProfileResponseID = pr.ID
JOIN AdvanceStore.dbo.mpi_ReferralMethod AS rm
ON cl.ReferralMethodID = rm.ID',
@attach_query_result_as_file = 0,
@query_result_width = 256
END

ELSE IF ......
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-01-22 : 11:54:40
Please post complete code. I don't see any INSERT statements in what you posted, plus I don't see the trigger definition. In order for us to help, we need to look at all of the code and possibly duplicate the issue on our machines.

Tara Kizer
Go to Top of Page

VPeters
Starting Member

20 Posts
Posted - 2007-01-22 : 12:02:45
Okay, here it is.


USE AHS_QA

IF OBJECT_ID('mpi_Client.EmailFormRequest','TR') IS NOT NULL
DROP TRIGGER mpi_Client.EmailFormRequest
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO

ALTER TRIGGER dbo.EmailFormRequest ON dbo.mpi_Client
AFTER INSERT, UPDATE

AS

BEGIN


-- Delete prior record from dbo.mpi_HoldClientID
TRUNCATE TABLE dbo.mpi_HoldClientID


-- Insert ID field from inserted into dbo.mpi_HoldClientID.
-- Inserted is a virtual table that contains the values of the
-- record that was inserted into mpi_Client.
INSERT INTO dbo.mpi_HoldClientID
SELECT i.ID
FROM inserted AS i


-- Declare variables
DECLARE
@DBName nvarchar(128),
@ProfileType int


-- Set @DBName equal to current database name; this is so when sp_send_dbmail
-- executes from master DB, it knows from which database we are querying.
SET @DBName = db_name()

-- Set @ProfileType equal to profile type of newly inserted record.
SET @ProfileType =
(
SELECT cl.ProfileType
FROM dbo.mpi_Client AS cl
JOIN dbo.mpi_HoldClientID AS t ON cl.ID = t.ID
)


/************************************************************************************
IF CURRENT DATABASE IS ADVANCESTORE
************************************************************************************/
IF @DBName = 'AdvanceStore'
BEGIN
-- Evaluate @ProfileType. Depending on value returned, send an email with
-- specified data fields and subject line.
IF @ProfileType = 1 -- Contact Us form
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'vicki.peters@merion.com',
@subject = 'Custom Promotions - Email Sign Up Request',
@query =
'
SELECT
[Date Entered] = pr.CreateDate,
[Profile Type] = ''Contact Us'',
[First Name] = cl.FirstName,
[Last Name] = cl.LastName,
[Company Name] = ISNULL(cl.CompanyName,''''),
[Department] = ISNULL(cl.Department,''''),
[Job Title] = ISNULL(cl.JobTitle,''''),
[Phone Number] = ISNULL(cl.PhoneNumber,''''),
[Phone Extension] = ISNULL(cl.PhoneExtension,''''),
[Email] = cl.Email,

[Preferred Contact Method] =
CASE
WHEN cl.ContactMethodID IS NULL THEN ''(none)''
WHEN cl.ContactMethodID = 1 THEN ''Phone''
ELSE ''Email''
END,
[Referral Method] = rm.[Name],

[Wants Mailing List] =
CASE
WHEN cl.MailingList = 1 THEN ''Yes''
ELSE ''No''
END
FROM AdvanceStore.dbo.mpi_HoldClientID AS hc
JOIN AdvanceStore.dbo.mpi_Client AS cl ON hc.ID = cl.ID
LEFT JOIN AdvanceStore.dbo.mpi_ClientXProfileResponse AS cpr ON cl.ID = cpr.ClientID
LEFT JOIN AdvanceStore.dbo.mpi_ProfileResponse AS pr ON cpr.ProfileResponseID = pr.ID
JOIN AdvanceStore.dbo.mpi_ReferralMethod AS rm ON cl.ReferralMethodID = rm.ID
',
@attach_query_result_as_file = 0,
@query_result_width = 256
END
ELSE IF @ProfileType = 2 -- Email Sign Up form
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'vicki.peters@merion.com',
@subject = 'Custom Promotions - Email Sign Up Request',
@query =
'
SELECT
[Date Entered] = pr.CreateDate,
[Profile Type] = ''Email Sign Up'',
[First Name] = cl.FirstName,
[Last Name] = cl.LastName,
[Company Name] = ISNULL(cl.CompanyName,''''),
[Department] = ISNULL(cl.Department,''''),
[Job Title] = ISNULL(cl.JobTitle,''''),
[Street Address 1] = ISNULL(cl.StreetLine1,''''),
[Street Address 2] = ISNULL(cl.StreetLine2,''''),
[City] = ISNULL(cl.City,''''),
[State] = ISNULL(cl.StateName,''''),
[Zip] = ISNULL(cl.PostalCode,''''),
[Phone Number] = ISNULL(cl.PhoneNumber,''''),
[Phone Extension] = ISNULL(cl.PhoneExtension,''''),
[Email] = cl.Email,
[Wants Mailing List] =
CASE
WHEN cl.MailingList = 1 THEN ''Yes''
ELSE ''No''
END,
[Preferred Contact Method] =
CASE
WHEN cl.ContactMethodID IS NULL THEN ''(none)''
WHEN cl.ContactMethodID = 1 THEN ''Phone''
ELSE ''Email''
END,
[Referral Method] = rm.[Name],
[Question] = pq.QuestionText,
[Answer] = pa.AnswerText,
[Other Reason] =
CASE
WHEN pa.RequiresAdditionalText = 0 THEN ''''
ELSE prd.AnswerText
END
FROM AdvanceStore.dbo.mpi_HoldClientID AS t
JOIN AdvanceStore.dbo.mpi_Client AS cl ON t.ID = cl.ID
JOIN AdvanceStore.dbo.mpi_ClientXProfileResponse AS cpr ON cl.ID = cpr.ClientID
JOIN AdvanceStore.dbo.mpi_ProfileResponse AS pr ON cpr.ProfileResponseID = pr.ID
JOIN AdvanceStore.dbo.mpi_ProfileResponseDetail AS prd ON pr.ID = prd.ProfileResponseID
JOIN AdvanceStore.dbo.mpi_ProfileQuestion AS pq ON prd.ProfileQuestionID = pq.ID
JOIN AdvanceStore.dbo.mpi_ProfileAnswer AS pa ON prd.ProfileQuestionID = pa.ProfileQuestionID
AND prd.ProfileAnswerID = pa.ID
JOIN AdvanceStore.dbo.mpi_ReferralMethod AS rm ON cl.ReferralMethodID = rm.ID
',
@attach_query_result_as_file = 0,
@query_result_width = 256
END
ELSE -- @ProfileType = 3 -- Information Packet form
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'vicki.peters@merion.com',
@subject = 'Custom Promotions - Email Sign Up Request',
@query =
'
SELECT
[Date Entered] = pr.CreateDate,
[Profile Type] = ''Information Packet'',
[First Name] = cl.FirstName,
[Last Name] = cl.LastName,
[Company Name] = ISNULL(cl.CompanyName,''''),
[Department] = ISNULL(cl.Department,''''),
[Job Title] = ISNULL(cl.JobTitle,''''),
[Street Address 1] = ISNULL(cl.StreetLine1,''''),
[Street Address 2] = ISNULL(cl.StreetLine2,''''),
[City] = ISNULL(cl.City,''''),
[State] = ISNULL(cl.StateName,''''),
[Zip] = ISNULL(cl.PostalCode,''''),
[Phone Number] = ISNULL(cl.PhoneNumber,''''),
[Phone Extension] = ISNULL(cl.PhoneExtension,''''),
[Email] = cl.Email,
[Wants Mailing List] =
CASE
WHEN cl.MailingList = 1 THEN ''Yes''
ELSE ''No''
END,
[Preferred Contact Method] =
CASE
WHEN cl.ContactMethodID IS NULL THEN ''(none)''
WHEN cl.ContactMethodID = 1 THEN ''Phone''
ELSE ''Email''
END,
[Referral Method] = rm.[Name]
FROM AdvanceStore.dbo.mpi_HoldClientID AS hc
JOIN AdvanceStore.dbo.mpi_Client AS cl ON hc.ID = cl.ID
LEFT JOIN AdvanceStore.dbo.mpi_ClientXProfileResponse AS cpr ON cl.ID = cpr.ClientID
LEFT JOIN AdvanceStore.dbo.mpi_ProfileResponse AS pr ON cpr.ProfileResponseID = pr.ID
JOIN AdvanceStore.dbo.mpi_ReferralMethod AS rm ON cl.ReferralMethodID = rm.ID
',
@attach_query_result_as_file = 0,
@query_result_width = 256
END
END
/************************************************************************************
IF CURRENT DATABASE IS AHS_QA
************************************************************************************/
ELSE IF @DBName = 'AHS_QA'
BEGIN
-- Evaluate @ProfileType. Depending on value returned, send an email with
-- specified data fields and subject line.
IF @ProfileType = 1 -- Contact Us form
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'vicki.peters@merion.com',
@subject = 'Custom Promotions - Email Sign Up Request',
@query =
'
SELECT
[Date Entered] = pr.CreateDate,
[Profile Type] = ''Contact Us'',
[First Name] = cl.FirstName,
[Last Name] = cl.LastName,
[Company Name] = ISNULL(cl.CompanyName,''''),
[Department] = ISNULL(cl.Department,''''),
[Job Title] = ISNULL(cl.JobTitle,''''),
[Phone Number] = ISNULL(cl.PhoneNumber,''''),
[Phone Extension] = ISNULL(cl.PhoneExtension,''''),
[Email] = cl.Email,

[Preferred Contact Method] =
CASE
WHEN cl.ContactMethodID IS NULL THEN ''(none)''
WHEN cl.ContactMethodID = 1 THEN ''Phone''
ELSE ''Email''
END,
[Referral Method] = rm.[Name],

[Wants Mailing List] =
CASE
WHEN cl.MailingList = 1 THEN ''Yes''
ELSE ''No''
END
FROM AHS_QA.dbo.mpi_HoldClientID AS hc
JOIN AHS_QA.dbo.mpi_Client AS cl ON hc.ID = cl.ID
LEFT JOIN AHS_QA.dbo.mpi_ClientXProfileResponse AS cpr ON cl.ID = cpr.ClientID
LEFT JOIN AHS_QA.dbo.mpi_ProfileResponse AS pr ON cpr.ProfileResponseID = pr.ID
JOIN AHS_QA.dbo.mpi_ReferralMethod AS rm ON cl.ReferralMethodID = rm.ID
',
@attach_query_result_as_file = 0,
@query_result_width = 256
END
ELSE IF @ProfileType = 2 -- Email Sign Up form
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'vicki.peters@merion.com',
@subject = 'Custom Promotions - Email Sign Up Request',
@query =
'
SELECT
[Date Entered] = pr.CreateDate,
[Profile Type] = ''Email Sign Up'',
[First Name] = cl.FirstName,
[Last Name] = cl.LastName,
[Company Name] = ISNULL(cl.CompanyName,''''),
[Department] = ISNULL(cl.Department,''''),
[Job Title] = ISNULL(cl.JobTitle,''''),
[Street Address 1] = ISNULL(cl.StreetLine1,''''),
[Street Address 2] = ISNULL(cl.StreetLine2,''''),
[City] = ISNULL(cl.City,''''),
[State] = ISNULL(cl.StateName,''''),
[Zip] = ISNULL(cl.PostalCode,''''),
[Phone Number] = ISNULL(cl.PhoneNumber,''''),
[Phone Extension] = ISNULL(cl.PhoneExtension,''''),
[Email] = cl.Email,
[Wants Mailing List] =
CASE
WHEN cl.MailingList = 1 THEN ''Yes''
ELSE ''No''
END,
[Preferred Contact Method] =
CASE
WHEN cl.ContactMethodID IS NULL THEN ''(none)''
WHEN cl.ContactMethodID = 1 THEN ''Phone''
ELSE ''Email''
END,
[Referral Method] = rm.[Name],
[Question] = pq.QuestionText,
[Answer] = pa.AnswerText,
[Other Reason] =
CASE
WHEN pa.RequiresAdditionalText = 0 THEN ''''
ELSE prd.AnswerText
END
FROM AHS_QA.dbo.mpi_HoldClientID AS t
JOIN AHS_QA.dbo.mpi_Client AS cl ON t.ID = cl.ID
JOIN AHS_QA.dbo.mpi_ClientXProfileResponse AS cpr ON cl.ID = cpr.ClientID
JOIN AHS_QA.dbo.mpi_ProfileResponse AS pr ON cpr.ProfileResponseID = pr.ID
JOIN AHS_QA.dbo.mpi_ProfileResponseDetail AS prd ON pr.ID = prd.ProfileResponseID
JOIN AHS_QA.dbo.mpi_ProfileQuestion AS pq ON prd.ProfileQuestionID = pq.ID
JOIN AHS_QA.dbo.mpi_ProfileAnswer AS pa ON prd.ProfileQuestionID = pa.ProfileQuestionID
AND prd.ProfileAnswerID = pa.ID
JOIN AHS_QA.dbo.mpi_ReferralMethod AS rm ON cl.ReferralMethodID = rm.ID
',
@attach_query_result_as_file = 0,
@query_result_width = 256
END
ELSE -- @ProfileType = 3 -- Information Packet form
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'vicki.peters@merion.com',
@subject = 'Custom Promotions - Email Sign Up Request',
@query =
'
SELECT
[Date Entered] = pr.CreateDate,
[Profile Type] = ''Information Packet'',
[First Name] = cl.FirstName,
[Last Name] = cl.LastName,
[Company Name] = ISNULL(cl.CompanyName,''''),
[Department] = ISNULL(cl.Department,''''),
[Job Title] = ISNULL(cl.JobTitle,''''),
[Street Address 1] = ISNULL(cl.StreetLine1,''''),
[Street Address 2] = ISNULL(cl.StreetLine2,''''),
[City] = ISNULL(cl.City,''''),
[State] = ISNULL(cl.StateName,''''),
[Zip] = ISNULL(cl.PostalCode,''''),
[Phone Number] = ISNULL(cl.PhoneNumber,''''),
[Phone Extension] = ISNULL(cl.PhoneExtension,''''),
[Email] = cl.Email,
[Wants Mailing List] =
CASE
WHEN cl.MailingList = 1 THEN ''Yes''
ELSE ''No''
END,
[Preferred Contact Method] =
CASE
WHEN cl.ContactMethodID IS NULL THEN ''(none)''
WHEN cl.ContactMethodID = 1 THEN ''Phone''
ELSE ''Email''
END,
[Referral Method] = rm.[Name]
FROM AHS_QA.dbo.mpi_HoldClientID AS hc
JOIN AHS_QA.dbo.mpi_Client AS cl ON hc.ID = cl.ID
LEFT JOIN AHS_QA.dbo.mpi_ClientXProfileResponse AS cpr ON cl.ID = cpr.ClientID
LEFT JOIN AHS_QA.dbo.mpi_ProfileResponse AS pr ON cpr.ProfileResponseID = pr.ID
JOIN AHS_QA.dbo.mpi_ReferralMethod AS rm ON cl.ReferralMethodID = rm.ID
',
@attach_query_result_as_file = 0,
@query_result_width = 256
END
END
/************************************************************************************
IF CURRENT DATABASE IS AHS_Dev
************************************************************************************/
ELSE -- @DBName = 'AHS_Dev'
BEGIN
-- Evaluate @ProfileType. Depending on value returned, send an email with
-- specified data fields and subject line.
IF @ProfileType = 1 -- Contact Us form
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'vicki.peters@merion.com',
@subject = 'Custom Promotions - Email Sign Up Request',
@query =
'
SELECT
[Date Entered] = pr.CreateDate,
[Profile Type] = ''Contact Us'',
[First Name] = cl.FirstName,
[Last Name] = cl.LastName,
[Company Name] = ISNULL(cl.CompanyName,''''),
[Department] = ISNULL(cl.Department,''''),
[Job Title] = ISNULL(cl.JobTitle,''''),
[Phone Number] = ISNULL(cl.PhoneNumber,''''),
[Phone Extension] = ISNULL(cl.PhoneExtension,''''),
[Email] = cl.Email,

[Preferred Contact Method] =
CASE
WHEN cl.ContactMethodID IS NULL THEN ''(none)''
WHEN cl.ContactMethodID = 1 THEN ''Phone''
ELSE ''Email''
END,
[Referral Method] = rm.[Name],

[Wants Mailing List] =
CASE
WHEN cl.MailingList = 1 THEN ''Yes''
ELSE ''No''
END
FROM AHS_Dev.dbo.mpi_HoldClientID AS hc
JOIN AHS_Dev.dbo.mpi_Client AS cl ON hc.ID = cl.ID
LEFT JOIN AHS_Dev.dbo.mpi_ClientXProfileResponse AS cpr ON cl.ID = cpr.ClientID
LEFT JOIN AHS_Dev.dbo.mpi_ProfileResponse AS pr ON cpr.ProfileResponseID = pr.ID
JOIN AHS_Dev.dbo.mpi_ReferralMethod AS rm ON cl.ReferralMethodID = rm.ID
',
@attach_query_result_as_file = 0,
@query_result_width = 256
END
ELSE IF @ProfileType = 2 -- Email Sign Up form
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'vicki.peters@merion.com',
@subject = 'Custom Promotions - Email Sign Up Request',
@query =
'
SELECT
[Date Entered] = pr.CreateDate,
[Profile Type] = ''Email Sign Up'',
[First Name] = cl.FirstName,
[Last Name] = cl.LastName,
[Company Name] = ISNULL(cl.CompanyName,''''),
[Department] = ISNULL(cl.Department,''''),
[Job Title] = ISNULL(cl.JobTitle,''''),
[Street Address 1] = ISNULL(cl.StreetLine1,''''),
[Street Address 2] = ISNULL(cl.StreetLine2,''''),
[City] = ISNULL(cl.City,''''),
[State] = ISNULL(cl.StateName,''''),
[Zip] = ISNULL(cl.PostalCode,''''),
[Phone Number] = ISNULL(cl.PhoneNumber,''''),
[Phone Extension] = ISNULL(cl.PhoneExtension,''''),
[Email] = cl.Email,
[Wants Mailing List] =
CASE
WHEN cl.MailingList = 1 THEN ''Yes''
ELSE ''No''
END,
[Preferred Contact Method] =
CASE
WHEN cl.ContactMethodID IS NULL THEN ''(none)''
WHEN cl.ContactMethodID = 1 THEN ''Phone''
ELSE ''Email''
END,
[Referral Method] = rm.[Name],
[Question] = pq.QuestionText,
[Answer] = pa.AnswerText,
[Other Reason] =
CASE
WHEN pa.RequiresAdditionalText = 0 THEN ''''
ELSE prd.AnswerText
END
FROM AHS_Dev.dbo.mpi_HoldClientID AS t
JOIN AHS_Dev.dbo.mpi_Client AS cl ON t.ID = cl.ID
JOIN AHS_Dev.dbo.mpi_ClientXProfileResponse AS cpr ON cl.ID = cpr.ClientID
JOIN AHS_Dev.dbo.mpi_ProfileResponse AS pr ON cpr.ProfileResponseID = pr.ID
JOIN AHS_Dev.dbo.mpi_ProfileResponseDetail AS prd ON pr.ID = prd.ProfileResponseID
JOIN AHS_Dev.dbo.mpi_ProfileQuestion AS pq ON prd.ProfileQuestionID = pq.ID
JOIN AHS_Dev.dbo.mpi_ProfileAnswer AS pa ON prd.ProfileQuestionID = pa.ProfileQuestionID
AND prd.ProfileAnswerID = pa.ID
JOIN AHS_Dev.dbo.mpi_ReferralMethod AS rm ON cl.ReferralMethodID = rm.ID
',
@attach_query_result_as_file = 0,
@query_result_width = 256
END
ELSE -- @ProfileType = 3 -- Information Packet form
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'vicki.peters@merion.com',
@subject = 'Custom Promotions - Email Sign Up Request',
@query =
'
SELECT
[Date Entered] = pr.CreateDate,
[Profile Type] = ''Information Packet'',
[First Name] = cl.FirstName,
[Last Name] = cl.LastName,
[Company Name] = ISNULL(cl.CompanyName,''''),
[Department] = ISNULL(cl.Department,''''),
[Job Title] = ISNULL(cl.JobTitle,''''),
[Street Address 1] = ISNULL(cl.StreetLine1,''''),
[Street Address 2] = ISNULL(cl.StreetLine2,''''),
[City] = ISNULL(cl.City,''''),
[State] = ISNULL(cl.StateName,''''),
[Zip] = ISNULL(cl.PostalCode,''''),
[Phone Number] = ISNULL(cl.PhoneNumber,''''),
[Phone Extension] = ISNULL(cl.PhoneExtension,''''),
[Email] = cl.Email,
[Wants Mailing List] =
CASE
WHEN cl.MailingList = 1 THEN ''Yes''
ELSE ''No''
END,
[Preferred Contact Method] =
CASE
WHEN cl.ContactMethodID IS NULL THEN ''(none)''
WHEN cl.ContactMethodID = 1 THEN ''Phone''
ELSE ''Email''
END,
[Referral Method] = rm.[Name]
FROM AHS_Dev.dbo.mpi_HoldClientID AS hc
JOIN AHS_Dev.dbo.mpi_Client AS cl ON hc.ID = cl.ID
LEFT JOIN AHS_Dev.dbo.mpi_ClientXProfileResponse AS cpr ON cl.ID = cpr.ClientID
LEFT JOIN AHS_Dev.dbo.mpi_ProfileResponse AS pr ON cpr.ProfileResponseID = pr.ID
JOIN AHS_Dev.dbo.mpi_ReferralMethod AS rm ON cl.ReferralMethodID = rm.ID
',
@attach_query_result_as_file = 0,
@query_result_width = 256
END
END


-- End trigger
END
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-01-22 : 14:44:10
I seriously would consider redesigning your system. This trigger has got to be adding a huge performance hit on inserts. To do e-mails, you typically have the trigger write to a table that says an e-mail needs to be sent out. Then you've got a job that queries this table say every 1-5 minutes. The job handles the e-mail.

But anyway, have you tried replacing the TRUNCATE with a DELETE? I haven't heard of any issues with TRUNCATE in triggers, but you never know.

Tara Kizer
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-01-23 : 01:59:52
"If sending an email via a trigger is not a good idea (why isn't it?), then what would be a good alternate?"

Sending an email takes a "long time" (in SQL transaction time!) and relies on external processes - which may fail etc. These will cause the trigger to be slow, hold locks on tables etc. for longer than necessary, and is likely to cause hard-to-find-bugs.

As Tara has said better to just insert the data for the Email into a table (with a SentOnDate column where, say, NULL = "Not yet sent") and then have a scheduled routine that sends the Emails periodically. (This also has the benefit of providing an Audit of what emails have been sent, and the opportunity to aggregate Emails to the same person - e.g. to prevent a flood of emails if exceptionally high activity occurs, for some reason)

If you must send the Email immediately then better to use a Stored Procedure rather than a Trigger - there the code is isolated to a single process.

I also wonder about the concurrency of your trigger if you have code for the TRUNCATE of another (permanent) table - what happens if two people make changes to the underlying table concurrently and the second TRUNCATE deletes the data that the first Trigger instance has just inserted, but not yet used? (I haven't read the code to see if you have made provision of that)

Kristen
Go to Top of Page

VPeters
Starting Member

20 Posts
Posted - 2007-01-23 : 08:52:59
Thanks, Kristen, for the in-depth explanation. I appreciate it. While I've been coding in SQL for a number of years now, having the permissions to create triggers is new to me. In a previous life, our DBA kept a tight lock on that! Anyhow, this sounds like a good plan and I will try creating the job. Regarding the table...I actually created that table just for use in this trigger. It should never be truncated or written to unless I'm the one doing it in my trigger (or now, in my stored procedure). Thanks again!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-01-24 : 08:33:24
It would be safer, IMHO, to build the trigger on the basis that it might be called concurrently

Kristen
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site