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.

 

 

Have more questions? Submit a request

0 Comments

Article is closed for comments.