October 13, 2008 at 11:16 am
Currently I have a trigger created to replace a students password when certain conditions are met. This part works fine. Now I need to send them an email letting them know what their new password is. This is where I am having problems. Can someone tell me what I am doing wrong? Here is the code.
IF EXISTS (SELECT 1
FROM sysobjects
WHERE name = 'tr_TW_WEB_SECURITY '
AND type = 'TR')
DROP TRIGGER tr_TW_WEB_SECURITY
GO
CREATE TRIGGER tr_TW_WEB_SECURITY
ON candidacy
FOR UPDATE, INSERT
AS
declare @erroroccurred int
-- UPDATE Koala Connection Information
begin
UPDATE TW_WEB_SECURITY
set ACCESS_CDE = substring(convert(char(9), replicate('0',9-datalength(ltrim(str(biograph_master.ssn,9)))) + ltrim(str(biograph_master.ssn,9))),6,4),
USER_NAME = 'TE_ADMIN',
JOB_NAME = 'TRIGGER',
JOB_TIME = GETDATE()
FROM inserted join biograph_master on biograph_master.id_num = inserted.id_num
JOIN TW_WEB_SECURITY ON inserted.ID_NUM = TW_WEB_SECURITY.ID_NUM
WHERE inserted.stage in('DMS','GMS','EMS')
and tw_web_security.access_cde <> substring(convert(char(9), replicate('0',9-datalength(ltrim(str(biograph_master.ssn,9)))) + ltrim(str(biograph_master.ssn,9))),6,4)
and inserted.id_num = tw_web_security.id_num
AND inserted.id_num in (select id_num from tw_web_security)
if @erroroccurred = 0
declare @stud_id int
declare @email varchar(60)
declare @em_text varchar(200)
begin
select @Stud_id = inserted.id_num,
@email = am.addr_line_1,
@em_text = 'You login to Koala Connection has changed. It is now ' +
CAST(inserted.id_num as varchar(8)) + ' and your password has been set to the last xxxxxx'
from inserted join address_master am ON am.id_num = inserted.id_num AND am.addr_cde = 'PEML'
where inserted.stage in ('DMS', 'GMS', 'EMS')
EXEC msdb.dbo.sp_send_dbmail
@recipients=N’nwinningham@columbiasc.edu,@body= @em_text ,
@subject ='Koala Connection change',@profile_name ='database mail'
end
GO
October 13, 2008 at 12:02 pm
A couple things. First, you don't want to do this in a trigger. You're asking for issues. Instead use the trigger to drop a row in a table (email, new pwd, status unsent) and then have a job read that table every minute, call xp_sendmail and then mark the row as sent.
Second you're assuming one row updated, could have issues there.
Last, check this:
UPDATE TW_WEB_SECURITY
set ACCESS_CDE = substring(convert(char(9), replicate('0',9-datalength(ltrim(str(biograph_master.ssn,9)))) + ltrim(str(biograph_master.ssn,9))),6,4),
USER_NAME = 'TE_ADMIN',
JOB_NAME = 'TRIGGER',
JOB_TIME = GETDATE()
FROM inserted join biograph_master on biograph_master.id_num = inserted.id_num
JOIN TW_WEB_SECURITY ON inserted.ID_NUM = TW_WEB_SECURITY.ID_NUM
WHERE inserted.stage in('DMS','GMS','EMS')
and tw_web_security.access_cde substring(convert(char(9), replicate('0',9-datalength(ltrim(str(biograph_master.ssn,9)))) + ltrim(str(biograph_master.ssn,9))),6,4)
and inserted.id_num = tw_web_security.id_num
AND inserted.id_num in (select id_num from tw_web_security)
and be sure it returns the data you want. I'd insert the data from "inserted" into a table, then query the table from your SSMS and substitute that table for "inserted" and check the data.
October 13, 2008 at 12:28 pm
Thanks, I will try that.
October 13, 2008 at 4:33 pm
Steve:
You're right that SQL Mail (xp_send_mail) is a No-No from a trigger, however Database Mail (db_send_mail) is safe from a trigger because it is built around SQL Service Broker. In effect, it already does exactly what you advise, and does it asynchronously to boot!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 10:11 am
rbarryyoung (10/13/2008)
Steve:You're right that SQL Mail (xp_send_mail) is a No-No from a trigger, however Database Mail (db_send_mail) is safe from a trigger because it is built around SQL Service Broker. In effect, it already does exactly what you advise, and does it asynchronously to boot!
Barry,
Sure sp_send_dbmail is safe, but how would you handle a multi-row insert or update? Say the admin changes all passwords that are less than 8 characters to a 8 characters because the policy has been changed? The current trigger would not handle this. In this case you'd need either a cursor or table to queue up the changed rows for an other process to process.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2008 at 10:37 am
Jack Corbett (10/14/2008)
rbarryyoung (10/13/2008)
Steve:You're right that SQL Mail (xp_send_mail) is a No-No from a trigger, however Database Mail (db_send_mail) is safe from a trigger because it is built around SQL Service Broker. In effect, it already does exactly what you advise, and does it asynchronously to boot!
Barry,
Sure sp_send_dbmail is safe, but how would you handle a multi-row insert or update? Say the admin changes all passwords that are less than 8 characters to a 8 characters because the policy has been changed? The current trigger would not handle this. In this case you'd need either a cursor or table to queue up the changed rows for an other process to process.
Actually, the current trigger does appear to handle this, because it only has one possible recipient and it just bundles all of the changes up and sends it in one Email message.
If you're asking how you would normally handle multi-row modifications from a trigger when using SEND to a Service Broker queue, then the answer is XML. You just batch it all up in one big XML packet and then the receiver procedure can take the time it needs to sort it all out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 10:40 am
Perhaps I should clarify though: my original reply was only meant to comment on sending mail from a Trigger, not anything else that Steve was recommending.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply