Help in Trigger Mail - SQL Server Forums

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

SQL Server Administration (2005)

Help in Trigger Mail

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-28 : 02:06:26

Hi All,

I have created one Trigger to notify the user against any insertion in table inventory.

CREATE TRIGGER expensiveInventoryMailer ON dbo.inventory AFTER INSERT AS

DECLARE @price money
DECLARE @item varchar(50)

SET @price = (SELECT price FROM inserted)
SET @item = (SELECT item FROM inserted)

IF @price >= 0
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'phirani@vcmpartners.com', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'Prashant Hirani'
END
GO

insert into inventory (item,price) values ('KeyBoard',999)

Mail Output format is
---------------------------------------------------------------------
Expensive item "KeyBoard" entered into inventory at $999.00.

Now i need output something like this
---------------------------------------------------------------------
Hi,

Expensive item "KeyBoard" entered into inventory at $999.00.

Thanks,
VCM Partners LLC

Can any one Help me out?

Thanks
Prashant

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 02:48:05

May be this:-

CREATE TRIGGER expensiveInventoryMailer ON dbo.inventory AFTER INSERT AS

DECLARE @price money
DECLARE @item varchar(50)

SET @price = (SELECT price FROM inserted)
SET @item = (SELECT item FROM inserted)

IF @price >= 0
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Hi,'+ CHAR(13)+'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'phirani@vcmpartners.com', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'Prashant Hirani'
END
GO

suresha_b
Yak Posting Veteran

82 Posts

Posted - 2008-05-29 : 01:29:13

SET @msg = 'Hi, 
Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '. 
Thanks, 
VCM Partners LLC'

EXEC msdb.dbo.sp_send_dbmail @recipients=N'phirani@vcmpartners.com', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'Prashant Hirani', @body_format = 'HTML'

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-05-29 : 09:31:36

Do NOT use a trigger to send e-mails. This is a VERY BAD practice.
The scope of a trigger should be limited to its table if possible, but never beyond its database.
Instead, use the trigger to populate a staging table of messages, and then have a scheduled process poll the table at regular intervals and send out the e-mails notifications.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources