processmaker-files/workflow/engine/methods/setup/setupSchemas/triggerSubApplicationInsert.sql

CREATE TRIGGER SUB_APPLICATION_INSERT BEFORE INSERT ON SUB_APPLICATION
FOR EACH ROW
BEGIN
    DECLARE PREVIOUS_USR_UID VARCHAR(32);

    SET @PREVIOUS_USR_UID = '';

    SET @PREVIOUS_USR_UID = (
        SELECT APPDEL2.USR_UID
        FROM   APP_DELEGATION AS APPDEL1, APP_DELEGATION AS APPDEL2
        WHERE  APPDEL1.APP_UID = NEW.APP_PARENT AND APPDEL1.DEL_INDEX = NEW.DEL_INDEX_PARENT AND
               APPDEL1.APP_UID = APPDEL2.APP_UID AND APPDEL1.DEL_PREVIOUS = APPDEL2.DEL_INDEX
        LIMIT 1
    );

    IF (@PREVIOUS_USR_UID IS NULL) THEN
        SET @PREVIOUS_USR_UID = '';
    END IF;

    -- Update record in APP_CACHE_VIEW
    UPDATE APP_CACHE_VIEW
    SET    PREVIOUS_USR_UID = @PREVIOUS_USR_UID
    WHERE  APP_UID = NEW.APP_UID AND DEL_INDEX = (SELECT APPDEL.DEL_INDEX
                                                  FROM   APP_DELEGATION AS APPDEL
                                                  WHERE  APPDEL.APP_UID = NEW.APP_UID AND APPDEL.DEL_PREVIOUS = 0
                                                  LIMIT 1
                                                 );

END