Trigger Examples

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. 

This page lists multiple Trigger Examples with full details. 

Email Trigger example

This example will cover a trigger that sends a plain text email anytime a Vendor is updated in the DEACOM system.

Setting up the email Trigger

  1. Navigate to System > Maintenance > Triggers
  2. Click the "New" button and fill in the “Edit Trigger” form as indicated below:
  • Name: Vendor Test Change
  • Trigger Event: Update
  • Table: Vendor master
  • Output: Plain text email
  • Subject: Vendor changed: <<ve_name>>
  • Destination: “select an appropriate email address”
  • Conditions: leave blank for this example
  • Message: The vendor <<ve_name>> has just been changed by <<lc_username>>.
<<ve_street>>
<<ve_street2>>
<<ve_city>>, <<ve_state>> <<ve_zip>>
<<TRANSFORM(ve_phone, “999-999-9999”)>>
  • Custom query: leave blank for this example
  • FTP server: N/A for this example
  • FTP user name: N/A for this example
  • FTP password: N/A for this example
  • Active: checked
  • Template: unchecked
  • Add line feeds after line items: checked
  • Accept invalid certificates for FTPS: unchecked

3. Once finished, click the "Save" and "Exit" buttons to close the "Edit Trigger" form. Next, click the "Exit" button on triggers list.

Running the email Trigger

  1. First, ensure either the Automation Service or Console is running.
  2. Next, navigate to Purchasing > Vendors and select a vendor to modify.
  3. On the "General" tab of the vendor record, type a value in the ID field and save.
  4. Next, check the mailbox of the email address in the example and the email with the correct message information generated by the trigger should be visible.

ACH payments send email Trigger example

This example will cover a trigger that sends an email to the vendor when an ACH payment is created within the DEACOM system. This will only fire if the payment type during the check run is ACH and it is a payment (not a void).

Setting up the ACH Trigger

  • Name: ACH Payment
  • Trigger event: Insert
  • Table: Header for cash transactions (dtcash)
  • Output: Plain text email
  • Subject: ACH Payment
  • Destination: <<ve_email>>
  • Conditions: ca_c3id = 2 AND ca_check NOT LIKE '%void%' AND ca_arap = 'ap'
  • Message: Hello, An ACH payment was made to you today. The funds will deposit into your account by the next business day. The details of the payment are below. If you have any questions please email [email protected]
  • Details on Payment:
  • Vendor: <<ve_name>>
  • Paid Date: <<DTOC(tp_paydate)>>

<<LINES= 'Total Cash: $' + TRANS(c2_credits - c2_debits, '999,999,999.99') + chr(9) +'Purchase Order:' + ' ' + TRANS(tp_purnum, '9999-99-99999') + chr(9) + 'Vendor PO:' + ' ' + ALLT(tp_vendinv)>>

  • Subtotal: <<TRANS(ca_totamt, '$$$,$$$,$$9.99')>>

Thank You - Tom Smith- AP Dept

  • Custom Query:

SELECT *

FROM dtcash2

JOIN dtcash on ca_postref = c2_postref

JOIN dmcash3 on c3_id = ca_c3id

JOIN dttpur on tp_purnum = c2_ordnum

JOIN dmvend on ve_id = tp_veid

WHERE c2_postref = '<<ca_postref>>' AND c2_Type = 'cash' AND c3_name = 'ACH'

Note: ca_c3id=2 and c3_name = ‘ACH’ are representative of the Payment Type and Payment Type Name  dependent on customer data. This data can be reviewed in Accounting > Maintenance > Payment Types.

Running the ACH Trigger

  1. First, ensure that your DEACOM.INI file for the environment is pointing to the correct machine and this machine is either the Automation Service or Console is running. If the IP Address needs to be changed, ensure the service or console is restarted afterwards.
  2. Next, perform a check run or manual check payment for the vendor(s), ensuring the payment type represents the ACH payment type.
  3. Next, check the mailbox of the email address in the example and the email with the correct message information generated by the trigger should be visible. If the email does not arrive, review the Trigger History for more information.

EDI Trigger example

This example will cover a trigger that creates an 810 invoice when an invoice for an EDI Order is printed in the DEACOM system. Below is how the resulting file will appear when viewed in Notepad:

ST*810*20121912100

G01*20120425***20120425*99999

N1*RE*Demo System*9*118035211

N3*950 West Valley Road

N4*Wayne*PA*19087

N1*ST*MyCustomer*9*012345

N3*123 Any Street

N4*Any Town*PA*19355

G17*10*EA*300.00*FINI1

G69*Finished good1 -BF

G17*5*EA*25.00*FINI16

G69*Finished good16 -BF

G17*30*EA*1.00*FINI2

G69*Two FINI1 together

G33*315500

Setting up the EDI Trigger

  1. Navigate to System > Maintenance > Triggers.
  2. Click the "New" button and fill in the "Edit Trigger" form as indicated below:
  • Name: 810 Invoice
  • Trigger Event: Update
  • Table: Sales order header
  • Output: EDI
  • Subject: N/A for this example
  • Destination: “enter an appropriate path” + \so<<ALLT(STR(to_ordnum, 15))>>.txt Example: \\Server2\Test\EDI\Invoice\so<<ALLT(STR(to_ordnum, 15))>>.txt  This will create a file with the name “so20120000100.txt” in the folder \\Server2\Test\EDI\Invoice for example.
  • Conditions: to_descrip = 'EDI Import' AND to_invdate IS NOT NULL AND old_to_invdate IS NULL AND to_ordnum NOT IN (SELECT dttord.to_ordnum FROM dttord WHERE dttord.to_ordnum = inserted.to_ordnum AND dttord.to_history LIKE '[null %un-invoiced%]') **This condition will cause the trigger to fire only once when the order is invoiced for the 1st time. The trigger will not fire if the order is un-invoiced and re-invoiced.
  • Message: ST*810*<<TRANSFORM(to_ordnum,"99999999999")>>
G01*<<DTOS(to_invdate)>>***<<DTOS(to_orddate)>>*<<ALLT(to_billpo)>>
N1*RE*<<ALLTRIM(df_remit)>>*9*118035211
N3*<<ALLT(df_remit2)>>
N4*<<ALLT(df_remit3)>>*<<ALLT(df_remit4)>>*<<ALLT(df_remit5)>>
N1*ST*<<ALLT(sh_name)>>*9*<<ALLT(sh_custid)>>
N3*<<ALLT(sh_street)>>
N4*<<ALLT(sh_city)>>*<<ALLT(sh_state)>>*<<ALLT(sh_zip)>>
<<SOLINES=IIF(or_linenum = 1,"",chr(13) + chr(10)) + "G17*" + ALLT(TRANSFORM(or_qship, "999999999")) + "*" + "EA" + "*" + ALLT(TRANSFORM(or_price, "9999999.99")) + "*" + ALLT(pr_retail) + chr(13) + chr(10)
+ "G69*" + ALLT(pr_descrip)>> G33*<<ALLT(TRANSFORM(to_totdue * 100, "999999999"))>>
  • Custom query: leave blank for this example
  • FTP server: N/A for this example
  • FTP user name: N/A for this example
  • FTP password: N/A for this example
  • Active: checked
  • Template: unchecked
  • Add line feeds after line items: checked
  • Accept invalid certificates for FTPS: unchecked

3. Once finished, click the "Save" and "Exit" buttons to close the Edit Trigger form. Next, click the "Exit" button on triggers list.

Notes:

  • The message above contains a system tag entitled "SOLINES". The system tag allows the EDI message field to access the sales order line fields in the system, which allows them to be included in conditional statements when determining the message or information that will be contained in the EDI export file.
  • If using sales order dates as conditions for EDI triggers, users may wish to consider the effect of the "Execute SO Date Calcs On Backorders" flag in Sales > Options > Order Entry tab. If this flag is checked, then any SO date calcs within Sales Options will be executed/calculated whenever a backorder sales order is created. If unchecked, SO date calcs in Sales Options will not be executed when a backorder is created. Unchecking the box may be helpful in situations where triggers and/or EDI transactions are being used in the DEACOM system and use sales order dates as a condition for processing. In these cases, having this flag unchecked ensures that backorders will not automatically trigger EDI transactions.

Running the EDI Trigger

  1. First, ensure either the Automation Service or Console is running and the order created during the EDI Import example above has been shipped.
  2. Next, navigate to Printouts > Invoices > View > Select the order in the grid and click "Print One".
  3. Navigate to System > View Trigger History.
  4. The first record in the grid should show the file that was created and the path where it was placed (It may take a minute or two for the trigger history to be updated).
  5. Check the path referenced in the trigger history and the 810 Invoice file that was created should be visible.

Shipment Notification

  • Name: Shipment Notification
  • Trigger Event: Update
  • Table: Sales Order Header
  • Output: Plain text or HTML
  • Subject: Shipment Notification - <<TRANSFORM(to_ordnum, go.Ordmask)>>
  • Destination: The email address or group address that should be emailed.
  • Conditions statement: to_shipped IS NOT null AND to_invdate IS null AND to_balance > 0 AND old_to_shipped IS NULL
  • Message: (can be modified)

<div>Good news - your order has shipped!</br></br>

<table style="border-style:none"> <tr><td><b>Shipped Date:</b></td><td><<to_shipped>></td></tr> <tr><td><b>Customer PO Number:</b></td><td><<to_billpo>></td></tr> <tr><td><b>Order Number:</b></td><td><<TRANSFORM(to_ordnum, go.Ordmask)>></td></tr> <tr><td><b>Ship Via:</b></td><td><<tr_name>></td></tr> <tr><td><b>Tracking Number:</b></td><td><<to_confirm>></td></tr> <tr><td><b>Ship-To:</b></td><td><<IIF(EMPTY(u_shipping_name), sh_name, u_shipping_name)>></td></tr> <tr><td></td><td><<IIF(EMPTY(u_shipping_name), sh_street, u_Shipping_Address1)>></td></tr> <tr><td></td><td><<IIF(Empty(u_shipping_name), sh_street2, u_Shipping_Address2)>></td></tr> <tr><td></td><td><<IIF(Empty(u_shipping_name), sh_city, u_Shipping_City)>>, <<IIF(Empty(u_shipping_name), sh_state, u_Shipping_State)>> <<IIF(Empty(u_shipping_name), sh_zip, u_Shipping_Zip)>></td></tr> </table></div>

</br>

<div>Thank You!</br></br>

Credit Hold removed from Sales Order

  • Name: Credit Hold To Sales Order (can be modified)
  • Trigger Event: Insert
  • Table: Sales order header
  • Output: Plain text email
  • Subject: <<transform(m.to_ordnum,"9999-99999-99")>> Off Credit Hold (can be modified)
  • Destination: The email address or group address that should be emailed.
  • Conditions statement: (SELECT MAX(dttord.to_id) FROM dttord WHERE dttord.to_id IN (SELECT TOP 2 dttord.to_id FROM dttord WHERE dttord.to_ordnum = inserted.to_ordnum ORDER BY dttord.to_id DESC) AND dttord.to_ordtype LIKE '%h%') > 0 AND inserted.to_ordtype = 's'
  • Message: (can be modified) The following order <<transform(m.to_ordnum,"9999-99999-99")>> is now off credit hold.

Line added to a Sales Order for a Part with no on hand inventory

  • Name: (user-defined)
  • Trigger Event: Insert
  • Table: Sales order lines
  • Output: EDI
  • Conditions statement: or_prid not in (SELECT fi_prid FROM dtfifo JOIN or_prid=fi_prid WHERE fi_zeroed is null)
  • Message: <<ALLTRIM(pr_codenum)>> is out of stock.
  • Custom Query: select pr_codenum from dmprod where pr_id = <<or_prid>>

Customer Part Cross Reference updated and Shelf Life Days on xref is less than 10 days

  • Name: (user defined)
  • Trigger Event: Both
  • Table: Customer part number
  • Output: Plain text email
  • Destination: The email address or group address that should be emailed.
  • Conditions statement: cu_shelfdays < 10
  • Message: Customer <<ALLTRIM(bi_name)>> has a part with shelf life less than 1 day.  Part <<ALLTRIM(pr_codenum)>>, customer part <<ALLTRIM(cu_codenum)>> was changed.
  • Custom Query:

SELECT bi_name, pr_codenum  FROM dmbill JOIN dmprod ON 1=1 WHERE bi_id = <<cu_biid>> AND pr_id = <<cu_prid>>

Notify a user when they need to log into DEACOM and approve a Work Flow Sequence on a Purchase Order

  • Name: (user-defined)
  • Trigger Event: Insert
  • Table: Work flow history dxtrak3
  • Output: Plain text email
  • Subject: (user-defined)
  • Destination: The email address or group address that should be emailed.
  • Conditions: t3_usid2 = 8 AND t3_date IS NULL (where '8' is the users id number). To get the correct t3_usid2 value: System > Options > select the user > click the "View All Fields" button in the toolbar. Note the value in the us_id field.
  • Message: (user-defined)

Purchase Order Work Flow Sequence completed by a user

  • Name: (user-defined)
  • Trigger Event: Insert
  • Table: Work flow history dxtrak3
  • Output: Plain text email
  • Subject: (user-defined, e.g. PO # <<transform(m.tp_purnum,"9999-99999-99")>> Approved - Please Place Order)
  • Destination: The email address or group address that should be emailed.
  • Conditions: t3_t2id='119' AND t3_usid='66' AND t3_table='dttpur' AND t3_date IS NOT NULL (t3_t2id is the numerical id of the work flow sequence and t3_usid is the numerical id of the user. "dttpur" is the purchase order table.)
  • Message: user-defined, e.g. "The following order (see the Custom Query below. This query will provide the PO number to the message) has been approved by Joe Smith. You may now place the order with the vendor".
  • To get the correct t3_t2id value: Tools > Edit Work Flows > select the appropriate work flow > click "Modify" > highlight the sequence for which the Trigger should fire > click the "View All Fields" button in the toolbar. Note the value in the T2_id field. To get the correct t5_usid value: System > Options > select the user > click the "View All Fields" button in the toolbar. Note the value in the us_id field.
  • To get the purchase order number, that is not included in the workflow history, a Custom Query (below) was added to the Trigger
  • Custom Query: SELECT tp_purnum FROM dttpur WHERE tp_id = <<t3_recid>>

Custom Automated EDI Export

The below example covers the use of the LINES block. Similar to SOLINES, though not limited to the use of sales order fields. The below trigger would be setup to fire on a scheduled event nightly and export each sales order line shipped that day and the order, quantity and bill-to the shipment was for. This can be done via import profile, setup by DEACOM Support or our Data Team – or by using a table which has automated updates, such as foreign currency. For this example, we will rely on the automated update of currencies from the Automation Service / Console.

  • Name: Daily Order Export
  • Trigger Event: Update
  • Table: Currencies (dmfcur)
  • Output: EDI
  • Subject: Anything or leave blank
  • Destination: (Desired File Path)\ <<ExportDate>>.csv
  • Conditions: fc_name = 'CAD' AND (SELECT COUNT(to_ordnum) FROM dttord WHERE to_shipped = (SELECT FORMAT(GETDATE(), 'MM-dd-yyyy'))) > 0 (Note: ‘CAD’ Can be any non-default entry in your dmfcur table. Verify through: Accounting > Maintenance > Currencies)
  • Message:

Deacom_Order_ID,Bill_To_PO,ShipVia,Part_Number,Shipped<<chr(13)>><<chr(10)>><<LINES = ALLT(TRANS(Deacom_Order_ID)) + ',' + ALLT(TRANS(Bill_to_PO)) + ',' + ALLT(TRANS(ShipVia)) + ',' + ALLT(TRANS(Part_Number)) + ',' + ALLT(TRANS(Shipped))>>

  • Custom Query:

SELECT or_ordnum AS Deacom_Order_ID, to_billpo AS Bill_to_PO, tr_name AS ShipVia,

pr_codenum AS Part_Number, or_shipquant AS Shipped, REPLACE(REPLACE(REPLACE(CAST(getdate() AS VARCHAR), '-', ''), ':', ''), ' ', '') AS ExportDate

FROM dtord

JOIN dttord on to_id = or_toid

JOIN dmprod on or_prid=pr_id

JOIN dmtruk on tr_id=to_trid2

JOIN dmbill on bi_id = to_biid

WHERE to_status = 'c'

            AND to_ordtype = 's'

            AND to_shipped is not null

            AND to_shipped = (SELECT FORMAT(GETDATE(), 'MM-dd-yyyy'))

ORDER BY to_ordnum DESC

Modification to a field on an Item Master record

  • Name: Unit Weight Changed
  • Trigger Event: Update
  • Table: Item master file
  • Output: Plain text email
  • Subject: Unit Weight changed: <<pr_codenum>>
  • Conditions: old_pr_unitwgt IS NULL OR old_pr_unitwgt IS NOT NULL and pr_unitwgt IS NOT NULL
  • Message: The item master for <<pr_codenum>> has just been changed by <<lc_Username>>.
  • Description: <<pr_descrip>>
  • Old unit weight: <<old_pr_unitwgt>>
  • New unit weight: <<pr_unitwgt>>

Notify a user when a Facility is changed on an order

Use the following Conditions statement on an Insert trigger: to_waid <> (SELECT dttord.to_waid FROM dttord WHERE dttord.to_id IN (SELECT MAX(dttord.to_id) FROM dttord WHERE dttord.to_ordnum = inserted.to_ordnum AND dttord.to_status = 'h')).

Certain customers have invoices created in DEACOM

  • Name = [Your Choice]
  • Trigger Event = Update
  • Table = Sales order header
  • Output = Plain text email
  • Subject = [Subject of email you would like]
  • Destination = [Email address where trigger should fire to]
  • Conditions = to_invdate IS NOT NULL AND old_to_invdate IS NULL AND to_biid IN (110,120) - These numbers indicate the unique ID (to_biid) of the customers on the orders that have been invoiced.
  • Message = The following order has been invoiced: Order: <<TRANS(to_ordnum, "9999-99999-99")>> Bill-To: <<ALLT(bi_name)>> Ship-To: <<ALLT(sh_name)>>

Condition example that uses single quotes around your data

If you are wanting to write a condition using 'Yes' or 'No', you have to add two extra single quotes to escape the single quote in the data. For example:

(mo_field = 'bi_active' AND RTRIM(mo_oldval) = '''No''' AND RTRIM(mo_newval) = '''Yes''')