SQL Trigger To Automatically Enable Email Documents In Purchasing – All

This post includes a script which amalgamates the previous two posted triggers to enable email documents (purchase orders and check remittances) into one.

The below script creates a trigger on the SY04905 table to automatically flag the remittance and purchase orders to be emailed out in PDF format and using Message IDs of REMITTANCE and PURCHASEORDER respectively.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE TRIGGER utr_AZRCRV_Update_SY04905_Activate_All ON dbo.SY04905 AFTER Insert AS
	-- Enable Purchase Order
	UPDATE
		Email
	SET
		EmailDocumentEnabled = 1
		,EmailMessageID = 'PURCHASEORDER'
		,EmailDocumentFormat = 3
	FROM
		SY04905 AS Email
	INNER JOIN
		inserted
			ON Email.EmailDictionaryID = 0
				AND Email.EmailSeriesID = 4
				AND Email.MODULE1 = 12
				AND Email.EmailCardID = inserted.EmailCardID
				AND Email.EmailDocumentID = 1
	-- Enable Remittance
	UPDATE
		Email
	SET
		EmailDocumentEnabled = 1
		,EmailMessageID = 'REMITTANCE'
		,EmailDocumentFormat = 3
	FROM
		SY04905 AS Email
	INNER JOIN
		inserted
			ON Email.EmailDictionaryID = 0
				AND Email.EmailSeriesID = 4
				AND Email.MODULE1 = 19
				AND Email.EmailCardID = inserted.EmailCardID
				AND Email.EmailDocumentID = 6
GO