An Insert Trigger with CDOSYS generated email

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(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');
"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=B2 NOT=B3 recommended to send Emails in a trigger...
WHY
=3D=3D=3D=3D
1=2E 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=2ETriggers 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=B4t 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=B4t 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 fo
r
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~
--
Application Engineer / DBA
UCLA SOM
"Jens" wrote:

> Hi Marcial,
> just to add to Aarons post:
> Its REALLY2 NOT3 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=B4t
come back to the thread please send me a reminder that I will keep
track of that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

Among Numeric and Float

among numeric and float data types, which one is more precise for numeric calculations?
can any body help
thanx in advance...probably Float, because you can set the scale and precision.
e.g. Float(10,5) will show 1234.00023 (five decimal places or as many as you need)