Configuring Triggers

A Trigger is a stored procedure that runs automatically when a row is inserted, updated, or deleted in a table. In Deacom, triggers serve two main purposes:

  1. Sending notifications, via email, when changes to a system table are made.
  2. Creation of EDI output files used for EDI exports such as 810 Invoices.

Triggers are useful for notifying users when changes or updates to important tables have been made in the system, such as when a formula is changed, an order is shipped, or a customer has been placed on credit hold. Triggers can also be used for other purposes, such as sending text messages from Deacom by utilizing phone carriers' email-to-text services or for emailing Deacom reports and print outs. Additional information on sending text messages is available in the "Sending text messages from Deacom" section of this page.

Notes

  1. The security setting, "System -- triggers" controls which users have access to Triggers. 
  2. If users do not require access to the System > Maintenance area, a Favorite can be created for the Triggers option, and the security setting "System Menu" can be set to "No."

Configuration

Three different configuration steps are required to run Triggers.

  • System Options:
    • Required - Trigger Email From, Trigger Username, Trigger Password, and Trigger Domain. In versions of Deacom 16.01 and above, this information is populated within the Company tab. Earlier version of Deacom will utilize the Email tab for all Trigger & email data.
    • Optional - Use Default Network Credentials For Trigger Email
    • Optional - Trigger Auth Type, Trigger App ID, Trigger Tenant ID, and Trigger Secret. These fields can be used to add an additional layer of authentication (SSO authentication) when sending Triggers. See the Configuring Azure SSO with Triggers section further down on this page for more information.
  • Automation Console or Service - The Deacom automation console or service must be running on the system/server whose respective IP address is specified on the “SVCADDR” line in the Deacom INI file. The automation console or service can then be set to run on the server. Users do not need to install or run the automation console or service on their individual PCs. The difference between the service and the console is the console must be run manually anytime the server hosting the main Deacom application is restarted, unlike the service, which can be set to run automatically any time the server is restarted. We recommend using the automation service in your production environment. See "Deacom Automation Service and Console" page for additional information on setup. While multiple automation services or consoles may be in use for WMS Connections, only the one running on the IP configured in SVCADDR will be utilized for trigger communication. 
  • Deacom INI File - The INI file is a plain text file that contains Deacom configuration details including the location of the system database, server IP address, and the network path for the different folders used by the Deacom application. The INI file is created during the initial installation process and is contained within the main Deacom application folder. The Deacom INI file must contain the IP address of the system running the automation console or service. If necessary, users can use the command window to check the ipconfig setup.  Additional information on the Deacom INI file, together with samples is available via the "Deacom INI File" page. The IP Address configured here, will be copied into the actual SQL Database trigger which is created in your Deacom SQL Database. Changing of the IP Address in the Deacom.INI file will require the automation service be restarted to change the trigger addresses within SQL Server.

Process

Setting up Triggers

  1. Navigate to System > Maintenance > Triggers.
  2. Click "New" to open the Edit Trigger form.
  3. Fill in all necessary fields.
  4. Ensure that the "Active" flag is checked so that the Trigger will fire successfully.
  5. If the trigger is meant to be used, un-check the "Template" flag. Template triggers will not fire, but are to be used as a template to create other triggers via the ‘Copy Trigger’ function.
  6. Once all the information has been entered, click the "Save" and "Exit" buttons to complete the process.

Running Triggers

There is no set criteria for running a trigger, and the action of the trigger itself is automated through the Automation Console / Service whenever your triggers conditions are met. For example, if you have a trigger setup for the Trigger Event of Insert, on the Item Master table with no conditions listed, the trigger will send any time a new item is created.

Trigger verification

To review trigger activity in your database, Deacom provides a trigger history log which can be useful in troubleshooting if triggers are firing correctly or if there are issues with sending emails when triggers are fired. In versions of Deacom 15.03.033 and above, this is located in System > View History / Performance > Type – “Trigger”, in prior editions, the option will show in System > View Trigger History. Since EDI Exports are handled via the use of triggers, the trigger history log can be used to ensure that EDI Exports were performed correctly. In addition, the trigger history log may be saved as a preview and displayed on the main Deacom window so that sales order or invoice acknowledgement failures may be viewed and researched in a timely manner.

Reprocessing Triggers

Beginning in version 17.02.011, users can re-send or re-process individual triggers, weather the trigger was successful fired or not, via the "Reprocess" button on the Trigger History report via System > History/Performance. This option is useful when users wish to reprocess successful triggers or when a failure occurs, such as when the Deacom automation service goes down (such as during a power outage) and triggers need to be re-sent without un-doing/re-doing the underlying Deacom transaction.

Performing EDI Exports using Triggers

EDI Exports in Deacom are handled via the use of Triggers. While most all trigger header options will remain universal to the trigger setup – the output type of EDI allows users to not only place a file to their local network when a trigger fires, though also has the ability to upload this information to an FTP, in which the settings for FTP Server, SFTP Port, FTP User Name, Password and File Encoding control. When the FTP Server field is not populated with valid FTP data, the trigger will simply place the exported document to the Destination and will not attempt to upload it to any FTP host.

While EDI triggers are commonly used to upload EDI Invoices to FTP hosts, they’re not limited to this and can be used to easily add notification files to local directories on your network. Entering a Destination of only a file name will add the file to the root of your Deacom directory.

Note that beginning in version 16.03, users may choose to have additional Deacom reports or print outs placed in the destination location by using the fields on the Attachments tab of the "Edit Trigger" form.

Sending text messages from Deacom

Along with their default uses, Triggers can also be used to send text messages from Deacom by utilizing phone carriers' email-to-text services and setting up the trigger with a delivery type of Plain Text Email. This may be a desired function for internal alerts to Deacom users who are regularly out of the office, use mainly the Deacom Mobile application, or otherwise rely on text messages more so than email. To configure and use a Trigger for texting, the user will need to know the intended recipient's phone number and phone carrier. Use the process steps listed above in the "Setting up Triggers" section and in the "Destination" field, enter one of the following, based on carrier (simply replace "number" with the ten digit phone number, no punctuation):

Note: This feature only works for short, text-only messages, so no attachments or signatures can be included. If the phone carrier being used is not listed above, please contact Tech Support for assistance.

Trigger Examples

Multiple Trigger examples are available via the Trigger Examples page.

Configuring Azure SSO with Triggers

Users must first configure MS Azure (see the Setting Up Azure on the Configuring and sending email section) before completing the section below. Note that the Tenant ID and APP Id refereed to on the aforementioned page must be copied into the Deacom "Trigger App ID" and "Trigger Tenant ID" fields via System > Options > Email tab and not in the "Outlook App ID" and "Outlook Tenant ID" fields as mentioned on that page.

Setting up Azure for secrets

Customers will most likely need to coordinate with their IT department for this step. The screens below are taken from an in house system used for demo purposes.

When using Trigger Secrets users will not be required to enter email credentials when sending emails from Deacom.

  1. In the Azure admin center, on the left hand menu (under the "Manage" folder) click the "Certificates & secrets" option.
  2. Select the "+New client secret" option.
  3. Enter a description for the secret and expiration time frame in the Expires field. (Not that IT administrators will need to be aware of this time frame so they may add/update the secrets and expiration details)
  4. Click Add. Do not leave or refresh this page and continue with the steps below. The secret value will only be visible once.
  5. Select the information in the "Value" column displayed and copy that to the clipboard.
  6. Paste the value into the Trigger Secret field in the Deacom systemvia System > Options > Email tab. Be sure to save your changes in Deacom.
  7. Back in Azure, click on "API Permissions" on the left hand menu.
  8. Click "Add a permission".
  9. On the Request API Permissions form, click "APIs my organization uses".
  10. In the filter box at the top type Office 365 Exchange Online
  11. Click Office 365 Exchange Online
  12. Click the Application permissions option.
  13. You will need to select the four permissions below. You may do this by expanding the corresponding option or by typing the API permission in the Select permissions search bar at the top.
    1. EWS.AccessAsUser.All
    2. Mail.Send
    3. Mail.Read
    4. MailboxSettings.Read
  14. Once all the permissions have been selected click the "Add permissions" button at the bottom.
  15. After these changes have been made in Azure, you will need to refresh the browser where the Deacom system is running to ensure the changes have been applied.

FAQs and Diagnostic Tips

After setting up a Trigger, I receive the following: 'Error while executing SQL command' containing a ROWLOCK and text similar to 'An error occurred processing a SQL command: Cannot find either column "Deacomproduction" or the user-defined function or aggregate "Deacomproduction.dbo.DeacomTriggerExecution_Deacomproduction", or the name is ambiguous.'

Often, this is an indication that the component running the trigger was not properly updated during the last update. Ensure that all Automation services/consoles are off, all users are logged out and re-run the last build update.

When sending trigger via email, I am seeing the error ‘The server response was: 5.7.3 STARTTLS is required to send mail’

This message often indicates an error with your SMTP host handling the email sent from Deacom. You can often resolve this issue by avoiding the ‘Use Default Network Credentials’ setting within the Trigger configuration in System > Options. However, often you will need to contact your SMTP Host to determine the root cause of the email being stopped by the server.

What setup is required to prevent Triggers from re-firing multiple times on the same order during modifications?

In the Conditions field on the Trigger set up, most fields can be prefaced with 'old_' to prevent a re-fire. For example, on a Sales Order Header Update trigger firing on an order that has been shipped, the following string be used to prevent a re-fire during invoicing:

old_to_shipped IS NULL AND to_shipped IS NOT NULL

Note the 'old_' function will not work for Insert triggers as there is no previous record to reference. In the case of old_to_shipped, the field value will default to Null after un-shipping an order.

How can I add tax amounts to 810 invoicing EDIs ?

The taxamt 1/2 variables can be used in triggers to reference the taxes owed for each line item. The fields are calculated as or_exten * ta_rate. An example of the setup when using these variables is indicated below.

  • Trigger Event: Update
  • Table: Sales order header
  • Output: EDI
  • Destination: C:\TEMP\test.xml(some local location)
  • Conditions: to_descrip = 'EDI Order'
  • Message: Added taxamt1 and taxamt2 variables to SOLINES= part of the trigger's message that was copied from a customer

What are some strategies for troubleshooting triggers?

  • Ensure the trigger is marked Active and is not marked Template.
  • Ensure the Deacom Automation Service or Console is running.
  • Verify the IP Address listed in the current environments directory that the Deacom.ini SVCADDRESS is the IP address of the machine running the Automation Service or Console.
  • Validate overall trigger functionality. A quick test to verify that triggers overall are working would be an Update type trigger on the Vendor Master (dmvend) table with no conditions listed. Making a change to any Vendor while this is active should cause the trigger to fire and reflect in Trigger History.
  • Verify the table in focus is truly where the data is being changed or added. To test this, remove conditions from your trigger and perform the action again, or, query the recent entries to dxmod (Update Triggers) or dxlog (Insert Triggers) to see which table is being affected in the mo_table and lo_table fields respectively.
  • In versions 14.2 and below, SQL Mail must be set up on the SQL server for triggers to fire. Confirm with the SQL administrator if trying to fire a trigger and nothing shows up in the Trigger History.

Why am I receiving an 'Unable to send email for (Trigger name) to '[email protected]'. Reason: Failure sending mail' failure message in my Trigger history and none of my e-mail Triggers firing?

In versions 14.3 and above e-mail triggers must have the Trigger E-mail Host, Trigger Email Port, and Trigger Email From defined on the System > Options > Company tab.

Where are Triggers stored after they are saved?

Triggers are stored on the Server under the main database (not Docs or System) in the Triggers folder under the table they were created under. For example, a Bill-to trigger would be saved in the Triggers folder in SQL Server Management Studio under the dbo.dmbill table. The conditions, active status and other criteria entered in Deacom To create these, lives in the company database as well, in the table dxtrig.

Why have no Triggers fired after updating from 14.2 to 14.3?

In the Deacom.ini file, there are SVCADDR and SVCPORT lines. The IP address and port must match the server that the automation service/console is running on. If the IP address and port are changed, the automation service/console must be restarted.

Do you have an example date format of YYYYMMDD?

<<SUBSTR(ALLT(STR(YEAR(date()))), 1, 4) + PADL(ALLT(STR(MONTH(date()))),2,"0") + PADL(ALLT(STR(DAY(date()))),2,"0")>>.

Attempting to run Triggers with both the Deacom Automation Console and the Deacom Automation Service results in an 'Unable to start hosting. No primary key defined for the dxedihist table' message.

Generally, this indicates that the dxedihist table in SQL has become corrupted, or a change has been made to the table that does not allow it to function properly. In these scenarios, re-installing the most recent Deacom update applied to the database will resolve the error.

Attempting to write a Trigger that includes a User Defined Order Line field results in an error.

When using Order Line UDFs, they must be prefaced by ‘cUSERORD’. For example, u_allergyinfo would be on a trigger layout as ‘ALLT(cUSERORD.u_allergyinfo)’.

A 'Message' setup that had fired correctly in the past now returns an 'Invalid Expression' after updating to a new version.

Between builds, field names may be changed. Use System > Data Dictionary to search the fields in the Invalid Expression and ensure that they are still active fields. Additionally, masks change from being prefaced by a 'gc_' to requiring a 'go.' between 14.2 and 14.3.

Tips:

  • All information available to sales order header triggers is also available to sales order line triggers. In addition, Item Master User fields are available to the LOTINFO and MASTERLOTINFO table tags in triggers.
  • The sales order data gatherer makes the shipped via information available when an order is shipped, and the ship via information before it is shipped.
  • User Defined Fields marked as "Part Form Only" are available to Triggers for Sales Orders. User Defined Fields are sometimes marked as "Part Form Only" in order to ensure that are now included towards the total number of UDF fields allowed by Deacom.
  • Before an order is shipped, triggers will refer to the to_trid (Ship Via ID) of the order. After being shipped, triggers will refer to to_trid2 (Shipped Via ID) of the order.
  • Changes to the fields in System Options are immediately available to Triggers without requiring a restart of the automation console or service.

Subqueries in my trigger conditions do not appear to be working.

Often, if you’re using a subquery within your triggers conditions, which references the base table the trigger is defined on, the query will appear to not evaluate as expected. This is due to the trigger assuming we’re referencing the inserted data and will automatically append those table definitions within SQL Server as INSERTED.[table]. To avoid this issue, try aliasing your subqueries tables. For example, checking for an existing contact: bi_contact IN (SELECT sub.bi_contact FROM dmbill sub WHERE sub.bi_contact = bi_contact)

No triggers will fire, regardless of condition settings.

Typically, this will be a result of an incorrect SVCPORT definition in the environments Deacom.ini file located in the program directory – or the result of the Automation Service or Console not running. However, there are time where the company databases trigger assemblies are missing, corrupted or improperly named. Re-running the current version update with the Automation Service & Console turned off should resolve this.

How do I know what fields are available to my trigger, and how can I add more?

You can view fields available to your trigger by selecting the ‘Show Fields’ button on the trigger configuration form. If fields are not available that you wish to reference, the Custom Query section can be used to bring in additional fields. You can use available fields for reference in the WHERE clause of your Custom Query to tie to selection back to your current record.  Keep in mind, data from Custom Query is only available to use in the Message and Destination and cannot be used for conditions. For additional conditions, we recommend using a subquery.

Example: Adding Bill-to Name to a Sales Order Header trigger

SELECT bi_name

FROM dmbill

JOIN dttord on bi_id = to_biid

WHERE bi_id = <<to_biid>>