- SQL Server 2016 SP2 on Windows Server 2012.
- File table (Feedback Forms) is currently in use, files are saved daily by multiple users.
- Database Mail is configured and was tested successfully.
Scenario:
- A training course takes place.
- The trainer completes an evaluation form for each student who attended the course.
- Trainer emails the evaluation forms to training operations mailbox.
- Training Admin forwards the message to another admin (Admin2).
- Admin2 saves each evaluation form to a mapped network folder (the file table) using a specific naming convention. (Filename is determined by a query made accessible to admin via Excel.
- Admin2 manually emails each evaluation form to the student's manager and copies the local training operations manager.
I would like to automate step 6, and I believe that I can achieve this via a trigger to execute sp_send_dbmail.
I have a view which lists the filename, path_locator and the To and CC email addresses for each file in the database.
I just need some assistance figuring out how to write the trigger to execute sp_send_dbmail with the specifics from the view and attach the PDF file.
