|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
An Insert Trigger with CDOSYS generated emailI've got an insert trigger defined on a table. Everything seems to work perfectly excpet the body section of my e-mail message is delivered empty. Here the relavent code segment... Begin DECLARE @CaseCounter varchar(50) If (SELECT Count(*) FROM inserted WHERE PI_ID = '0000') >0 SELECT @CaseCounter = RTRIM(CAST(IDENT_CURRENT('inserted') AS varchar(50))) SELECT @body = 'The Case Number is: ' + @CaseCounter Set @vet_email = 'valid@to.email.address' exec dbo.sp_send_cdosysmail 'valid@from.email.address, @vet_email, 'An Unlisted PI was submitted with this case', @body End As you can tell from the my trigger code I'm trying to concatenate the inserted Identity value onto a Character string. As I stated above, the e-mail arrive fine but the Body section is blank. In case you're wondering, the @vet_mail variable is DECLARED earlier in the trigger. I previously had all variables DECLARE at the top of the code but I still got the same results. All suggestions are welcomed! Thanks -- Application Engineer / DBA UCLA SOM (a) please, please, please... do NOT send e-mail from a trigger!
(b) why don't you look at the base table instead of inserted. inserted is a virtual table and I think you will find that IDENT_CURRENT() will return NULL (which, when concatenated to your @body value, makes the whole parameter NULL). To prove it, try: SET @body = 'The Case Number is: ' + COALESCE(@CaseCounter, 'NULL'); Show quote "Marcial" <no_spam@antispammer.com> wrote in message news:1B796F05-D8E3-4F0A-AA9F-A1859F94FE58@microsoft.com... > Hello, > > I've got an insert trigger defined on a table. > Everything seems to work perfectly excpet the body section of my e-mail > message is delivered empty. > > Here the relavent code segment... > > Begin > DECLARE @CaseCounter varchar(50) > If (SELECT Count(*) FROM inserted WHERE PI_ID = '0000') >0 > SELECT @CaseCounter = RTRIM(CAST(IDENT_CURRENT('inserted') AS > varchar(50))) > SELECT @body = 'The Case Number is: ' + @CaseCounter > Set @vet_email = 'valid@to.email.address' > exec dbo.sp_send_cdosysmail 'valid@from.email.address, @vet_email, 'An > Unlisted PI was submitted with this case', @body > End > > > As you can tell from the my trigger code I'm trying to concatenate the > inserted Identity value onto a Character string. As I stated above, the > e-mail arrive fine but the Body section is blank. In case you're > wondering, > the @vet_mail variable is DECLARED earlier in the trigger. > > I previously had all variables DECLARE at the top of the code but I still > got the same results. > > All suggestions are welcomed! > > Thanks > > -- > Application Engineer / DBA > UCLA SOM Hi Marcial,
just to add to Aarons post: Its REALLY² NOT³ recommended to send Emails in a trigger.... WHY ==== 1. Triggers behave synchronously, that means the trigger will block the current transaction till the whole code in it was executed. So in any cases that your mail server is unreachable, taking long for communcation etc. the transaction will be blocked the data / pages / tables (depending on your locking level) will be blocked and your application or frontend or whatever another transaction wants to manipulate the data will be on hold. 2.Triggers can cause the transcation to rollback due to a non-business error. Although when the data and the transaction which is executed is valid and should be commited to the database, if the sending EMail procedure will bring back an error and you don´t handle it, or it is of a certain severity which causes the transaction to rollback, your whole BUSINESS is on hold, only because of sending an email !!! You don´t want that, erh ? I would suggest (as this is not time critical) to write the data in a table which is regulary checked for content to be sent. HTH, Jens Suessmeyer --- http://www.sqlserver2005.de --- Thanks Very Much Jens for the expanded explanation. And thanks to Aarron for
the initial Alert anbd Reply. ....You may consider me anevangelized user who has been convinced to find another way beside triggers to send e-mail. Toward that end might anyone have an example of Stored Proc code that searches a table and sends e-mail accordingly. Cheers~ -- Show quoteApplication Engineer / DBA UCLA SOM "Jens" wrote: > Hi Marcial, > > just to add to Aarons post: > > Its REALLY² NOT³ recommended to send Emails in a trigger.... > > WHY > ==== > > 1. Triggers behave synchronously, that means the trigger will block the > current transaction till the whole code in it was executed. So in any > cases that your mail server is unreachable, taking long for > communcation etc. the transaction will be blocked the data / pages / > tables (depending on your locking level) will be blocked and your > application or frontend or whatever another transaction wants to > manipulate the data will be on hold. > > 2.Triggers can cause the transcation to rollback due to a non-business > error. Although when the data and the transaction which is executed is > valid and should be commited to the database, if the sending EMail > procedure will bring back an error and you don´t handle it, or it is > of a certain severity which causes the transaction to rollback, your > whole BUSINESS is on hold, only because of sending an email !!! > > You don´t want that, erh ? > I would suggest (as this is not time critical) to write the data in a > table which is regulary checked for content to be sent. > > > HTH, Jens Suessmeyer > > --- > http://www.sqlserver2005.de > --- > > Hi Marcial,
I thinkk I will write one for you that take as a template. If I don´t come back to the thread please send me a reminder that I will keep track of that. HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de ---  |
|||||||||||||||||||||||
Other interesting topics