email on insert

[Guju]

Hi all,

I wanted sql server to shoot an email upon insert into a table. I treated a
trigger on that table as below.

CREATE TRIGGER [emailoninsert] ON [dbo].[table_name]
FOR INSERT
AS
exec sp_sendSMTPmail 'u***@user.com', 'New records are inserted in
table_name table', 'Please investigate and take necessary actions.',
@cc='', @BCC = '',
@Importance=1,
@Attachments='', @HTMLFormat = 0,@From =
'n***********@sqlserver.com'

Is this solution a good method?

Thanks,

Guju

[Greg D. Moore \(Strider\)]

"Guju" <pa**********@yahoo.com.au> wrote in message
news:42********@news.iprimus.com.au...

Hi all,

I wanted sql server to shoot an email upon insert into a table. I treated a trigger on that table as below.

CREATE TRIGGER [emailoninsert] ON [dbo].[table_name]
FOR INSERT
AS
exec sp_sendSMTPmail 'u***@user.com', 'New records are inserted in
table_name table', 'Please investigate and take necessary actions.',
@cc='', @BCC = '',
@Importance=1,
@Attachments='', @HTMLFormat = 0,@From =
'n***********@sqlserver.com'

Is this solution a good method?
No.

It will greatly slow down insert speeds.


Thanks,

Guju

[Tzvika Barenholz]

Guju wrote:

Hi all,

I wanted sql server to shoot an email upon insert into a table. I treated a trigger on that table as below.

CREATE TRIGGER [emailoninsert] ON [dbo].[table_name]
FOR INSERT
AS
exec sp_sendSMTPmail 'u***@user.com', 'New records are inserted in
table_name table', 'Please investigate and take necessary actions.',
@cc='', @BCC = '',
@Importance=1,
@Attachments='', @HTMLFormat = 0,@From =
'n***********@sqlserver.com'

Is this solution a good method?

Thanks,

Guju

It may be a better idea to create a script that checks for new records
in the table every x number of hours (run it as an sql agent job).
the script may save the last record id that it already saw in a table
for this purpose.
As mentioned above, the solution you implemented means the email is
sent at the expense of the insert statement , making it horribly slow.
My way you can also send one mail if 10 records were inserted in stead
of 10, with the data of all 10, which you may trust me is more useful
to the sorry person actually receiving these mails.

hope this helps.

Tzvika

[williamsnis]

Is it possible for you to post a sample script..I am attempting to get
the same result as the author.

[williamsnis]

Is it possible for you to post a sample script..I am attempting to get
the same result as the author.