December 7, 2017 at 11:15 am
I know just enough about SQL to be dangerous, so when I was asked to create a trigger that would email a user when line items from a PO were added I said that should be easy. Well, it turned out to be a week later and no luck.
I have managed to get the emails to send one at a time but if you have a PO with 30 items the user gets 30 emails. I have tried to make the trigger send an XLS doc with the items in it but with no luck. Here is the code I have so far
Alter TRIGGER [dbo].[NewPoForIT]
ON [dbo].[POI] AFTER Insert
AS
SET NOCOUNT ON
DECLARE @to NVARCHAR(MAX) = N'test@test.com';
DECLARE @cc NVARCHAR(MAX) = N'';
DECLARE @subj NVARCHAR(255) = N'New PO Created';
Declare @body VARCHAR(max);
DECLARE @file NVARCHAR(255) = N'New IT PO Lines.xls';
DECLARE @tab CHAR(1) = CHAR(9);
---- check if PO contains IT account number
IF EXISTS (
SELECT 1 FROM INSERTED
JOIN COA ON POI_AcctNbr = COA_AcctNbr
WHERE COA_UserDef1 = 'IT'
)
BEGIN
---- if yes, build message for e-mail
EXEC msdb.dbo.sp_send_dbmail
@recipients=@to
, @copy_recipients=@cc
, @subject=@subj
, @query=N'SELECT POI_PurchOrderID, POM_VendorName, POI_LineItemValAmt, POI_AcctNbr, POI_ItemName
FROM INSERTED
JOIN POM WITH(NOLOCK) ON POM_RecordID = POI_POM_RecordID
JOIN COA WITH(NOLOCK) ON POI_AcctNbr = COA_AcctNbr
WHERE COA_UserDef1 = ''IT'';'
, @execute_query_database=TEST87_SOLON /* set proper DB here. */
, @attach_query_result_as_file=1
, @query_attachment_filename=@file
, @query_result_separator=@tab
, @query_result_no_padding=1
;
END
GO
I get a message Invalid object name 'INSERTED' when the trigger runs.
Any help with this would be much appreciated.
December 7, 2017 at 2:50 pm
I could be wrong on this as I have not built a trigger like that before, but I fairly confident that the INSERTED table is only visible to your trigger, not outside the trigger.
So when you try to execute the sp_send_dbmail command, the query you are passing doesn't have access to INSERTED and that is what is causing you issues.
So what I think you would want to do if sending the query results is a requirement is to change that @query so it is pulling data from a permanent table and not from INSERTED.
What you'd need to do is store POI_POM_RecordID and POI_AcctNbr from INSERTED into variables and then change your FROM, JOIN and WHERE to be something more like:FROM POM
JOIN COA ON COA.COA_AcctNbr = @POI_AcctNbr
WHERE COA.COA_UserDef1 = 'IT'
AND POM_RecordID = @POI_POM_RecordID
Does that make sense?
You will still be sending off 1 email per insert though.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 7, 2017 at 3:32 pm
This sounds like something you'd want to schedule with a job instead of using a trigger... well, at least to my mostly ignorant way of thinking. Then you could find all Invoices/parent records with updated/new line items (if they're time-stamped), and do e-mail per invoice. Per line item sounds pretty painful.
I'm sure someone smarter than I will poke holes in my idea, but one e-mail per line item sounds a bit excessive.
December 7, 2017 at 4:08 pm
pietlinden - Thursday, December 7, 2017 3:32 PMThis sounds like something you'd want to schedule with a job instead of using a trigger... well, at least to my mostly ignorant way of thinking. Then you could find all Invoices/parent records with updated/new line items (if they're time-stamped), and do e-mail per invoice. Per line item sounds pretty painful.I'm sure someone smarter than I will poke holes in my idea, but one e-mail per line item sounds a bit excessive.
That was what I was thinking as well. Main reason being that I don't like sending emails in triggers.
But it resolves the issue about all the emails going out. Use the trigger to dump the data to a table, have a processed flag or something like that for the table, have a job poll and send the email, then mark the rows as processed (or delete them but the history would likely be important for at least some time).
Sue
December 10, 2017 at 5:31 pm
Sue_H - Thursday, December 7, 2017 4:08 PMpietlinden - Thursday, December 7, 2017 3:32 PMThis sounds like something you'd want to schedule with a job instead of using a trigger... well, at least to my mostly ignorant way of thinking. Then you could find all Invoices/parent records with updated/new line items (if they're time-stamped), and do e-mail per invoice. Per line item sounds pretty painful.I'm sure someone smarter than I will poke holes in my idea, but one e-mail per line item sounds a bit excessive.
That was what I was thinking as well. Main reason being that I don't like sending emails in triggers.
But it resolves the issue about all the emails going out. Use the trigger to dump the data to a table, have a processed flag or something like that for the table, have a job poll and send the email, then mark the rows as processed (or delete them but the history would likely be important for at least some time).Sue
That's the way I do such things mostly because it allows me to shut down the email process without having to change the code in the trigger. It also allows for other exquisite control and validation without have much affect on the instigating process at all.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply