Sql-server – SQL Server sp_send_dbmail Query Error

sql-server-2005trigger

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'

Best Answer

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.

Related Question