Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
EXEC msdb.dbo.sp_send_dbmail 
@recipients= 'test@test.com',
@copy_recipients = 'test@test.com', 
@query = 'SELECT * FROM INSERTED',
@attach_query_result_as_file = 1,
@subject = 'Auto-Generated Deleted Item in Order Item Detail', 
@body = 'Item has been deleted from Order Item Detail', 
@body_format = 'HTML',
@importance = 'High'

When the above code executes I get the following error:

Msg 22050, Level 16, State 1, Line 0 Error formatting query, probably invalid parameters Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495 Query execution failed: Msg 208, Level 16, State 1, Server BK00SQL0002, Line 1 Invalid object name 'INSERTED'.

The below works: (How do I modify for INSERTED?)

    EXEC msdb.dbo.sp_send_dbmail 
@recipients= 'test@test.com',
@copy_recipients = 'test@test.com',
@query = 'SELECT * FROM [PreMfg].[dbo].[Order Item Detail Audit]', 
@subject = 'An Item Has Been Deleted From the Order Item Detail Table',
@attach_query_result_as_file = 1, 
@body = 'An Item Has Been Deleted From the Order Item Detail Table', 
@body_format = 'HTML',
@importance = 'High'
share|improve this question

migrated from stackoverflow.com Feb 12 '12 at 7:52

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 2 down vote accepted

You can't reference the inserted pseudo-table from outside the trigger. And do you really want to send an e-mail for every insert/update? And make the transaction that invoked the trigger wait for all that to happen (even though database mail is asynchronous, it isn't free)?

I suggest you log the data to a queue table, and have a job that every minute (or five minutes, or some reasonable frequency) wakes up and sends mail for any new data it finds in the queue table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.