Spaces:
Sleeping
Sleeping
File size: 3,935 Bytes
07c3cdd | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | CREATE TRIGGER APP_DELEGATION_UPDATE BEFORE UPDATE ON APP_DELEGATION
FOR EACH ROW
BEGIN
DECLARE DEFAULT_LANG VARCHAR(2);
DECLARE APP_NUMBER INT;
DECLARE APP_STATUS VARCHAR(32);
DECLARE APP_CREATE_DATE DATETIME;
DECLARE APP_UPDATE_DATE DATETIME;
DECLARE APP_TITLE VARCHAR(255);
DECLARE APP_PRO_TITLE VARCHAR(255);
DECLARE APP_TAS_TITLE VARCHAR(255);
DECLARE APP_CURRENT_USER VARCHAR(255);
DECLARE PREVIOUS_USR_UID VARCHAR(32);
DECLARE APP_DEL_PREVIOUS_USER VARCHAR(255);
DECLARE APP_THREAD_STATUS VARCHAR(32);
SET @DEFAULT_LANG = '{lang}';
SET @APP_CURRENT_USER = '';
SELECT APPLICATION.APP_NUMBER into @APP_NUMBER FROM APPLICATION WHERE APP_UID = NEW.APP_UID LIMIT 1;
SELECT APPLICATION.APP_STATUS into @APP_STATUS FROM APPLICATION WHERE APP_UID = NEW.APP_UID LIMIT 1;
SELECT APPLICATION.APP_CREATE_DATE into @APP_CREATE_DATE FROM APPLICATION WHERE APP_UID = NEW.APP_UID LIMIT 1;
SELECT APPLICATION.APP_UPDATE_DATE into @APP_UPDATE_DATE FROM APPLICATION WHERE APP_UID = NEW.APP_UID LIMIT 1;
SELECT APPLICATION.APP_TITLE into @APP_TITLE FROM APPLICATION WHERE NEW.APP_UID=APPLICATION.APP_UID LIMIT 1;
IF ( @APP_TITLE IS NULL ) THEN
SET @APP_TITLE = '';
END IF;
SELECT PROCESS.PRO_TITLE into @APP_PRO_TITLE FROM PROCESS WHERE NEW.PRO_UID=PROCESS.PRO_UID LIMIT 1;
SELECT TASK.TAS_TITLE into @APP_TAS_TITLE FROM TASK WHERE NEW.TAS_UID=TASK.TAS_UID LIMIT 1;
SELECT CONCAT(USERS.USR_LASTNAME, ' ', USERS.USR_FIRSTNAME) INTO @APP_CURRENT_USER FROM USERS WHERE USR_UID = NEW.USR_UID LIMIT 1;
IF ( @APP_CURRENT_USER IS NULL ) THEN
SET @APP_CURRENT_USER = '';
END IF;
IF ( NEW.DEL_PREVIOUS > 0 ) THEN
SELECT USR_UID INTO @PREVIOUS_USR_UID FROM APP_DELEGATION WHERE APP_UID = NEW.APP_UID AND DEL_INDEX = NEW.DEL_PREVIOUS LIMIT 1;
SELECT CONCAT(USERS.USR_LASTNAME, ' ', USERS.USR_FIRSTNAME) INTO @APP_DEL_PREVIOUS_USER FROM USERS WHERE USR_UID = @PREVIOUS_USR_UID LIMIT 1;
IF ( @APP_DEL_PREVIOUS_USER IS NULL ) THEN
SET @APP_DEL_PREVIOUS_USER = '';
END IF;
ELSE
SET @APP_DEL_PREVIOUS_USER = '';
SET @PREVIOUS_USR_UID = '';
END IF;
SELECT APP_THREAD_STATUS INTO @APP_THREAD_STATUS FROM APP_THREAD WHERE APP_UID = NEW.APP_UID AND DEL_INDEX = NEW.DEL_PREVIOUS LIMIT 1;
IF ( @APP_THREAD_STATUS IS NULL ) THEN
SET @APP_THREAD_STATUS = 'OPEN';
END IF;
SET @TAS_TYPE = (SELECT TAS_TYPE FROM TASK WHERE TAS_UID = NEW.TAS_UID LIMIT 1);
UPDATE `APP_CACHE_VIEW`
SET
APP_NUMBER = @APP_NUMBER,
APP_STATUS = @APP_STATUS,
USR_UID = NEW.USR_UID,
PREVIOUS_USR_UID = @PREVIOUS_USR_UID,
TAS_UID = NEW.TAS_UID,
PRO_UID = NEW.PRO_UID,
DEL_DELEGATE_DATE = NEW.DEL_DELEGATE_DATE,
DEL_INIT_DATE = NEW.DEL_INIT_DATE,
DEL_FINISH_DATE = NEW.DEL_FINISH_DATE,
DEL_TASK_DUE_DATE = NEW.DEL_TASK_DUE_DATE,
DEL_RISK_DATE = NEW.DEL_RISK_DATE,
DEL_THREAD_STATUS = NEW.DEL_THREAD_STATUS,
APP_THREAD_STATUS = @APP_THREAD_STATUS,
APP_TITLE = @APP_TITLE,
APP_PRO_TITLE = @APP_PRO_TITLE,
APP_TAS_TITLE = @APP_TAS_TITLE,
APP_CURRENT_USER = @APP_CURRENT_USER,
APP_DEL_PREVIOUS_USER = @APP_DEL_PREVIOUS_USER,
DEL_PRIORITY = NEW.DEL_PRIORITY,
DEL_DURATION = NEW.DEL_DURATION,
DEL_QUEUE_DURATION = NEW.DEL_QUEUE_DURATION,
DEL_DELAY_DURATION = NEW.DEL_DELAY_DURATION,
DEL_STARTED = NEW.DEL_STARTED,
DEL_FINISHED = NEW.DEL_FINISHED,
DEL_DELAYED = NEW.DEL_DELAYED,
APP_FINISH_DATE = NULL,
APP_UPDATE_DATE = @APP_UPDATE_DATE,
APP_OVERDUE_PERCENTAGE = NEW.APP_OVERDUE_PERCENTAGE,
DEL_LAST_INDEX = NEW.DEL_LAST_INDEX
WHERE
APP_UID = NEW.APP_UID
AND DEL_INDEX = NEW.DEL_INDEX;
END
|