1
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'
2

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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