Database Trigger - MySQL

Situation:

After an outbound EDI file is generated by the B2B flow, we'd like to update the source database (pre-process) indicating the record(s) have been processed successfully

Strategy:

Use Adeptia's log table to identify that the file was sent successfully, then use a process flow update the source database accordingly. This can be achieved by using a database trigger.

Example:

• Create a Database Event for each Outbound file type
  • To lookup AU_GROUPDATA table for following condition
    DISPLAYSTATUS = 'SENT' and FLOWTYPE = 'OUTBOUND' and GS01 = 'QM'
  • Condition GS01 = 'QM' is used to identity Outbound file type like 214, Invoice, etc.
  • Below is the SQL Trigger script that will be used while defining Database Event

    CREATE TRIGGER DBTrigger AFTER UPDATE ON `au_groupdata` FOR EACH ROW
    BEGIN
    	IF (new.DISPLAYSTATUS = 'SENT' and new.FLOWTYPE = 'OUTBOUND' and GS01 = 'QM') THEN 
    	 	INSERT INTO dbeventtriggertable VALUES (concat('Query = WHERE ID =','''',new.ID,''''));
    	END IF;
    END
    

• The above database event will trigger a process flow specific to Outbound type.