Spaces:
Sleeping
Sleeping
File size: 3,884 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 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 | CREATE TRIGGER APP_DELEGATION_INSERT BEFORE INSERT 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 DEL_LAST_INDEX = 0 WHERE APP_UID = NEW.APP_UID;
INSERT INTO `APP_CACHE_VIEW` (
APP_UID,
DEL_INDEX,
APP_NUMBER,
APP_STATUS,
USR_UID,
PREVIOUS_USR_UID,
TAS_UID,
PRO_UID,
DEL_DELEGATE_DATE,
DEL_INIT_DATE,
DEL_FINISH_DATE,
DEL_TASK_DUE_DATE,
DEL_RISK_DATE,
DEL_THREAD_STATUS,
APP_THREAD_STATUS,
APP_TITLE,
APP_PRO_TITLE,
APP_TAS_TITLE,
APP_CURRENT_USER,
APP_DEL_PREVIOUS_USER,
DEL_PRIORITY,
DEL_DURATION,
DEL_QUEUE_DURATION,
DEL_DELAY_DURATION,
DEL_STARTED,
DEL_FINISHED,
DEL_DELAYED,
APP_CREATE_DATE,
APP_FINISH_DATE,
APP_UPDATE_DATE,
APP_OVERDUE_PERCENTAGE,
DEL_LAST_INDEX
)
VALUES (
NEW.APP_UID,
NEW.DEL_INDEX,
@APP_NUMBER,
@APP_STATUS,
NEW.USR_UID,
@PREVIOUS_USR_UID,
NEW.TAS_UID,
NEW.PRO_UID,
NEW.DEL_DELEGATE_DATE,
NEW.DEL_INIT_DATE,
NEW.DEL_FINISH_DATE,
NEW.DEL_TASK_DUE_DATE,
NEW.DEL_RISK_DATE,
NEW.DEL_THREAD_STATUS,
@APP_THREAD_STATUS,
@APP_TITLE,
@APP_PRO_TITLE,
@APP_TAS_TITLE,
@APP_CURRENT_USER,
@APP_DEL_PREVIOUS_USER,
NEW.DEL_PRIORITY,
NEW.DEL_DURATION,
NEW.DEL_QUEUE_DURATION,
NEW.DEL_DELAY_DURATION,
NEW.DEL_STARTED,
NEW.DEL_FINISHED,
NEW.DEL_DELAYED,
@APP_CREATE_DATE,
NULL,
@APP_UPDATE_DATE,
NEW.APP_OVERDUE_PERCENTAGE,
NEW.DEL_LAST_INDEX
);
END
|