· 8 years ago · Mar 29, 2018, 12:24 PM
1/*
2SQLyog Community v12.5.1 (64 bit)
3MySQL - 10.2.7-MariaDB-10.2.7+maria~xenial-log : Database - Elibrary
4*********************************************************************
5*/
6
7/*!40101 SET NAMES utf8 */;
8
9/*!40101 SET SQL_MODE=''*/;
10
11/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
12/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
13/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
14/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
15CREATE DATABASE /*!32312 IF NOT EXISTS*/`Elibrary` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;
16
17USE `Elibrary`;
18
19/* Trigger structure for table `CUSTOMERORDERS` */
20
21DELIMITER $$
22
23/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BIR_CUSTOMERORDERS` */$$
24
25/*!50003 CREATE */ /*!50003 TRIGGER `CG$BIR_CUSTOMERORDERS` BEFORE INSERT ON `CUSTOMERORDERS` FOR EACH ROW BEGIN
26 -- AR 180215 : Work in Progress
27 DECLARE vitCUO_ID INT(38);
28 DECLARE vitCUO_ORI_ID INT(38);
29 DECLARE vitCUO_CUS_ID INT(38);
30
31 SET @vitCUO_ID = NEW.CUO_ID;
32 SET @vitCUO_ORI_ID = NEW.CUO_ORI_ID;
33 SET @vitCUO_CUS_ID = NEW.CUO_CUS_ID;
34
35 CALL CG$CUSTOMERORDERS_ins(@vitCUO_ID,FALSE);
36
37END */$$
38
39
40DELIMITER ;
41
42/* Trigger structure for table `CUSTOMERORDERS` */
43
44DELIMITER $$
45
46/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BUR_CUSTOMERORDERS` */$$
47
48/*!50003 CREATE */ /*!50003 TRIGGER `CG$BUR_CUSTOMERORDERS` BEFORE UPDATE ON `CUSTOMERORDERS` FOR EACH ROW BEGIN
49 -- AR 180215 : Work in Progress
50 DECLARE vutCUO_ID INT(38);
51 DECLARE vutCUO_ORI_ID INT(38);
52 DECLARE vutCUO_CUS_ID INT(38);
53
54 SET @vutCUO_ID = OLD.CUO_ID;
55 SET @vutCUO_ORI_ID = NEW.CUO_ORI_ID;
56 SET @vutCUO_CUS_ID = NEW.CUO_CUS_ID;
57
58 CALL CG$CUSTOMERORDERS_upd(@vutCUO_ID,FALSE);
59
60END */$$
61
62
63DELIMITER ;
64
65/* Trigger structure for table `CUSTOMERORDERS` */
66
67DELIMITER $$
68
69/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$ADR_CUSTOMERORDERS` */$$
70
71/*!50003 CREATE */ /*!50003 TRIGGER `CG$ADR_CUSTOMERORDERS` AFTER DELETE ON `CUSTOMERORDERS` FOR EACH ROW BEGIN
72 -- AR 180215 : Work in Progress
73 DECLARE vCUO_ID INT(38);
74
75 SELECT OLD.CUO_ID INTO vCUO_ID;
76
77 CALL CG$CUSTOMERORDERS_DEL(vCUO_ID,FALSE);
78
79END */$$
80
81
82DELIMITER ;
83
84/* Trigger structure for table `CUSTOMERS` */
85
86DELIMITER $$
87
88/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BIR_CUSTOMERS` */$$
89
90/*!50003 CREATE */ /*!50003 TRIGGER `CG$BIR_CUSTOMERS` BEFORE INSERT ON `CUSTOMERS` FOR EACH ROW BEGIN
91 -- AR 180215 : Work in Progress
92 DECLARE vutCUS_ID int(38);
93 DECLARE vutCUS_EMAIL varchar(100);
94 DECLARE vutCUS_FIRSTNAME varchar(50);
95 DECLARE vutCUS_LASTNAME varchar(50);
96 DECLARE vutCUS_INSERTEDBY varchar(50);
97 DECLARE vutCUS_UPDATEDBY varchar(50);
98 DECLARE vutCUS_CREATED datetime(6);
99 DECLARE vutCUS_CREATEDBY varchar(30);
100 DECLARE vutCUS_MODIFIED datetime(6);
101 DECLARE vutCUS_MODIFIEDBY varchar(30);
102
103 SET @vitCUS_ID = NEW.CUS_ID;
104 SET @vitCUS_EMAIL = NEW.CUS_EMAIL;
105 SET @vitCUS_FIRSTNAME = NEW.CUS_FIRSTNAME;
106 SET @vitCUS_LASTNAME = NEW.CUS_LASTNAME;
107 SET @vitCUS_INSERTEDBY = NEW.CUS_INSERTEDBY;
108 SET @vitCUS_UPDATEDBY = NEW.CUS_UPDATEDBY;
109
110 /*
111 * Start: Following fields value are set by Trigger
112 */
113 IF(NEW.CUS_INSERTEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.CUS_INSERTEDBY)) = 0)) THEN
114 SET NEW.CUS_INSERTEDBY = USER();
115 END IF;
116
117 IF(NEW.CUS_CREATEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.CUS_CREATEDBY)) = 0)) THEN
118 SET NEW.CUS_CREATEDBY = USER();
119 END IF;
120 SET NEW.CUS_CREATED = SYSDATE();
121
122 IF(NEW.CUS_MODIFIEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.CUS_MODIFIEDBY)) = 0)) THEN
123 SET NEW.CUS_MODIFIEDBY = USER();
124 END IF;
125 SET NEW.CUS_MODIFIED = SYSDATE();
126
127
128 /*
129 * Following logic is just a copy from Existing Oracle Trigger
130 */
131 IF(NEW.CUS_CREATED IS NULL) THEN
132 SET @vitCUS_CREATED = FALSE;
133 ELSE
134 SET @vitCUS_CREATED = TRUE;
135 END IF;
136
137 IF(NEW.CUS_CREATEDBY IS NULL) THEN
138 SET @vitCUS_CREATEDBY = FALSE;
139 ELSE
140 SET @vitCUS_CREATEDBY = TRUE;
141 END IF;
142
143 IF(NEW.CUS_MODIFIED IS NULL) THEN
144 SET @vitCUS_MODIFIED = FALSE;
145 ELSE
146 SET @vitCUS_MODIFIED = TRUE;
147 END IF;
148
149 IF(NEW.CUS_MODIFIEDBY IS NULL) THEN
150 SET @vitCUS_MODIFIEDBY = FALSE;
151 ELSE
152 SET @vitCUS_MODIFIEDBY = TRUE;
153 END IF;
154
155 CALL CG$CUSTOMERS_ins(@vitCUS_ID,FALSE);
156
157END */$$
158
159
160DELIMITER ;
161
162/* Trigger structure for table `CUSTOMERS` */
163
164DELIMITER $$
165
166/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BUR_CUSTOMERS` */$$
167
168/*!50003 CREATE */ /*!50003 TRIGGER `CG$BUR_CUSTOMERS` BEFORE UPDATE ON `CUSTOMERS` FOR EACH ROW BEGIN
169 -- AR 180215 : Work in Progress
170 DECLARE vutCUS_ID INT(38);
171 DECLARE vutCUS_EMAIL VARCHAR(100);
172 DECLARE vutCUS_FIRSTNAME VARCHAR(50);
173 DECLARE vutCUS_LASTNAME VARCHAR(50);
174 DECLARE vutCUS_INSERTEDBY VARCHAR(50);
175 DECLARE vutCUS_UPDATEDBY VARCHAR(50);
176 DECLARE vutCUS_CREATED DATETIME(6);
177 DECLARE vutCUS_CREATEDBY VARCHAR(50);
178 DECLARE vutCUS_MODIFIED DATETIME(6);
179 DECLARE vutCUS_MODIFIEDBY VARCHAR(50);
180
181 SET @vutCUS_ID = OLD.CUS_ID;
182 SET @vutCUS_EMAIL = NEW.CUS_EMAIL;
183 SET @vutCUS_FIRSTNAME = NEW.CUS_FIRSTNAME;
184 SET @vutCUS_LASTNAME = NEW.CUS_LASTNAME;
185 SET @vutCUS_INSERTEDBY = NEW.CUS_INSERTEDBY;
186 SET @vutCUS_UPDATEDBY = NEW.CUS_UPDATEDBY;
187 SET @vutCUS_MODIFIED = NEW.CUS_MODIFIED;
188 SET @vutCUS_MODIFIEDBY = NEW.CUS_MODIFIEDBY;
189
190 /*
191 * Start: Following fields value are set by Trigger
192 */
193
194 IF(NEW.CUS_UPDATEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.CUS_UPDATEDBY)) = 0)) THEN
195 SET NEW.CUS_UPDATEDBY = USER();
196 END IF;
197
198 IF(NEW.CUS_MODIFIEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.CUS_MODIFIEDBY)) = 0)) THEN
199 SET NEW.CUS_MODIFIEDBY = USER();
200 END IF;
201 SET NEW.CUS_MODIFIED = SYSDATE();
202
203 /*
204 * Start: Following fields value are set by Trigger
205 */
206 SET @vutCUS_UPDATEDBY = NEW.CUS_UPDATEDBY;
207 SET @vutCUS_MODIFIED = NEW.CUS_MODIFIED;
208 SET @vutCUS_MODIFIEDBY = NEW.CUS_MODIFIEDBY;
209
210
211 CALL CG$CUSTOMERS_upd(@vutCUS_ID,FALSE);
212
213END */$$
214
215
216DELIMITER ;
217
218/* Trigger structure for table `CUSTOMERS` */
219
220DELIMITER $$
221
222/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$ADR_CUSTOMERS` */$$
223
224/*!50003 CREATE */ /*!50003 TRIGGER `CG$ADR_CUSTOMERS` AFTER DELETE ON `CUSTOMERS` FOR EACH ROW BEGIN
225 -- AR 180215 : Work in Progress
226 DECLARE vCUS_ID INT(38);
227
228 SELECT OLD.CUS_ID INTO vCUS_ID;
229
230 CALL CG$CUSTOMERS_DEL(vCUS_ID,FALSE);
231
232END */$$
233
234
235DELIMITER ;
236
237/* Trigger structure for table `DOCUMENTDATA` */
238
239DELIMITER $$
240
241/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BIR_DOCUMENTDATA` */$$
242
243/*!50003 CREATE */ /*!50003 TRIGGER `CG$BIR_DOCUMENTDATA` BEFORE INSERT ON `DOCUMENTDATA` FOR EACH ROW
244BEGIN
245 /* Tahmina Aziz : Work in Progress */
246 DECLARE vitDOD_ID INT(38);
247 DECLARE vitDOD_ORI_ID DECIMAL(38,0);
248 DECLARE vitDOD_LII_ID DECIMAL(38,0);
249 DECLARE vitDOD_DOCTYPE VARCHAR(2);
250 DECLARE vitDOD_TITLE TEXT;
251 DECLARE vitDOD_JOURNAL_NAME VARCHAR(255);
252 DECLARE vitDOD_PUBL_YEAR DECIMAL(4,0);
253 DECLARE vitDOD_AUTHORS TEXT;
254 DECLARE vitDOD_VOLUME VARCHAR(50);
255 DECLARE vitDOD_ISSUE VARCHAR(50);
256 DECLARE vitDOD_PAGEINFO VARCHAR(100);
257 DECLARE vitDOD_LANGUAGE VARCHAR(100);
258 DECLARE vitDOD_KEYWORDS TEXT;
259 DECLARE vitDOD_AFFILIATION TEXT;
260 DECLARE vitDOD_ABSTRACT TEXT;
261 DECLARE vitDOD_PMID DECIMAL(10,0);
262 DECLARE vitDOD_DOI VARCHAR(255);
263 DECLARE vitDOD_ISSN VARCHAR(10);
264 DECLARE vitDOD_MESH_TERMS TEXT;
265 DECLARE vitDOD_SUBSTANCES TEXT;
266 DECLARE vitDOD_PUB_TYPE VARCHAR(255);
267 DECLARE vitDOD_EDITOR_NAME VARCHAR(255);
268 DECLARE vitDOD_BOOKTITLE TEXT;
269 DECLARE vitDOD_PUBLISHER VARCHAR(255);
270 DECLARE vitDOD_ISBN VARCHAR(50);
271 DECLARE vitDOD_CONF_NAME VARCHAR(255);
272 DECLARE vitDOD_SPONSOR VARCHAR(100);
273 DECLARE vitDOD_PAT_APPL_DATE VARCHAR(50);
274 DECLARE vitDOD_PAT_APPL_NUM VARCHAR(50);
275 DECLARE vitDOD_PAT_PRIO_DATES TEXT;
276 DECLARE vitDOD_PAT_PRIO_NUM TEXT;
277 DECLARE vitDOD_PAT_PUBL_DATE VARCHAR(50);
278 DECLARE vitDOD_PAT_PUBL_NUM VARCHAR(50);
279 DECLARE vitDOD_PRODUCT VARCHAR(255);
280 DECLARE vitDOD_INDICATION TEXT;
281 DECLARE vitDOD_KEY_PUBLICATION VARCHAR(1);
282 DECLARE vitDOD_CLINICAL_ARTICLE VARCHAR(1);
283 DECLARE vitDOD_COMMENT TEXT;
284 DECLARE vitDOD_INSERTEDBY VARCHAR(50);
285 DECLARE vitDOD_UPDATEDBY VARCHAR(50);
286 DECLARE vitDOD_CREATED DATETIME;
287 DECLARE vitDOD_CREATEDBY VARCHAR(30);
288 DECLARE vitDOD_MODIFIED DATETIME;
289 DECLARE vitDOD_MODIFIEDBY VARCHAR(30);
290 DECLARE vitDOD_LINKTYPE VARCHAR(50);
291 DECLARE vitDOD_URL TEXT;
292 DECLARE vitCUST_COUNTRY VARCHAR(255);
293 DECLARE vitDOD_LII_HIDDEN VARCHAR(1);
294 DECLARE vitDOD_LII_COMMENT TEXT;
295 DECLARE vitDOD_LII_CREATEDBY VARCHAR(50);
296 DECLARE vitDOD_LII_MODIFIEDBY VARCHAR(50);
297 DECLARE vitDOD_LII_INSERTEDBY VARCHAR(50);
298 DECLARE vitDOD_LII_UPDATEDBY VARCHAR(50);
299 DECLARE vitDOD_LII_CREATED DATETIME;
300 DECLARE vitDOD_LII_MODIFIED DATETIME;
301 DECLARE vitDOD_ORI_DUPE_OF_ORI_ID DECIMAL(38,0);
302 DECLARE vitDOD_ORI_ORDERID VARCHAR(50);
303 DECLARE vitDOD_ORI_PDF_DELIVERY_DATE DATETIME;
304 DECLARE vitDOD_ORI_STATUS VARCHAR(1);
305 DECLARE vitDOD_ORI_COMMENT TEXT;
306 DECLARE vitDOD_ORI_URGENCY VARCHAR(1);
307 DECLARE vitDOD_ORI_MAILBODY TEXT;
308 DECLARE vitDOD_ORI_ACTPUBMED VARCHAR(1);
309 DECLARE vitDOD_ORI_CREATEDBY VARCHAR(50);
310 DECLARE vitDOD_ORI_INSERTEDBY VARCHAR(50);
311 DECLARE vitDOD_ORI_UPDATEDBY VARCHAR(50);
312 DECLARE vitDOD_ORI_MODIFIEDBY VARCHAR(50);
313 DECLARE vitDOD_ORI_CREATED DATETIME;
314 DECLARE vitDOD_ORI_MODIFIED DATETIME;
315 DECLARE vitCONCAT_COLUMNS TEXT;
316 DECLARE vitDOD_DOI_FI_COL VARCHAR(255);
317 DECLARE vitDOD_ISBN_FI_COL VARCHAR(50);
318 DECLARE vitDOD_ISSN_FI_COL VARCHAR(10);
319 DECLARE vitDOD_ISSUE_FI_COL VARCHAR(50);
320 DECLARE vitDOD_LANGUAGE_FI_COL VARCHAR(100);
321 DECLARE vitDOD_ORI_ORDERID_FI_COL VARCHAR(50);
322 DECLARE vitDOD_PMID_FI_COL DECIMAL(10,0);
323 DECLARE vitDOD_PUBL_YEAR_FI_COL VARCHAR(4);
324 DECLARE vitROT_IDX1_COL DECIMAL(38,0);
325
326 DECLARE vCUS_EMAIL VARCHAR(100);
327
328
329
330 SET @vitDOD_ID := NEW.DOD_ID;
331 SET @vitDOD_ORI_ID := NEW.DOD_ORI_ID;
332 SET @vitDOD_LII_ID := NEW.DOD_LII_ID;
333 SET @vitDOD_DOCTYPE := NEW.DOD_DOCTYPE;
334 SET @vitDOD_TITLE := NEW.DOD_TITLE;
335 SET @vitDOD_JOURNAL_NAME := NEW.DOD_JOURNAL_NAME;
336 SET @vitDOD_PUBL_YEAR := NEW.DOD_PUBL_YEAR;
337 SET @vitDOD_AUTHORS := NEW.DOD_AUTHORS;
338 SET @vitDOD_VOLUME := NEW.DOD_VOLUME;
339 SET @vitDOD_ISSUE := NEW.DOD_ISSUE;
340 SET @vitDOD_PAGEINFO := NEW.DOD_PAGEINFO;
341 SET @vitDOD_LANGUAGE := NEW.DOD_LANGUAGE;
342 SET @vitDOD_KEYWORDS := NEW.DOD_KEYWORDS;
343 SET @vitDOD_AFFILIATION := NEW.DOD_AFFILIATION;
344 SET @vitDOD_ABSTRACT := NEW.DOD_ABSTRACT;
345 SET @vitDOD_PMID := NEW.DOD_PMID;
346 SET @vitDOD_DOI := NEW.DOD_DOI;
347 SET @vitDOD_ISSN := NEW.DOD_ISSN;
348 SET @vitDOD_MESH_TERMS := NEW.DOD_MESH_TERMS;
349 SET @vitDOD_SUBSTANCES := NEW.DOD_SUBSTANCES;
350 SET @vitDOD_PUB_TYPE := NEW.DOD_PUB_TYPE;
351 SET @vitDOD_EDITOR_NAME := NEW.DOD_EDITOR_NAME;
352 SET @vitDOD_BOOKTITLE := NEW.DOD_BOOKTITLE;
353 SET @vitDOD_PUBLISHER := NEW.DOD_PUBLISHER;
354 SET @vitDOD_ISBN := NEW.DOD_ISBN;
355 SET @vitDOD_CONF_NAME := NEW.DOD_CONF_NAME;
356 SET @vitDOD_SPONSOR := NEW.DOD_SPONSOR;
357 SET @vitDOD_PAT_APPL_DATE := NEW.DOD_PAT_APPL_DATE;
358 SET @vitDOD_PAT_APPL_NUM := NEW.DOD_PAT_APPL_NUM;
359 SET @vitDOD_PAT_PRIO_DATES := NEW.DOD_PAT_PRIO_DATES;
360 SET @vitDOD_PAT_PRIO_NUM := NEW.DOD_PAT_PRIO_NUM;
361 SET @vitDOD_PAT_PUBL_DATE := NEW.DOD_PAT_PUBL_DATE;
362 SET @vitDOD_PAT_PUBL_NUM := NEW.DOD_PAT_PUBL_NUM;
363 SET @vitDOD_PRODUCT := NEW.DOD_PRODUCT;
364 SET @vitDOD_INDICATION := NEW.DOD_INDICATION;
365 SET @vitDOD_KEY_PUBLICATION := NEW.DOD_KEY_PUBLICATION;
366 SET @vitDOD_CLINICAL_ARTICLE := NEW.DOD_CLINICAL_ARTICLE;
367 SET @vitDOD_COMMENT := NEW.DOD_COMMENT;
368 SET @vitDOD_LINKTYPE := NEW.DOD_LINKTYPE;
369 SET @vitDOD_URL := NEW.DOD_URL;
370 SET @vitCUST_COUNTRY := NEW.CUST_COUNTRY;
371 SET @vitDOD_LII_HIDDEN := NEW.DOD_LII_HIDDEN;
372 SET @vitDOD_LII_COMMENT := NEW.DOD_LII_COMMENT;
373 SET @vitDOD_ORI_DUPE_OF_ORI_ID := NEW.DOD_ORI_DUPE_OF_ORI_ID;
374 SET @vitDOD_ORI_ORDERID := NEW.DOD_ORI_ORDERID;
375 SET @vitDOD_ORI_PDF_DELIVERY_DATE := NEW.DOD_ORI_PDF_DELIVERY_DATE;
376 SET @vitDOD_ORI_STATUS := NEW.DOD_ORI_STATUS;
377 SET @vitDOD_ORI_COMMENT := NEW.DOD_ORI_COMMENT;
378 SET @vitDOD_ORI_URGENCY := NEW.DOD_ORI_URGENCY;
379 SET @vitDOD_ORI_MAILBODY := NEW.DOD_ORI_MAILBODY;
380 SET @vitDOD_ORI_ACTPUBMED := NEW.DOD_ORI_ACTPUBMED;
381
382 SET NEW.DOD_LII_INSERTEDBY := NULL;
383 SET NEW.DOD_LII_CREATEDBY = NULL;
384 SET NEW.DOD_LII_CREATED = NULL;
385 SET NEW.DOD_LII_MODIFIEDBY = NULL;
386 SET NEW.DOD_LII_UPDATEDBY = NULL;
387 SET NEW.DOD_LII_MODIFIED = NULL;
388
389 SET @vitDOD_LII_INSERTEDBY := NEW.DOD_LII_INSERTEDBY;
390 SET @vitDOD_LII_CREATEDBY := NEW.DOD_LII_CREATEDBY;
391 SET @vitDOD_LII_CREATED := NEW.DOD_LII_CREATED;
392 SET @vitDOD_LII_MODIFIEDBY := NEW.DOD_LII_MODIFIEDBY;
393 SET @vitDOD_LII_UPDATEDBY := NEW.DOD_LII_UPDATEDBY;
394 SET @vitDOD_LII_MODIFIED := NEW.DOD_LII_MODIFIED;
395
396 /*
397 * Start: Following fields value are set by Trigger
398 */
399 IF(NEW.DOD_INSERTEDBY IS NULL) THEN
400 SET NEW.DOD_INSERTEDBY = USER();
401 END IF;
402
403 IF(NEW.DOD_CREATEDBY IS NULL) THEN
404 SET NEW.DOD_CREATEDBY = USER();
405 END IF;
406
407 SET NEW.DOD_CREATED = SYSDATE();
408 SET @vitDOD_CREATED := NEW.DOD_CREATED;
409
410 IF(NEW.DOD_ORI_INSERTEDBY IS NULL) THEN
411 SET NEW.DOD_ORI_INSERTEDBY = USER();
412 END IF;
413
414 IF(NEW.DOD_ORI_CREATEDBY IS NULL) THEN
415 SET NEW.DOD_ORI_CREATEDBY := USER();
416 END IF;
417
418 SET NEW.DOD_ORI_CREATED := SYSDATE();
419 SET @vitDOD_ORI_CREATED := NEW.DOD_ORI_CREATED;
420
421 IF(NEW.DOD_MODIFIEDBY IS NULL) THEN
422 SET NEW.DOD_MODIFIEDBY = USER();
423 END IF;
424
425 IF(NEW.DOD_UPDATEDBY IS NULL) THEN
426 SET NEW.DOD_UPDATEDBY = USER();
427 END IF;
428
429 SET NEW.DOD_MODIFIED = SYSDATE();
430 SET @vitDOD_MODIFIED := NEW.DOD_MODIFIED;
431
432 IF(NEW.DOD_ORI_MODIFIEDBY IS NULL) THEN
433 SET NEW.DOD_ORI_MODIFIEDBY = USER();
434 END IF;
435
436 IF(NEW.DOD_ORI_UPDATEDBY IS NULL) THEN
437 SET NEW.DOD_ORI_UPDATEDBY = USER();
438 END IF;
439
440 SET NEW.DOD_ORI_MODIFIED = SYSDATE();
441 SET @vitDOD_ORI_MODIFIED := NEW.DOD_ORI_MODIFIED;
442
443 /*set NEW.DOD_DOI_FI_COL = LOWER(NEW.DOD_DOI);
444 SET NEW.DOD_ISBN_FI_COL = LOWER(NEW.DOD_ISBN);
445 SET NEW.DOD_ISSN_FI_COL = LOWER(NEW.DOD_ISSN);
446 SET NEW.DOD_ISSUE_FI_COL = LOWER(NEW.DOD_ISSUE);
447 SET NEW.DOD_LANGUAGE_FI_COL = LOWER(NEW.DOD_LANGUAGE);
448 SET NEW.DOD_ORI_ORDERID_FI_COL = NEW.DOD_ORI_ORDERID;
449 SET NEW.DOD_PMID_FI_COL = NEW.DOD_PMID;
450 SET NEW.DOD_PUBL_YEAR_FI_COL = NEW.DOD_PUBL_YEAR;
451 SET NEW.ROT_IDX1_COL = IFNULL(NEW.DOD_LII_ID,NEW.DOD_ORI_ID);
452 */
453
454 /*
455 * Start: Following fields value are set by Trigger
456 */
457 SET @vitDOD_INSERTEDBY := NEW.DOD_INSERTEDBY;
458 SET @vitDOD_CREATEDBY := NEW.DOD_CREATEDBY;
459 SET @vitDOD_ORI_INSERTEDBY := NEW.DOD_ORI_INSERTEDBY;
460 SET @vitDOD_ORI_CREATEDBY := NEW.DOD_ORI_CREATEDBY;
461
462 SET @vitDOD_MODIFIEDBY := NEW.DOD_MODIFIEDBY;
463 SET @vitDOD_UPDATEDBY := NEW.DOD_UPDATEDBY;
464 SET @vitDOD_ORI_MODIFIEDBY := NEW.DOD_ORI_MODIFIEDBY;
465 SET @vitDOD_ORI_UPDATEDBY := NEW.DOD_ORI_UPDATEDBY;
466
467
468 -- default values
469 IF (@vitDOD_KEY_PUBLICATION IS NULL OR (CHAR_LENGTH(TRIM(@vitDOD_KEY_PUBLICATION)) = 0)) THEN
470 SET @vitDOD_KEY_PUBLICATION := 'N';
471 SET NEW.DOD_KEY_PUBLICATION = @vitDOD_KEY_PUBLICATION;
472 END IF;
473
474 IF (@vitDOD_CLINICAL_ARTICLE IS NULL OR (CHAR_LENGTH(TRIM(@vitDOD_CLINICAL_ARTICLE)) = 0)) THEN
475 SET @vitDOD_CLINICAL_ARTICLE := 'N';
476 SET NEW.DOD_CLINICAL_ARTICLE = @vitDOD_CLINICAL_ARTICLE;
477 END IF;
478
479 IF (@vitDOD_LII_HIDDEN IS NULL OR (CHAR_LENGTH(TRIM(@vitDOD_LII_HIDDEN)) = 0)) THEN
480 SET @vitDOD_LII_HIDDEN := 'N';
481 SET NEW.DOD_LII_HIDDEN = @vitDOD_LII_HIDDEN;
482 END IF;
483
484
485 SET NEW.DOD_ORI_DUPE_OF_ORI_ID = NULL;
486
487 SELECT CUS_EMAIL
488 INTO vCUS_EMAIL
489 FROM CUSTOMERS, CUSTOMERORDERS
490 WHERE CUS_ID = CUO_CUS_ID
491 AND CUO_ORI_ID = NEW.DOD_ORI_ID;
492
493 SET NEW.CONCAT_COLUMNS =
494 (CONCAT(IFNULL(NEW.DOD_PMID,' ')
495 ,' ',IFNULL(NEW.DOD_PUBL_YEAR,' ')
496 ,' ',IFNULL(DATE_FORMAT(NEW.DOD_ORI_PDF_DELIVERY_DATE, '%Y.%m.%d'),' ')
497 ,' ',IFNULL(DATE_FORMAT(NEW.DOD_ORI_CREATED, '%Y.%m.%d'),' ')
498 ,' ',IFNULL(vCUS_EMAIL,' '))) ;
499
500
501 CALL CG$DOCUMENTDATA_validate_domain(@vitDOD_DOCTYPE);
502
503
504 END */$$
505
506
507DELIMITER ;
508
509/* Trigger structure for table `DOCUMENTDATA` */
510
511DELIMITER $$
512
513/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$AIR_DOCUMENTDATA` */$$
514
515/*!50003 CREATE */ /*!50003 TRIGGER `CG$AIR_DOCUMENTDATA` AFTER INSERT ON `DOCUMENTDATA` FOR EACH ROW
516BEGIN
517 /* Tahmina Aziz : Work in Progress */
518 DECLARE vitDOD_ID INT(38);
519 DECLARE vitDOD_ORI_ID DECIMAL(38,0);
520 DECLARE vitDOD_LII_ID DECIMAL(38,0);
521 DECLARE vitDOD_DOCTYPE VARCHAR(2);
522 DECLARE vitDOD_TITLE TEXT;
523 DECLARE vitDOD_JOURNAL_NAME VARCHAR(255);
524 DECLARE vitDOD_PUBL_YEAR DECIMAL(4,0);
525 DECLARE vitDOD_AUTHORS TEXT;
526 DECLARE vitDOD_VOLUME VARCHAR(50);
527 DECLARE vitDOD_ISSUE VARCHAR(50);
528 DECLARE vitDOD_PAGEINFO VARCHAR(100);
529 DECLARE vitDOD_LANGUAGE VARCHAR(100);
530 DECLARE vitDOD_KEYWORDS TEXT;
531 DECLARE vitDOD_AFFILIATION TEXT;
532 DECLARE vitDOD_ABSTRACT TEXT;
533 DECLARE vitDOD_PMID DECIMAL(10,0);
534 DECLARE vitDOD_DOI VARCHAR(255);
535 DECLARE vitDOD_ISSN VARCHAR(10);
536 DECLARE vitDOD_MESH_TERMS TEXT;
537 DECLARE vitDOD_SUBSTANCES TEXT;
538 DECLARE vitDOD_PUB_TYPE VARCHAR(255);
539 DECLARE vitDOD_EDITOR_NAME VARCHAR(255);
540 DECLARE vitDOD_BOOKTITLE TEXT;
541 DECLARE vitDOD_PUBLISHER VARCHAR(255);
542 DECLARE vitDOD_ISBN VARCHAR(50);
543 DECLARE vitDOD_CONF_NAME VARCHAR(255);
544 DECLARE vitDOD_SPONSOR VARCHAR(100);
545 DECLARE vitDOD_PAT_APPL_DATE VARCHAR(50);
546 DECLARE vitDOD_PAT_APPL_NUM VARCHAR(50);
547 DECLARE vitDOD_PAT_PRIO_DATES TEXT;
548 DECLARE vitDOD_PAT_PRIO_NUM TEXT;
549 DECLARE vitDOD_PAT_PUBL_DATE VARCHAR(50);
550 DECLARE vitDOD_PAT_PUBL_NUM VARCHAR(50);
551 DECLARE vitDOD_PRODUCT VARCHAR(255);
552 DECLARE vitDOD_INDICATION TEXT;
553 DECLARE vitDOD_KEY_PUBLICATION VARCHAR(1);
554 DECLARE vitDOD_CLINICAL_ARTICLE VARCHAR(1);
555 DECLARE vitDOD_COMMENT TEXT;
556 DECLARE vitDOD_INSERTEDBY VARCHAR(50);
557 DECLARE vitDOD_UPDATEDBY VARCHAR(50);
558 DECLARE vitDOD_CREATED DATETIME;
559 DECLARE vitDOD_CREATEDBY VARCHAR(30);
560 DECLARE vitDOD_MODIFIED DATETIME;
561 DECLARE vitDOD_MODIFIEDBY VARCHAR(30);
562 DECLARE vitDOD_LINKTYPE VARCHAR(50);
563 DECLARE vitDOD_URL TEXT;
564 DECLARE vitCUST_COUNTRY VARCHAR(255);
565 DECLARE vitDOD_LII_HIDDEN VARCHAR(1);
566 DECLARE vitDOD_LII_COMMENT TEXT;
567 DECLARE vitDOD_LII_CREATEDBY VARCHAR(50);
568 DECLARE vitDOD_LII_MODIFIEDBY VARCHAR(50);
569 DECLARE vitDOD_LII_INSERTEDBY VARCHAR(50);
570 DECLARE vitDOD_LII_UPDATEDBY VARCHAR(50);
571 DECLARE vitDOD_LII_CREATED DATETIME;
572 DECLARE vitDOD_LII_MODIFIED DATETIME;
573 DECLARE vitDOD_ORI_DUPE_OF_ORI_ID DECIMAL(38,0);
574 DECLARE vitDOD_ORI_ORDERID VARCHAR(50);
575 DECLARE vitDOD_ORI_PDF_DELIVERY_DATE DATETIME;
576 DECLARE vitDOD_ORI_STATUS VARCHAR(1);
577 DECLARE vitDOD_ORI_COMMENT TEXT;
578 DECLARE vitDOD_ORI_URGENCY VARCHAR(1);
579 DECLARE vitDOD_ORI_MAILBODY TEXT;
580 DECLARE vitDOD_ORI_ACTPUBMED VARCHAR(1);
581 DECLARE vitDOD_ORI_CREATEDBY VARCHAR(50);
582 DECLARE vitDOD_ORI_INSERTEDBY VARCHAR(50);
583 DECLARE vitDOD_ORI_UPDATEDBY VARCHAR(50);
584 DECLARE vitDOD_ORI_MODIFIEDBY VARCHAR(50);
585 DECLARE vitDOD_ORI_CREATED DATETIME;
586 DECLARE vitDOD_ORI_MODIFIED DATETIME;
587
588 SET @vitDOD_ID := NEW.DOD_ID;
589 SET @vitDOD_ORI_ID := NEW.DOD_ORI_ID;
590 SET @vitDOD_LII_ID := NEW.DOD_LII_ID;
591 SET @vitDOD_DOCTYPE := NEW.DOD_DOCTYPE;
592 SET @vitDOD_TITLE := NEW.DOD_TITLE;
593 SET @vitDOD_JOURNAL_NAME := NEW.DOD_JOURNAL_NAME;
594 SET @vitDOD_PUBL_YEAR := NEW.DOD_PUBL_YEAR;
595 SET @vitDOD_AUTHORS := NEW.DOD_AUTHORS;
596 SET @vitDOD_VOLUME := NEW.DOD_VOLUME;
597 SET @vitDOD_ISSUE := NEW.DOD_ISSUE;
598 SET @vitDOD_PAGEINFO := NEW.DOD_PAGEINFO;
599 SET @vitDOD_LANGUAGE := NEW.DOD_LANGUAGE;
600 SET @vitDOD_KEYWORDS := NEW.DOD_KEYWORDS;
601 SET @vitDOD_AFFILIATION := NEW.DOD_AFFILIATION;
602 SET @vitDOD_ABSTRACT := NEW.DOD_ABSTRACT;
603 SET @vitDOD_PMID := NEW.DOD_PMID;
604 SET @vitDOD_DOI := NEW.DOD_DOI;
605 SET @vitDOD_ISSN := NEW.DOD_ISSN;
606 SET @vitDOD_MESH_TERMS := NEW.DOD_MESH_TERMS;
607 SET @vitDOD_SUBSTANCES := NEW.DOD_SUBSTANCES;
608 SET @vitDOD_PUB_TYPE := NEW.DOD_PUB_TYPE;
609 SET @vitDOD_EDITOR_NAME := NEW.DOD_EDITOR_NAME;
610 SET @vitDOD_BOOKTITLE := NEW.DOD_BOOKTITLE;
611 SET @vitDOD_PUBLISHER := NEW.DOD_PUBLISHER;
612 SET @vitDOD_ISBN := NEW.DOD_ISBN;
613 SET @vitDOD_CONF_NAME := NEW.DOD_CONF_NAME;
614 SET @vitDOD_SPONSOR := NEW.DOD_SPONSOR;
615 SET @vitDOD_PAT_APPL_DATE := NEW.DOD_PAT_APPL_DATE;
616 SET @vitDOD_PAT_APPL_NUM := NEW.DOD_PAT_APPL_NUM;
617 SET @vitDOD_PAT_PRIO_DATES := NEW.DOD_PAT_PRIO_DATES;
618 SET @vitDOD_PAT_PRIO_NUM := NEW.DOD_PAT_PRIO_NUM;
619 SET @vitDOD_PAT_PUBL_DATE := NEW.DOD_PAT_PUBL_DATE;
620 SET @vitDOD_PAT_PUBL_NUM := NEW.DOD_PAT_PUBL_NUM;
621 SET @vitDOD_PRODUCT := NEW.DOD_PRODUCT;
622 SET @vitDOD_INDICATION := NEW.DOD_INDICATION;
623 SET @vitDOD_KEY_PUBLICATION := NEW.DOD_KEY_PUBLICATION;
624 SET @vitDOD_CLINICAL_ARTICLE := NEW.DOD_CLINICAL_ARTICLE;
625 SET @vitDOD_COMMENT := NEW.DOD_COMMENT;
626 SET @vitDOD_LINKTYPE := NEW.DOD_LINKTYPE;
627 SET @vitDOD_URL := NEW.DOD_URL;
628 SET @vitCUST_COUNTRY := NEW.CUST_COUNTRY;
629 SET @vitDOD_LII_HIDDEN := NEW.DOD_LII_HIDDEN;
630 SET @vitDOD_LII_COMMENT := NEW.DOD_LII_COMMENT;
631 SET @vitDOD_ORI_DUPE_OF_ORI_ID := NEW.DOD_ORI_DUPE_OF_ORI_ID;
632 SET @vitDOD_ORI_ORDERID := NEW.DOD_ORI_ORDERID;
633 SET @vitDOD_ORI_PDF_DELIVERY_DATE := NEW.DOD_ORI_PDF_DELIVERY_DATE;
634 SET @vitDOD_ORI_STATUS := NEW.DOD_ORI_STATUS;
635 SET @vitDOD_ORI_COMMENT := NEW.DOD_ORI_COMMENT;
636 SET @vitDOD_ORI_URGENCY := NEW.DOD_ORI_URGENCY;
637 SET @vitDOD_ORI_MAILBODY := NEW.DOD_ORI_MAILBODY;
638 SET @vitDOD_ORI_ACTPUBMED := NEW.DOD_ORI_ACTPUBMED;
639
640 /*
641 * Start: Following fields value are set by Trigger
642 */
643
644 SET @vitDOD_INSERTEDBY := NEW.DOD_INSERTEDBY;
645 SET @vitDOD_CREATEDBY := NEW.DOD_CREATEDBY;
646 SET @vitDOD_CREATED := NEW.DOD_CREATED;
647
648 SET @vitDOD_ORI_INSERTEDBY := NEW.DOD_ORI_INSERTEDBY;
649 SET @vitDOD_ORI_CREATED := NEW.DOD_ORI_CREATED;
650 SET @vitDOD_ORI_CREATEDBY := NEW.DOD_ORI_CREATEDBY;
651
652 SET @vitDOD_LII_INSERTEDBY := NEW.DOD_LII_INSERTEDBY;
653 SET @vitDOD_LII_CREATEDBY := NEW.DOD_LII_CREATEDBY;
654 SET @vitDOD_LII_CREATED := NEW.DOD_LII_CREATED;
655
656 SET @vitDOD_MODIFIEDBY := NEW.DOD_MODIFIEDBY;
657 SET @vitDOD_UPDATEDBY := NEW.DOD_UPDATEDBY;
658 SET @vitDOD_MODIFIED := NEW.DOD_MODIFIED;
659
660 SET @vitDOD_LII_MODIFIEDBY := NEW.DOD_LII_MODIFIEDBY;
661 SET @vitDOD_LII_UPDATEDBY := NEW.DOD_LII_UPDATEDBY;
662 SET @vitDOD_LII_MODIFIED := NEW.DOD_LII_MODIFIED;
663
664 SET @vitDOD_ORI_MODIFIEDBY := NEW.DOD_ORI_MODIFIEDBY;
665 SET @vitDOD_ORI_UPDATEDBY := NEW.DOD_ORI_UPDATEDBY;
666 SET @vitDOD_ORI_MODIFIED := NEW.DOD_ORI_MODIFIED;
667
668 -- default values
669 IF (@vitDOD_KEY_PUBLICATION IS NULL OR (CHAR_LENGTH(TRIM(@vitDOD_KEY_PUBLICATION)) = 0)) THEN
670 SET @vitDOD_KEY_PUBLICATION := 'N';
671 END IF;
672
673 IF (@vitDOD_CLINICAL_ARTICLE IS NULL OR (CHAR_LENGTH(TRIM(@vitDOD_CLINICAL_ARTICLE)) = 0)) THEN
674 SET @vitDOD_CLINICAL_ARTICLE := 'N';
675 END IF;
676
677 IF (@vitDOD_LII_HIDDEN IS NULL OR (CHAR_LENGTH(TRIM(@vitDOD_LII_HIDDEN)) = 0)) THEN
678 SET @vitDOD_LII_HIDDEN := 'N';
679 END IF;
680 -- SET NEW.DOD_ORI_DUPE_OF_ORI_ID = NULL; -- Updating of NEW row is not allowed in after trigger
681
682 CALL CG$DOCUMENTDATA_ins(@vitDOD_ID, FALSE);
683END */$$
684
685
686DELIMITER ;
687
688/* Trigger structure for table `DOCUMENTDATA` */
689
690DELIMITER $$
691
692/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BUR_DOCUMENTDATA` */$$
693
694/*!50003 CREATE */ /*!50003 TRIGGER `CG$BUR_DOCUMENTDATA` BEFORE UPDATE ON `DOCUMENTDATA` FOR EACH ROW
695BEGIN
696 /* Tahmina Aziz : Work in Progress */
697 DECLARE vutDOD_ID INT(38);
698 DECLARE vutDOD_ORI_ID DECIMAL(38,0);
699 DECLARE vutDOD_LII_ID DECIMAL(38,0);
700 DECLARE vutDOD_DOCTYPE VARCHAR(2);
701 DECLARE vutDOD_TITLE TEXT;
702 DECLARE vutDOD_JOURNAL_NAME VARCHAR(255);
703 DECLARE vutDOD_PUBL_YEAR DECIMAL(4,0);
704 DECLARE vutDOD_AUTHORS TEXT;
705 DECLARE vutDOD_VOLUME VARCHAR(50);
706 DECLARE vutDOD_ISSUE VARCHAR(50);
707 DECLARE vutDOD_PAGEINFO VARCHAR(100);
708 DECLARE vutDOD_LANGUAGE VARCHAR(100);
709 DECLARE vutDOD_KEYWORDS TEXT;
710 DECLARE vutDOD_AFFILIATION TEXT;
711 DECLARE vutDOD_ABSTRACT TEXT;
712 DECLARE vutDOD_PMID DECIMAL(10,0);
713 DECLARE vutDOD_DOI VARCHAR(255);
714 DECLARE vutDOD_ISSN VARCHAR(10);
715 DECLARE vutDOD_MESH_TERMS TEXT;
716 DECLARE vutDOD_SUBSTANCES TEXT;
717 DECLARE vutDOD_PUB_TYPE VARCHAR(255);
718 DECLARE vutDOD_EDITOR_NAME VARCHAR(255);
719 DECLARE vutDOD_BOOKTITLE TEXT;
720 DECLARE vutDOD_PUBLISHER VARCHAR(255);
721 DECLARE vutDOD_ISBN VARCHAR(50);
722 DECLARE vutDOD_CONF_NAME VARCHAR(255);
723 DECLARE vutDOD_SPONSOR VARCHAR(100);
724 DECLARE vutDOD_PAT_APPL_DATE VARCHAR(50);
725 DECLARE vutDOD_PAT_APPL_NUM VARCHAR(50);
726 DECLARE vutDOD_PAT_PRIO_DATES TEXT;
727 DECLARE vutDOD_PAT_PRIO_NUM TEXT;
728 DECLARE vutDOD_PAT_PUBL_DATE VARCHAR(50);
729 DECLARE vutDOD_PAT_PUBL_NUM VARCHAR(50);
730 DECLARE vutDOD_PRODUCT VARCHAR(255);
731 DECLARE vutDOD_INDICATION TEXT;
732 DECLARE vutDOD_KEY_PUBLICATION VARCHAR(1);
733 DECLARE vutDOD_CLINICAL_ARTICLE VARCHAR(1);
734 DECLARE vutDOD_COMMENT TEXT;
735 DECLARE vutDOD_INSERTEDBY VARCHAR(50);
736 DECLARE vutDOD_UPDATEDBY VARCHAR(50);
737 DECLARE vutDOD_CREATED DATETIME;
738 DECLARE vutDOD_CREATEDBY VARCHAR(30);
739 DECLARE vutDOD_MODIFIED DATETIME;
740 DECLARE vutDOD_MODIFIEDBY VARCHAR(30);
741 DECLARE vutDOD_LINKTYPE VARCHAR(50);
742 DECLARE vutDOD_URL TEXT;
743 DECLARE vutCUST_COUNTRY VARCHAR(255);
744 DECLARE vutDOD_LII_HIDDEN VARCHAR(1);
745 DECLARE vutDOD_LII_COMMENT TEXT;
746 DECLARE vutDOD_LII_CREATEDBY VARCHAR(50);
747 DECLARE vutDOD_LII_MODIFIEDBY VARCHAR(50);
748 DECLARE vutDOD_LII_INSERTEDBY VARCHAR(50);
749 DECLARE vutDOD_LII_UPDATEDBY VARCHAR(50);
750 DECLARE vutDOD_LII_CREATED DATETIME;
751 DECLARE vutDOD_LII_MODIFIED DATETIME;
752 DECLARE vutDOD_ORI_DUPE_OF_ORI_ID DECIMAL(38,0);
753 DECLARE vutDOD_ORI_ORDERID VARCHAR(50);
754 DECLARE vutDOD_ORI_PDF_DELIVERY_DATE DATETIME;
755 DECLARE vutDOD_ORI_STATUS VARCHAR(1);
756 DECLARE vutDOD_ORI_COMMENT TEXT;
757 DECLARE vutDOD_ORI_URGENCY VARCHAR(1);
758 DECLARE vutDOD_ORI_MAILBODY TEXT;
759 DECLARE vutDOD_ORI_ACTPUBMED VARCHAR(1);
760 DECLARE vutDOD_ORI_CREATEDBY VARCHAR(50);
761 DECLARE vutDOD_ORI_INSERTEDBY VARCHAR(50);
762 DECLARE vutDOD_ORI_UPDATEDBY VARCHAR(50);
763 DECLARE vutDOD_ORI_MODIFIEDBY VARCHAR(50);
764 DECLARE vutDOD_ORI_CREATED DATETIME;
765 DECLARE vutDOD_ORI_MODIFIED DATETIME;
766 DECLARE vutDOD_DOI_FI_COL VARCHAR(255);
767 DECLARE vutDOD_ISBN_FI_COL VARCHAR(50);
768 DECLARE vutDOD_ISSN_FI_COL VARCHAR(10);
769 DECLARE vutDOD_ISSUE_FI_COL VARCHAR(50);
770 DECLARE vutDOD_LANGUAGE_FI_COL VARCHAR(100);
771 DECLARE vutDOD_ORI_ORDERID_FI_COL VARCHAR(50);
772 DECLARE vutDOD_PMID_FI_COL DECIMAL(10,0);
773 DECLARE vutDOD_PUBL_YEAR_FI_COL VARCHAR(4);
774 DECLARE vutROT_IDX1_COL DECIMAL(38,0);
775
776 DECLARE vutDOD_ID_upd INT DEFAULT FALSE;
777
778 SET @vutDOD_ID := OLD.DOD_ID;
779 -- To check if someone is trying to update primary key
780 SET @vutDOD_ID_upd := (NEW.DOD_ID IS NULL AND OLD.DOD_ID IS NOT NULL )
781 OR (NEW.DOD_ID IS NOT NULL AND OLD.DOD_ID IS NULL)
782 OR NOT(NEW.DOD_ID = OLD.DOD_ID);
783 SET @vutDOD_ORI_ID := NEW.DOD_ORI_ID;
784 SET @vutDOD_LII_ID := NEW.DOD_LII_ID;
785 SET @vutDOD_DOCTYPE := NEW.DOD_DOCTYPE;
786 SET @vutDOD_TITLE := NEW.DOD_TITLE;
787 SET @vutDOD_JOURNAL_NAME := NEW.DOD_JOURNAL_NAME;
788 SET @vutDOD_PUBL_YEAR := NEW.DOD_PUBL_YEAR;
789 SET @vutDOD_AUTHORS := NEW.DOD_AUTHORS;
790 SET @vutDOD_VOLUME := NEW.DOD_VOLUME;
791 SET @vutDOD_ISSUE := NEW.DOD_ISSUE;
792 SET @vutDOD_PAGEINFO := NEW.DOD_PAGEINFO;
793 SET @vutDOD_LANGUAGE := NEW.DOD_LANGUAGE;
794 SET @vutDOD_KEYWORDS := NEW.DOD_KEYWORDS;
795 SET @vutDOD_AFFILIATION := NEW.DOD_AFFILIATION;
796 SET @vutDOD_ABSTRACT := NEW.DOD_ABSTRACT;
797 SET @vutDOD_PMID := NEW.DOD_PMID;
798 SET @vutDOD_DOI := NEW.DOD_DOI;
799 SET @vutDOD_ISSN := NEW.DOD_ISSN;
800 SET @vutDOD_MESH_TERMS := NEW.DOD_MESH_TERMS;
801 SET @vutDOD_SUBSTANCES := NEW.DOD_SUBSTANCES;
802 SET @vutDOD_PUB_TYPE := NEW.DOD_PUB_TYPE;
803 SET @vutDOD_EDITOR_NAME := NEW.DOD_EDITOR_NAME;
804 SET @vutDOD_BOOKTITLE := NEW.DOD_BOOKTITLE;
805 SET @vutDOD_PUBLISHER := NEW.DOD_PUBLISHER;
806 SET @vutDOD_ISBN := NEW.DOD_ISBN;
807 SET @vutDOD_CONF_NAME := NEW.DOD_CONF_NAME;
808 SET @vutDOD_SPONSOR := NEW.DOD_SPONSOR;
809 SET @vutDOD_PAT_APPL_DATE := NEW.DOD_PAT_APPL_DATE;
810 SET @vutDOD_PAT_APPL_NUM := NEW.DOD_PAT_APPL_NUM;
811 SET @vutDOD_PAT_PRIO_DATES := NEW.DOD_PAT_PRIO_DATES;
812 SET @vutDOD_PAT_PRIO_NUM := NEW.DOD_PAT_PRIO_NUM;
813 SET @vutDOD_PAT_PUBL_DATE := NEW.DOD_PAT_PUBL_DATE;
814 SET @vutDOD_PAT_PUBL_NUM := NEW.DOD_PAT_PUBL_NUM;
815 SET @vutDOD_PRODUCT := NEW.DOD_PRODUCT;
816 SET @vutDOD_INDICATION := NEW.DOD_INDICATION;
817 SET @vutDOD_KEY_PUBLICATION := NEW.DOD_KEY_PUBLICATION;
818 SET @vutDOD_CLINICAL_ARTICLE := NEW.DOD_CLINICAL_ARTICLE;
819 SET @vutDOD_COMMENT := NEW.DOD_COMMENT;
820 SET @vutDOD_INSERTEDBY := NEW.DOD_INSERTEDBY;
821 SET @vutDOD_CREATED := NEW.DOD_CREATED;
822 SET @vutDOD_CREATEDBY := NEW.DOD_CREATEDBY;
823 SET @vutCUST_COUNTRY := NEW.CUST_COUNTRY;
824 SET @vutDOD_LINKTYPE := NEW.DOD_LINKTYPE;
825 SET @vutDOD_URL := NEW.DOD_URL;
826
827
828
829 /* MRR added */
830 SET @vutDOD_LII_COMMENT := NEW.DOD_LII_COMMENT;
831 SET @vutDOD_LII_HIDDEN := NEW.DOD_LII_HIDDEN;
832 SET @vutDOD_LII_CREATED := NEW.DOD_LII_CREATED;
833 SET @vutDOD_LII_INSERTEDBY := NEW.DOD_LII_INSERTEDBY;
834 SET @vutDOD_LII_CREATEDBY := NEW.DOD_LII_CREATEDBY;
835 /* MR */
836 SET @vutDOD_ORI_DUPE_OF_ORI_ID := NEW.DOD_ORI_DUPE_OF_ORI_ID;
837 SET @vutDOD_ORI_ORDERID := NEW.DOD_ORI_ORDERID;
838 SET @vutDOD_ORI_PDF_DELIVERY_DATE := NEW.DOD_ORI_PDF_DELIVERY_DATE;
839 SET @vutDOD_ORI_STATUS := NEW.DOD_ORI_STATUS;
840 SET @vutDOD_ORI_COMMENT := NEW.DOD_ORI_COMMENT;
841 SET @vutDOD_ORI_URGENCY := NEW.DOD_ORI_URGENCY;
842 SET @vutDOD_ORI_MAILBODY := NEW.DOD_ORI_MAILBODY;
843 SET @vutDOD_ORI_ACTPUBMED := NEW.DOD_ORI_ACTPUBMED;
844 SET @vutDOD_ORI_INSERTEDBY := NEW.DOD_ORI_INSERTEDBY;
845 SET @vutDOD_ORI_CREATED := NEW.DOD_ORI_CREATED;
846 SET @vutDOD_ORI_CREATEDBY := NEW.DOD_ORI_CREATEDBY;
847
848 /*
849 * Start: Following fields value are set by Trigger
850 */
851
852 IF (NEW.DOD_MODIFIEDBY IS NULL) THEN
853 SET NEW.DOD_MODIFIEDBY = USER();
854 END IF;
855
856 IF (NEW.DOD_UPDATEDBY IS NULL) THEN
857 SET NEW.DOD_UPDATEDBY = USER();
858 END IF;
859
860 SET NEW.DOD_MODIFIED = SYSDATE();
861 SET @vutDOD_MODIFIED := NEW.DOD_MODIFIED;
862
863 IF (NEW.DOD_ORI_MODIFIEDBY IS NULL) THEN
864 SET NEW.DOD_ORI_MODIFIEDBY = USER();
865 END IF;
866
867 IF (NEW.DOD_ORI_UPDATEDBY IS NULL) THEN
868 SET NEW.DOD_ORI_UPDATEDBY = USER();
869 END IF;
870
871 SET NEW.DOD_ORI_MODIFIED = SYSDATE();
872 SET @vutDOD_ORI_MODIFIED := NEW.DOD_ORI_MODIFIED;
873
874 -- Logic for DOD_LII update
875 IF (NEW.DOD_LII_ID IS NOT NULL AND NEW.DOD_ORI_STATUS = 'N') THEN
876 IF (NEW.DOD_LII_INSERTEDBY IS NULL) THEN
877 SET NEW.DOD_LII_INSERTEDBY := USER();
878 END IF;
879
880 IF (NEW.DOD_LII_CREATEDBY IS NULL) THEN
881 SET NEW.DOD_LII_CREATEDBY = USER();
882 END IF;
883
884 SET NEW.DOD_LII_CREATED = SYSDATE();
885
886
887 ELSEIF (NEW.DOD_LII_ID IS NOT NULL AND NEW.DOD_ORI_STATUS != 'N') THEN
888
889 -- if CREATEDBY/INSERTEDBY/CREATED values are empty
890 IF (NEW.DOD_LII_INSERTEDBY IS NULL) THEN
891 SET NEW.DOD_LII_INSERTEDBY := USER();
892 END IF;
893
894 IF (NEW.DOD_LII_CREATEDBY IS NULL) THEN
895 SET NEW.DOD_LII_CREATEDBY = USER();
896 END IF;
897 IF (NEW.DOD_LII_CREATED IS NULL) THEN
898 SET NEW.DOD_LII_CREATED = SYSDATE();
899 END IF;
900
901 -- when DOD_ORI_STATUS is changed along with LII_ID
902 IF (NEW.DOD_LII_MODIFIEDBY IS NULL) THEN
903 SET NEW.DOD_LII_MODIFIEDBY = USER();
904 END IF;
905
906 IF (NEW.DOD_LII_UPDATEDBY IS NULL) THEN
907 SET NEW.DOD_LII_UPDATEDBY = USER();
908 END IF;
909
910 SET NEW.DOD_LII_MODIFIED = SYSDATE();
911 END IF;
912
913 /*SET NEW.DOD_DOI_FI_COL = LOWER(NEW.DOD_DOI);
914 SET NEW.DOD_ISBN_FI_COL = LOWER(NEW.DOD_ISBN);
915 SET NEW.DOD_ISSN_FI_COL = LOWER(NEW.DOD_ISSN);
916 SET NEW.DOD_ISSUE_FI_COL = LOWER(NEW.DOD_ISSUE);
917 SET NEW.DOD_LANGUAGE_FI_COL = LOWER(NEW.DOD_LANGUAGE);
918 SET NEW.DOD_ORI_ORDERID_FI_COL = NEW.DOD_ORI_ORDERID;
919 SET NEW.DOD_PMID_FI_COL = NEW.DOD_PMID;
920 SET NEW.DOD_PUBL_YEAR_FI_COL = NEW.DOD_PUBL_YEAR;
921 SET NEW.ROT_IDX1_COL = IFNULL(NEW.DOD_LII_ID,NEW.DOD_ORI_ID);*/
922
923 /*
924 * Start: Following fields value are set by Trigger
925 */
926
927 SET @vutDOD_MODIFIEDBY := NEW.DOD_MODIFIEDBY;
928 SET @vutDOD_UPDATEDBY := NEW.DOD_UPDATEDBY;
929 SET @vutDOD_ORI_MODIFIEDBY := NEW.DOD_ORI_MODIFIEDBY;
930 SET @vutDOD_ORI_UPDATEDBY := NEW.DOD_ORI_UPDATEDBY;
931
932 SET @vutDOD_LII_INSERTEDBY := NEW.DOD_LII_INSERTEDBY;
933 SET @vutDOD_LII_CREATEDBY := NEW.DOD_LII_CREATEDBY;
934 SET @vutDOD_LII_CREATED := NEW.DOD_LII_CREATED;
935
936 SET @vutDOD_LII_MODIFIEDBY := NEW.DOD_LII_MODIFIEDBY;
937 SET @vutDOD_LII_UPDATEDBY := NEW.DOD_LII_UPDATEDBY;
938 SET @vutDOD_LII_MODIFIED := NEW.DOD_LII_MODIFIED;
939
940
941 CALL CG$DOCUMENTDATA_validate_domain(@vutDOD_DOCTYPE);
942 CALL CG$DOCUMENTDATA_upd(@vutDOD_ID, FALSE);
943
944
945END */$$
946
947
948DELIMITER ;
949
950/* Trigger structure for table `DOCUMENTDATA` */
951
952DELIMITER $$
953
954/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$ADR_DOCUMENTDATA` */$$
955
956/*!50003 CREATE */ /*!50003 TRIGGER `CG$ADR_DOCUMENTDATA` AFTER DELETE ON `DOCUMENTDATA` FOR EACH ROW
957BEGIN
958 -- TAT 180220 Work in Progress
959 DECLARE vDOD_ID INT(38);
960
961 DECLARE vdtDOD_ID INT(38);
962 DECLARE vdtDOD_ORI_ID DECIMAL(38,0);
963 DECLARE vdtDOD_LII_ID DECIMAL(38,0);
964 DECLARE vdtDOD_DOCTYPE VARCHAR(2);
965 DECLARE vdtDOD_TITLE TEXT;
966 DECLARE vdtDOD_JOURNAL_NAME VARCHAR(255);
967 DECLARE vdtDOD_PUBL_YEAR DECIMAL(4,0);
968 DECLARE vdtDOD_AUTHORS TEXT;
969 DECLARE vdtDOD_VOLUME VARCHAR(50);
970 DECLARE vdtDOD_ISSUE VARCHAR(50);
971 DECLARE vdtDOD_PAGEINFO VARCHAR(100);
972 DECLARE vdtDOD_LANGUAGE VARCHAR(100);
973 DECLARE vdtDOD_KEYWORDS TEXT;
974 DECLARE vdtDOD_AFFILIATION TEXT;
975 DECLARE vdtDOD_ABSTRACT TEXT;
976 DECLARE vdtDOD_PMID DECIMAL(10,0);
977 DECLARE vdtDOD_DOI VARCHAR(255);
978 DECLARE vdtDOD_ISSN VARCHAR(10);
979 DECLARE vdtDOD_MESH_TERMS TEXT;
980 DECLARE vdtDOD_SUBSTANCES TEXT;
981 DECLARE vdtDOD_PUB_TYPE VARCHAR(255);
982 DECLARE vdtDOD_EDITOR_NAME VARCHAR(255);
983 DECLARE vdtDOD_BOOKTITLE TEXT;
984 DECLARE vdtDOD_PUBLISHER VARCHAR(255);
985 DECLARE vdtDOD_ISBN VARCHAR(50);
986 DECLARE vdtDOD_CONF_NAME VARCHAR(255);
987 DECLARE vdtDOD_SPONSOR VARCHAR(100);
988 DECLARE vdtDOD_PAT_APPL_DATE VARCHAR(50);
989 DECLARE vdtDOD_PAT_APPL_NUM VARCHAR(50);
990 DECLARE vdtDOD_PAT_PRIO_DATES TEXT;
991 DECLARE vdtDOD_PAT_PRIO_NUM TEXT;
992 DECLARE vdtDOD_PAT_PUBL_DATE VARCHAR(50);
993 DECLARE vdtDOD_PAT_PUBL_NUM VARCHAR(50);
994 DECLARE vdtDOD_PRODUCT VARCHAR(255);
995 DECLARE vdtDOD_INDICATION TEXT;
996 DECLARE vdtDOD_KEY_PUBLICATION VARCHAR(1);
997 DECLARE vdtDOD_CLINICAL_ARTICLE VARCHAR(1);
998 DECLARE vdtDOD_COMMENT TEXT;
999 DECLARE vdtDOD_INSERTEDBY VARCHAR(50);
1000 DECLARE vdtDOD_UPDATEDBY VARCHAR(50);
1001 DECLARE vdtDOD_CREATED DATETIME;
1002 DECLARE vdtDOD_CREATEDBY VARCHAR(30);
1003 DECLARE vdtDOD_MODIFIED DATETIME;
1004 DECLARE vdtDOD_MODIFIEDBY VARCHAR(30);
1005 DECLARE vdtDOD_LINKTYPE VARCHAR(50);
1006 DECLARE vdtDOD_URL TEXT;
1007 DECLARE vdtCUST_COUNTRY VARCHAR(255);
1008 DECLARE vdtDOD_LII_HIDDEN VARCHAR(1);
1009 DECLARE vdtDOD_LII_COMMENT TEXT;
1010 DECLARE vdtDOD_LII_CREATEDBY VARCHAR(50);
1011 DECLARE vdtDOD_LII_MODIFIEDBY VARCHAR(50);
1012 DECLARE vdtDOD_LII_INSERTEDBY VARCHAR(50);
1013 DECLARE vdtDOD_LII_UPDATEDBY VARCHAR(50);
1014 DECLARE vdtDOD_LII_CREATED DATETIME;
1015 DECLARE vdtDOD_LII_MODIFIED DATETIME;
1016 DECLARE vdtDOD_ORI_DUPE_OF_ORI_ID DECIMAL(38,0);
1017 DECLARE vdtDOD_ORI_ORDERID VARCHAR(50);
1018 DECLARE vdtDOD_ORI_PDF_DELIVERY_DATE DATETIME;
1019 DECLARE vdtDOD_ORI_STATUS VARCHAR(1);
1020 DECLARE vdtDOD_ORI_COMMENT TEXT;
1021 DECLARE vdtDOD_ORI_URGENCY VARCHAR(1);
1022 DECLARE vdtDOD_ORI_MAILBODY TEXT;
1023 DECLARE vdtDOD_ORI_ACTPUBMED VARCHAR(1);
1024 DECLARE vdtDOD_ORI_CREATEDBY VARCHAR(50);
1025 DECLARE vdtDOD_ORI_INSERTEDBY VARCHAR(50);
1026 DECLARE vdtDOD_ORI_UPDATEDBY VARCHAR(50);
1027 DECLARE vdtDOD_ORI_MODIFIEDBY VARCHAR(50);
1028 DECLARE vdtDOD_ORI_CREATED DATETIME;
1029 DECLARE vdtDOD_ORI_MODIFIED DATETIME;
1030
1031 SET @vdtDOD_ID := OLD.DOD_ID;
1032 SET @vdtDOD_ORI_ID := OLD.DOD_ORI_ID;
1033 SET @vdtDOD_LII_ID := OLD.DOD_LII_ID;
1034 SET @vdtDOD_DOCTYPE := OLD.DOD_DOCTYPE;
1035 SET @vdtDOD_TITLE := OLD.DOD_TITLE;
1036 SET @vdtDOD_JOURNAL_NAME := OLD.DOD_JOURNAL_NAME;
1037 SET @vdtDOD_PUBL_YEAR := OLD.DOD_PUBL_YEAR;
1038 SET @vdtDOD_AUTHORS := OLD.DOD_AUTHORS;
1039 SET @vdtDOD_VOLUME := OLD.DOD_VOLUME;
1040 SET @vdtDOD_ISSUE := OLD.DOD_ISSUE;
1041 SET @vdtDOD_PAGEINFO := OLD.DOD_PAGEINFO;
1042 SET @vdtDOD_LANGUAGE := OLD.DOD_LANGUAGE;
1043 SET @vdtDOD_KEYWORDS := OLD.DOD_KEYWORDS;
1044 SET @vdtDOD_AFFILIATION := OLD.DOD_AFFILIATION;
1045 SET @vdtDOD_ABSTRACT := OLD.DOD_ABSTRACT;
1046 SET @vdtDOD_PMID := OLD.DOD_PMID;
1047 SET @vdtDOD_DOI := OLD.DOD_DOI;
1048 SET @vdtDOD_ISSN := OLD.DOD_ISSN;
1049 SET @vdtDOD_MESH_TERMS := OLD.DOD_MESH_TERMS;
1050 SET @vdtDOD_SUBSTANCES := OLD.DOD_SUBSTANCES;
1051 SET @vdtDOD_PUB_TYPE := OLD.DOD_PUB_TYPE;
1052 SET @vdtDOD_EDITOR_NAME := OLD.DOD_EDITOR_NAME;
1053 SET @vdtDOD_BOOKTITLE := OLD.DOD_BOOKTITLE;
1054 SET @vdtDOD_PUBLISHER := OLD.DOD_PUBLISHER;
1055 SET @vdtDOD_ISBN := OLD.DOD_ISBN;
1056 SET @vdtDOD_CONF_NAME := OLD.DOD_CONF_NAME;
1057 SET @vdtDOD_SPONSOR := OLD.DOD_SPONSOR;
1058 SET @vdtDOD_PAT_APPL_DATE := OLD.DOD_PAT_APPL_DATE;
1059 SET @vdtDOD_PAT_APPL_NUM := OLD.DOD_PAT_APPL_NUM;
1060 SET @vdtDOD_PAT_PRIO_DATES := OLD.DOD_PAT_PRIO_DATES;
1061 SET @vdtDOD_PAT_PRIO_NUM := OLD.DOD_PAT_PRIO_NUM;
1062 SET @vdtDOD_PAT_PUBL_DATE := OLD.DOD_PAT_PUBL_DATE;
1063 SET @vdtDOD_PAT_PUBL_NUM := OLD.DOD_PAT_PUBL_NUM;
1064 SET @vdtDOD_PRODUCT := OLD.DOD_PRODUCT;
1065 SET @vdtDOD_INDICATION := OLD.DOD_INDICATION;
1066 SET @vdtDOD_KEY_PUBLICATION := OLD.DOD_KEY_PUBLICATION;
1067 SET @vdtDOD_CLINICAL_ARTICLE := OLD.DOD_CLINICAL_ARTICLE;
1068 SET @vdtDOD_COMMENT := OLD.DOD_COMMENT;
1069 SET @vdtDOD_INSERTEDBY := OLD.DOD_INSERTEDBY;
1070 SET @vdtDOD_CREATED := OLD.DOD_CREATED;
1071 SET @vdtDOD_CREATEDBY := OLD.DOD_CREATEDBY;
1072 SET @vdtCUST_COUNTRY := OLD.CUST_COUNTRY;
1073 SET @vdtDOD_LINKTYPE := OLD.DOD_LINKTYPE;
1074 SET @vdtDOD_URL := OLD.DOD_URL;
1075 SET @vdtDOD_MODIFIEDBY = OLD.DOD_MODIFIEDBY;
1076 SET @vdtDOD_UPDATEDBY = OLD.DOD_UPDATEDBY;
1077
1078
1079
1080 /* MRR added */
1081 SET @vdtDOD_LII_COMMENT := OLD.DOD_LII_COMMENT;
1082 SET @vdtDOD_LII_HIDDEN := OLD.DOD_LII_HIDDEN;
1083 SET @vdtDOD_LII_CREATED := OLD.DOD_LII_CREATED;
1084 SET @vdtDOD_LII_INSERTEDBY := OLD.DOD_LII_INSERTEDBY;
1085 SET @vdtDOD_LII_CREATEDBY := OLD.DOD_LII_CREATEDBY;
1086 /* MR */
1087 SET @vdtDOD_ORI_DUPE_OF_ORI_ID := OLD.DOD_ORI_DUPE_OF_ORI_ID;
1088 SET @vdtDOD_ORI_ORDERID := OLD.DOD_ORI_ORDERID;
1089 SET @vdtDOD_ORI_PDF_DELIVERY_DATE := OLD.DOD_ORI_PDF_DELIVERY_DATE;
1090 SET @vdtDOD_ORI_STATUS := OLD.DOD_ORI_STATUS;
1091 SET @vdtDOD_ORI_COMMENT := OLD.DOD_ORI_COMMENT;
1092 SET @vdtDOD_ORI_URGENCY := OLD.DOD_ORI_URGENCY;
1093 SET @vdtDOD_ORI_MAILBODY := OLD.DOD_ORI_MAILBODY;
1094 SET @vdtDOD_ORI_ACTPUBMED := OLD.DOD_ORI_ACTPUBMED;
1095 SET @vdtDOD_ORI_INSERTEDBY := OLD.DOD_ORI_INSERTEDBY;
1096 SET @vdtDOD_ORI_CREATED := OLD.DOD_ORI_CREATED;
1097 SET @vdtDOD_ORI_CREATEDBY := OLD.DOD_ORI_CREATEDBY;
1098 SET @vdtDOD_ORI_MODIFIEDBY = OLD.DOD_ORI_MODIFIEDBY;
1099 SET @vdtDOD_ORI_UPDATEDBY = OLD.DOD_ORI_UPDATEDBY;
1100
1101
1102 /*
1103 * Start: Following fields value are set by Trigger
1104 */
1105 SET @vdtDOD_MODIFIED := SYSDATE();
1106 SET @vdtDOD_ORI_MODIFIED := SYSDATE();
1107
1108 -- Logic for DOD_LII update
1109
1110 IF (OLD.DOD_LII_ID IS NOT NULL AND OLD.DOD_ORI_STATUS != 'N') THEN
1111 SET @vdtDOD_LII_MODIFIEDBY = OLD.DOD_LII_MODIFIEDBY;
1112 SET @vdtDOD_LII_UPDATEDBY = OLD.DOD_LII_UPDATEDBY;
1113 SET @vdtDOD_LII_MODIFIED = SYSDATE();
1114 END IF;
1115
1116
1117 SELECT OLD.DOD_ID INTO vDOD_ID;
1118
1119 CALL CG$DOCUMENTDATA_del(vDOD_ID,FALSE);
1120END */$$
1121
1122
1123DELIMITER ;
1124
1125/* Trigger structure for table `DOCUMENTS` */
1126
1127DELIMITER $$
1128
1129/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BIR_DOCUMENTS` */$$
1130
1131/*!50003 CREATE */ /*!50003 TRIGGER `CG$BIR_DOCUMENTS` BEFORE INSERT ON `DOCUMENTS` FOR EACH ROW BEGIN
1132 -- AR 180215 : Work in Progress
1133 DECLARE vitDOC_ID int(38);
1134 DECLARE vitDOC_LII_ID decimal(38,0);
1135 DECLARE vitDOC_FILENAME varchar(255);
1136 DECLARE vitDOC_MASTERDOC varchar(1);
1137 DECLARE vitDOC_COMMENT text;
1138 DECLARE vitDOC_MIMETYPE varchar(155);
1139 DECLARE vitDOC_INSERTEDBY varchar(50);
1140 DECLARE vitDOC_UPDATEDBY varchar(50);
1141 DECLARE vitDOC_CREATED datetime(6);
1142 DECLARE vitDOC_CREATEDBY varchar(30);
1143 DECLARE vitDOC_MODIFIED datetime(6);
1144 DECLARE vitDOC_MODIFIEDBY varchar(30);
1145 DECLARE vitDOC_TYPE varchar(10);
1146
1147 SET @vitDOC_ID = NEW.DOC_ID;
1148 SET @vitDOC_LII_ID = NEW.DOC_LII_ID;
1149 SET @vitDOC_FILENAME = NEW.DOC_FILENAME;
1150 SET @vitDOC_MASTERDOC = NEW.DOC_MASTERDOC;
1151 SET @vitDOC_COMMENT = NEW.DOC_COMMENT;
1152 SET @vitDOC_MIMETYPE = NEW.DOC_MIMETYPE;
1153 SET @vitDOC_INSERTEDBY = NEW.DOC_INSERTEDBY;
1154 SET @vitDOC_UPDATEDBY = NEW.DOC_UPDATEDBY;
1155 SET @vitDOC_CREATED = NEW.DOC_CREATED;
1156 SET @vitDOC_CREATEDBY = NEW.DOC_CREATEDBY;
1157 SET @vitDOC_MODIFIED = NEW.DOC_MODIFIED;
1158 SET @vitDOC_MODIFIEDBY = NEW.DOC_MODIFIEDBY;
1159 SET @vitDOC_TYPE = NEW.DOC_TYPE;
1160
1161 /*
1162 * Start: Following fields value are set by Trigger
1163 */
1164 IF(NEW.DOC_INSERTEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.DOC_INSERTEDBY)) = 0)) THEN
1165 SET NEW.DOC_INSERTEDBY = USER();
1166 END IF;
1167
1168 IF(NEW.DOC_CREATEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.DOC_CREATEDBY)) = 0)) THEN
1169 SET NEW.DOC_CREATEDBY = USER();
1170 END IF;
1171 SET NEW.DOC_CREATED = SYSDATE();
1172
1173 IF(NEW.DOC_MODIFIEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.DOC_MODIFIEDBY)) = 0)) THEN
1174 SET NEW.DOC_MODIFIEDBY = USER();
1175 END IF;
1176 SET NEW.DOC_MODIFIED = SYSDATE();
1177
1178 /*
1179 * Following logic is just a copy from Existing Oracle Trigger
1180 */
1181 IF(NEW.DOC_CREATED IS NULL) THEN
1182 SET @vitDOC_CREATED = FALSE;
1183 ELSE
1184 SET @vitDOC_CREATED = TRUE;
1185 END IF;
1186
1187 IF(NEW.DOC_CREATEDBY IS NULL) THEN
1188 SET @vitDOC_CREATEDBY = FALSE;
1189 ELSE
1190 SET @vitDOC_CREATEDBY = TRUE;
1191 END IF;
1192
1193 IF(NEW.DOC_MODIFIED IS NULL) THEN
1194 SET @vitDOC_MODIFIED = FALSE;
1195 ELSE
1196 SET @vitDOC_MODIFIED = TRUE;
1197 END IF;
1198
1199 IF(NEW.DOC_MODIFIEDBY IS NULL) THEN
1200 SET @vitDOC_MODIFIEDBY = FALSE;
1201 ELSE
1202 SET @vitDOC_MODIFIEDBY = TRUE;
1203 END IF;
1204
1205 CALL CG$DOCUMENTS_ins(@vitDOC_ID,FALSE);
1206
1207END */$$
1208
1209
1210DELIMITER ;
1211
1212/* Trigger structure for table `DOCUMENTS` */
1213
1214DELIMITER $$
1215
1216/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BUR_DOCUMENTS` */$$
1217
1218/*!50003 CREATE */ /*!50003 TRIGGER `CG$BUR_DOCUMENTS` BEFORE UPDATE ON `DOCUMENTS` FOR EACH ROW BEGIN
1219 -- AR 180215 : Work in Progress
1220 DECLARE vutDOC_ID INT(38);
1221 DECLARE vutDOC_LII_ID DECIMAL(38,0);
1222 DECLARE vutDOC_FILENAME VARCHAR(255);
1223 DECLARE vutDOC_MASTERDOC VARCHAR(1);
1224 DECLARE vutDOC_COMMENT TEXT;
1225 DECLARE vutDOC_MIMETYPE VARCHAR(155);
1226 DECLARE vutDOC_INSERTEDBY VARCHAR(50);
1227 DECLARE vutDOC_UPDATEDBY VARCHAR(50);
1228 DECLARE vutDOC_CREATED DATETIME(6);
1229 DECLARE vutDOC_CREATEDBY VARCHAR(30);
1230 DECLARE vutDOC_MODIFIED DATETIME(6);
1231 DECLARE vutDOC_MODIFIEDBY VARCHAR(30);
1232 DECLARE vutDOC_TYPE VARCHAR(10);
1233
1234 SET @vutDOC_ID = OLD.DOC_ID;
1235 SET @vutDOC_LII_ID = NEW.DOC_LII_ID;
1236 SET @vutDOC_FILENAME = NEW.DOC_FILENAME;
1237 SET @vutDOC_MASTERDOC = NEW.DOC_MASTERDOC;
1238 SET @vutDOC_COMMENT = NEW.DOC_COMMENT;
1239 SET @vutDOC_MIMETYPE = NEW.DOC_MIMETYPE;
1240 SET @vutDOC_INSERTEDBY = NEW.DOC_INSERTEDBY;
1241 SET @vutDOC_UPDATEDBY = NEW.DOC_UPDATEDBY;
1242 SET @vutDOC_CREATED = NEW.DOC_CREATED;
1243 SET @vutDOC_CREATEDBY = NEW.DOC_CREATEDBY;
1244 SET @vutDOC_MODIFIED = NEW.DOC_MODIFIED;
1245 SET @vutDOC_MODIFIEDBY = NEW.DOC_MODIFIEDBY;
1246 SET @vutDOC_TYPE = NEW.DOC_TYPE;
1247
1248 /*
1249 * Start: Following fields value are set by Trigger
1250 */
1251
1252 IF(NEW.DOC_UPDATEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.DOC_UPDATEDBY)) = 0)) THEN
1253 SET NEW.DOC_UPDATEDBY = USER();
1254 END IF;
1255
1256 IF(NEW.DOC_MODIFIEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.DOC_MODIFIEDBY)) = 0)) THEN
1257 SET NEW.DOC_MODIFIEDBY = USER();
1258 END IF;
1259 SET NEW.DOC_MODIFIED = SYSDATE();
1260
1261
1262 /*
1263 * Start: Following fields value are set by Trigger
1264 */
1265 SET @vutDOC_UPDATEDBY = NEW.DOC_UPDATEDBY;
1266 SET @vutDOC_MODIFIED = NEW.DOC_MODIFIED;
1267 SET @vutDOC_MODIFIEDBY = NEW.DOC_MODIFIEDBY;
1268
1269
1270 CALL CG$DOCUMENTS_upd(@vutDOC_ID,FALSE);
1271
1272END */$$
1273
1274
1275DELIMITER ;
1276
1277/* Trigger structure for table `DOCUMENTS` */
1278
1279DELIMITER $$
1280
1281/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$ADR_DOCUMENTS` */$$
1282
1283/*!50003 CREATE */ /*!50003 TRIGGER `CG$ADR_DOCUMENTS` AFTER DELETE ON `DOCUMENTS` FOR EACH ROW BEGIN
1284 -- AR 180215 : Work in Progress
1285 DECLARE vDOC_ID INT(38);
1286
1287 SELECT OLD.DOC_ID INTO vDOC_ID;
1288
1289 CALL CG$DOCUMENTS_DEL(vDOC_ID,FALSE);
1290
1291END */$$
1292
1293
1294DELIMITER ;
1295
1296/* Trigger structure for table `JOURNALS` */
1297
1298DELIMITER $$
1299
1300/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BIR_JOURNALS` */$$
1301
1302/*!50003 CREATE */ /*!50003 TRIGGER `CG$BIR_JOURNALS` BEFORE INSERT ON `JOURNALS` FOR EACH ROW BEGIN
1303 -- AR 180215 : Work in Progress
1304 DECLARE vitJRN_ID INT(38);
1305 DECLARE vitJRN_NAME VARCHAR(255);
1306 DECLARE vitJRN_ISSN VARCHAR(10);
1307 DECLARE vitJRN_PUBLISHER VARCHAR(100);
1308 DECLARE vitJRN_EMBARGO DECIMAL(38,0);
1309 DECLARE vitJRN_SUBSCRIPTION VARCHAR(255);
1310 DECLARE vitJRN_SUB_TYPE VARCHAR(50);
1311 DECLARE vitJRN_SUB_LOCATION VARCHAR(255);
1312 DECLARE vitJRN_URL TEXT;
1313 DECLARE vitJRN_INSERTEDBY VARCHAR(50);
1314 DECLARE vitJRN_UPDATEDBY VARCHAR(50);
1315 DECLARE vitJRN_CREATED DATETIME(6);
1316 DECLARE vitJRN_CREATEDBY VARCHAR(30);
1317 DECLARE vitJRN_MODIFIED DATETIME(6);
1318 DECLARE vitJRN_MODIFIEDBY VARCHAR(30);
1319
1320 SET @vitJRN_ID = NEW.JRN_ID;
1321 SET @vitJRN_NAME = NEW.JRN_NAME;
1322 SET @vitJRN_ISSN = NEW.JRN_ISSN;
1323 SET @vitJRN_PUBLISHER = NEW.JRN_PUBLISHER;
1324 SET @vitJRN_EMBARGO = NEW.JRN_EMBARGO;
1325 SET @vitJRN_SUBSCRIPTION = NEW.JRN_SUBSCRIPTION;
1326 SET @vitJRN_SUB_TYPE = NEW.JRN_SUB_TYPE;
1327 SET @vitJRN_SUB_LOCATION = NEW.JRN_SUB_LOCATION;
1328 SET @vitJRN_URL = NEW.JRN_URL;
1329 SET @vitJRN_INSERTEDBY = NEW.JRN_INSERTEDBY;
1330 SET @vitJRN_UPDATEDBY = NEW.JRN_UPDATEDBY;
1331 SET @vitJRN_CREATED = NEW.JRN_CREATED;
1332 SET @vitJRN_CREATEDBY = NEW.JRN_CREATEDBY;
1333 SET @vitJRN_MODIFIED = NEW.JRN_MODIFIED;
1334 SET @vitJRN_MODIFIEDBY = NEW.JRN_MODIFIEDBY;
1335
1336 /*
1337 * Start: Following fields value are set by Trigger
1338 */
1339 IF(NEW.JRN_INSERTEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.JRN_INSERTEDBY)) = 0)) THEN
1340 SET NEW.JRN_INSERTEDBY = USER();
1341 END IF;
1342
1343 IF(NEW.JRN_CREATEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.JRN_CREATEDBY)) = 0)) THEN
1344 SET NEW.JRN_CREATEDBY = USER();
1345 END IF;
1346 SET NEW.JRN_CREATED = SYSDATE();
1347
1348 IF(NEW.JRN_MODIFIEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.JRN_MODIFIEDBY)) = 0)) THEN
1349 SET NEW.JRN_MODIFIEDBY = USER();
1350 END IF;
1351 SET NEW.JRN_MODIFIED = SYSDATE();
1352
1353 /*
1354 * Start: Following fields value are set by Trigger
1355 */
1356 SET @vitJRN_INSERTEDBY = NEW.JRN_INSERTEDBY;
1357 SET @vitJRN_CREATED = NEW.JRN_CREATED;
1358 SET @vitJRN_CREATEDBY = NEW.JRN_CREATEDBY;
1359 SET @vitJRN_MODIFIED = NEW.JRN_MODIFIED;
1360 SET @vitJRN_MODIFIEDBY = NEW.JRN_MODIFIEDBY;
1361
1362 /*
1363 * Following logic is just a copy from Existing Oracle Trigger
1364 */
1365 IF(NEW.JRN_CREATED IS NULL) THEN
1366 SET @vitJRN_CREATED = FALSE;
1367 ELSE
1368 SET @vitJRN_CREATED = TRUE;
1369 END IF;
1370
1371 IF(NEW.JRN_CREATEDBY IS NULL) THEN
1372 SET @vitJRN_CREATEDBY = FALSE;
1373 ELSE
1374 SET @vitJRN_CREATEDBY = TRUE;
1375 END IF;
1376
1377 IF(NEW.JRN_MODIFIED IS NULL) THEN
1378 SET @vitJRN_MODIFIED = FALSE;
1379 ELSE
1380 SET @vitJRN_MODIFIED = TRUE;
1381 END IF;
1382
1383 IF(NEW.JRN_MODIFIEDBY IS NULL) THEN
1384 SET @vitJRN_MODIFIEDBY = FALSE;
1385 ELSE
1386 SET @vitJRN_MODIFIEDBY = TRUE;
1387 END IF;
1388
1389 CALL CG$JOURNALS_ins(@vitJRN_ID,FALSE);
1390
1391END */$$
1392
1393
1394DELIMITER ;
1395
1396/* Trigger structure for table `JOURNALS` */
1397
1398DELIMITER $$
1399
1400/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BUR_JOURNALS` */$$
1401
1402/*!50003 CREATE */ /*!50003 TRIGGER `CG$BUR_JOURNALS` BEFORE UPDATE ON `JOURNALS` FOR EACH ROW BEGIN
1403 -- AR 180215 : Work in Progress
1404 DECLARE vutJRN_ID int(38);
1405 DECLARE vutJRN_NAME varchar(255);
1406 DECLARE vutJRN_ISSN varchar(10);
1407 DECLARE vutJRN_PUBLISHER varchar(100);
1408 DECLARE vutJRN_EMBARGO decimal(38,0);
1409 DECLARE vutJRN_SUBSCRIPTION varchar(255);
1410 DECLARE vutJRN_SUB_TYPE varchar(50);
1411 DECLARE vutJRN_SUB_LOCATION varchar(255);
1412 DECLARE vutJRN_URL text;
1413 DECLARE vutJRN_INSERTEDBY varchar(50);
1414 DECLARE vutJRN_UPDATEDBY varchar(50);
1415 DECLARE vutJRN_CREATED datetime(6);
1416 DECLARE vutJRN_CREATEDBY varchar(30);
1417 DECLARE vutJRN_MODIFIED datetime(6);
1418 DECLARE vutJRN_MODIFIEDBY varchar(30);
1419
1420 SET @vutJRN_ID = OLD.JRN_ID;
1421 SET @vutJRN_NAME = NEW.JRN_NAME;
1422 SET @vutJRN_ISSN = NEW.JRN_ISSN;
1423 SET @vutJRN_PUBLISHER = NEW.JRN_PUBLISHER;
1424 SET @vutJRN_EMBARGO = NEW.JRN_EMBARGO;
1425 SET @vutJRN_SUBSCRIPTION = NEW.JRN_SUBSCRIPTION;
1426 SET @vutJRN_SUB_TYPE = NEW.JRN_SUB_TYPE;
1427 SET @vutJRN_SUB_LOCATION = NEW.JRN_SUB_LOCATION;
1428 SET @vutJRN_URL = NEW.JRN_URL;
1429 SET @vutJRN_INSERTEDBY = NEW.JRN_INSERTEDBY;
1430 SET @vutJRN_UPDATEDBY = NEW.JRN_UPDATEDBY;
1431 SET @vutJRN_CREATED = NEW.JRN_CREATED;
1432 SET @vutJRN_CREATEDBY = NEW.JRN_CREATEDBY;
1433 SET @vutJRN_MODIFIED = NEW.JRN_MODIFIED;
1434 SET @vutJRN_MODIFIEDBY = NEW.JRN_MODIFIEDBY;
1435
1436 /*
1437 * Start: Following fields value are set by Trigger
1438 */
1439
1440 IF(NEW.JRN_UPDATEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.JRN_UPDATEDBY)) = 0)) THEN
1441 SET NEW.JRN_UPDATEDBY = USER();
1442 END IF;
1443
1444 IF(NEW.JRN_MODIFIEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.JRN_MODIFIEDBY)) = 0)) THEN
1445 SET NEW.JRN_MODIFIEDBY = USER();
1446 END IF;
1447 SET NEW.JRN_MODIFIED = SYSDATE();
1448
1449 /*
1450 * Start: Following fields value are set by Trigger
1451 */
1452 SET @vutJRN_UPDATEDBY = NEW.JRN_UPDATEDBY;
1453 SET @vutJRN_MODIFIED = NEW.JRN_MODIFIED;
1454 SET @vutJRN_MODIFIEDBY = NEW.JRN_MODIFIEDBY;
1455
1456 CALL CG$JOURNALS_upd(@vutJRN_ID,FALSE);
1457
1458END */$$
1459
1460
1461DELIMITER ;
1462
1463/* Trigger structure for table `JOURNALS` */
1464
1465DELIMITER $$
1466
1467/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$ADR_JOURNALS` */$$
1468
1469/*!50003 CREATE */ /*!50003 TRIGGER `CG$ADR_JOURNALS` AFTER DELETE ON `JOURNALS` FOR EACH ROW BEGIN
1470 -- AR 180215 : Work in Progress
1471 DECLARE vJRN_ID INT(38);
1472
1473 SELECT OLD.JRN_ID INTO vJRN_ID;
1474
1475 CALL CG$JOURNALS_DEL(vJRN_ID,FALSE);
1476
1477END */$$
1478
1479
1480DELIMITER ;
1481
1482/* Trigger structure for table `JOURNAL_SYNC` */
1483
1484DELIMITER $$
1485
1486/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `DI$AIR_JOURNAL_SYNC` */$$
1487
1488/*!50003 CREATE */ /*!50003 TRIGGER `DI$AIR_JOURNAL_SYNC` AFTER INSERT ON `JOURNAL_SYNC` FOR EACH ROW
1489BEGIN
1490
1491 CALL DI$DOCUMENTDATA_sync(NEW.JID);
1492
1493END */$$
1494
1495
1496DELIMITER ;
1497
1498/* Trigger structure for table `JOURNAL_SYNC` */
1499
1500DELIMITER $$
1501
1502/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `DI$AUR_JOURNAL_SYNC` */$$
1503
1504/*!50003 CREATE */ /*!50003 TRIGGER `DI$AUR_JOURNAL_SYNC` AFTER UPDATE ON `JOURNAL_SYNC` FOR EACH ROW
1505BEGIN
1506
1507 CALL DI$DOCUMENTDATA_sync(NEW.JID);
1508
1509END */$$
1510
1511
1512DELIMITER ;
1513
1514/* Trigger structure for table `PARAMETER` */
1515
1516DELIMITER $$
1517
1518/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BIR_PARAMETER` */$$
1519
1520/*!50003 CREATE */ /*!50003 TRIGGER `CG$BIR_PARAMETER` BEFORE INSERT ON `PARAMETER` FOR EACH ROW BEGIN
1521 -- AR 180215 : Work in Progress
1522 DECLARE vutPAR_ID INT(38);
1523 DECLARE vutPAR_NAME VARCHAR(50);
1524 DECLARE vutPAR_VALUE TEXT;
1525 DECLARE vutPAR_DESCRIPTION TEXT;
1526 DECLARE vutPAR_INSERTEDBY VARCHAR(50);
1527 DECLARE vutPAR_UPDATEDBY VARCHAR(50);
1528 DECLARE vutPAR_CREATED DATETIME(6);
1529 DECLARE vutPAR_CREATEDBY VARCHAR(30);
1530 DECLARE vutPAR_MODIFIED DATETIME(6);
1531 DECLARE vutPAR_MODIFIEDBY VARCHAR(30);
1532
1533 SET @vutPAR_ID = NEW.PAR_ID;
1534 SET @vutPAR_NAME = NEW.PAR_NAME;
1535 SET @vutPAR_VALUE = NEW.PAR_VALUE;
1536 SET @vutPAR_DESCRIPTION = NEW.PAR_DESCRIPTION;
1537 SET @vutPAR_INSERTEDBY = NEW.PAR_INSERTEDBY;
1538 SET @vutPAR_UPDATEDBY = NEW.PAR_UPDATEDBY;
1539
1540 /*
1541 * Start: Following fields value are set by Trigger
1542 */
1543
1544 IF(NEW.PAR_INSERTEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.PAR_INSERTEDBY)) = 0)) THEN
1545 SET NEW.PAR_INSERTEDBY = USER();
1546 END IF;
1547
1548 IF(NEW.PAR_CREATEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.PAR_CREATEDBY)) = 0)) THEN
1549 SET NEW.PAR_CREATEDBY = USER();
1550 END IF;
1551 SET NEW.PAR_CREATED = SYSDATE();
1552
1553 IF(NEW.PAR_MODIFIEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.PAR_MODIFIEDBY)) = 0)) THEN
1554 SET NEW.PAR_MODIFIEDBY = USER();
1555 END IF;
1556 SET NEW.PAR_MODIFIED = SYSDATE();
1557
1558 CALL CG$PARAMETER_ins(@vitPAR_ID,FALSE);
1559
1560END */$$
1561
1562
1563DELIMITER ;
1564
1565/* Trigger structure for table `PARAMETER` */
1566
1567DELIMITER $$
1568
1569/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BUR_PARAMETER` */$$
1570
1571/*!50003 CREATE */ /*!50003 TRIGGER `CG$BUR_PARAMETER` BEFORE UPDATE ON `PARAMETER` FOR EACH ROW BEGIN
1572 -- AR 180215 : Work in Progress
1573
1574 DECLARE vutPAR_ID INT(38);
1575 DECLARE vutPAR_NAME VARCHAR(50);
1576 DECLARE vutPAR_VALUE TEXT;
1577 DECLARE vutPAR_DESCRIPTION TEXT;
1578 DECLARE vutPAR_INSERTEDBY VARCHAR(50);
1579 DECLARE vutPAR_UPDATEDBY VARCHAR(50);
1580 DECLARE vutPAR_CREATED DATETIME(6);
1581 DECLARE vutPAR_CREATEDBY VARCHAR(30);
1582 DECLARE vutPAR_MODIFIED DATETIME(6);
1583 DECLARE vutPAR_MODIFIEDBY VARCHAR(30);
1584
1585 SET @vutPAR_ID = OLD.PAR_ID;
1586 SET @vutPAR_NAME = NEW.PAR_NAME;
1587 SET @vutPAR_VALUE = NEW.PAR_VALUE;
1588 SET @vutPAR_DESCRIPTION = NEW.PAR_DESCRIPTION;
1589 SET @vutPAR_INSERTEDBY = NEW.PAR_INSERTEDBY;
1590 SET @vutPAR_UPDATEDBY = NEW.PAR_UPDATEDBY;
1591 SET @vutPAR_MODIFIED = NEW.PAR_MODIFIED;
1592 SET @vutPAR_MODIFIEDBY = NEW.PAR_MODIFIEDBY;
1593
1594 /*
1595 * Start: Following fields value are set by Trigger
1596 */
1597
1598 IF(NEW.PAR_UPDATEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.PAR_UPDATEDBY)) = 0)) THEN
1599 SET NEW.PAR_UPDATEDBY = USER();
1600 END IF;
1601
1602 IF(NEW.PAR_MODIFIEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.PAR_MODIFIEDBY)) = 0)) THEN
1603 SET NEW.PAR_MODIFIEDBY = USER();
1604 END IF;
1605 SET NEW.PAR_MODIFIED = SYSDATE();
1606
1607 /*
1608 * Start: Following fields value are set by Trigger
1609 */
1610 SET @vutPAR_UPDATEDBY = NEW.PAR_UPDATEDBY;
1611 SET @vutPAR_MODIFIED = NEW.PAR_MODIFIED;
1612 SET @vutPAR_MODIFIEDBY = NEW.PAR_MODIFIEDBY;
1613
1614 CALL CG$PARAMETER_upd(@vutPAR_ID,FALSE);
1615
1616END */$$
1617
1618
1619DELIMITER ;
1620
1621/* Trigger structure for table `PARAMETER` */
1622
1623DELIMITER $$
1624
1625/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$ADR_PARAMETER` */$$
1626
1627/*!50003 CREATE */ /*!50003 TRIGGER `CG$ADR_PARAMETER` AFTER DELETE ON `PARAMETER` FOR EACH ROW BEGIN
1628 -- AR 180215 : Work in Progress
1629 DECLARE vPAR_ID INT(38);
1630
1631 SELECT OLD.PAR_ID INTO vPAR_ID;
1632
1633 CALL CG$PARAMETER_DEL(vPAR_ID,FALSE);
1634
1635END */$$
1636
1637
1638DELIMITER ;
1639
1640/* Trigger structure for table `RECENT_SEARCH` */
1641
1642DELIMITER $$
1643
1644/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `RECENT_SEARCH_TRG` */$$
1645
1646/*!50003 CREATE */ /*!50003 TRIGGER `RECENT_SEARCH_TRG` BEFORE INSERT ON `RECENT_SEARCH` FOR EACH ROW BEGIN
1647 -- TI 180308 : Work in Progress
1648
1649 /*
1650 * Start: Following fields are updated via Trigger before insertion is completed
1651 */
1652 IF(NEW.SEARCH_DATETIME IS NULL) THEN
1653 SET NEW.SEARCH_DATETIME = SYSDATE();
1654 END IF;
1655 /*
1656 * End: Fields are updated via Trigger before insertion is completed
1657 */
1658 END */$$
1659
1660
1661DELIMITER ;
1662
1663/* Trigger structure for table `ROLES` */
1664
1665DELIMITER $$
1666
1667/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BIR_ROLES` */$$
1668
1669/*!50003 CREATE */ /*!50003 TRIGGER `CG$BIR_ROLES` BEFORE INSERT ON `ROLES` FOR EACH ROW BEGIN
1670 -- TI 180213 : Work in Progress
1671 DECLARE vitROL_ID INT(38);
1672 DECLARE vitROL_NAME VARCHAR(50);
1673 DECLARE vitROL_DESCRIPTION VARCHAR(255);
1674 DECLARE vitROL_INSERTEDBY VARCHAR(50);
1675 DECLARE vitROL_UPDATEDBY VARCHAR(50);
1676 DECLARE vitROL_CREATED BOOLEAN;
1677 DECLARE vitROL_CREATEDBY BOOLEAN;
1678 DECLARE vitROL_MODIFIED BOOLEAN;
1679 DECLARE vitROL_MODIFIEDBY BOOLEAN;
1680
1681 SET @vitROL_ID = NEW.ROL_ID;
1682 SET @vitROL_NAME = NEW.ROL_NAME;
1683 SET @vitROL_DESCRIPTION = NEW.ROL_DESCRIPTION;
1684 SET @vitROL_INSERTEDBY = NEW.ROL_INSERTEDBY;
1685 SET @vitROL_UPDATEDBY = NEW.ROL_UPDATEDBY;
1686
1687 /* Custom Exception Handling: For Testing Purpose Only
1688 IF @vitROL_DESCRIPTION IS NULL THEN
1689 signal sqlstate '45000' set message_text = 'My Error Message';
1690 END IF;
1691 */
1692
1693 /*
1694 * Start: Following fields are updated via Trigger before insertion is completed
1695 */
1696 IF(NEW.ROL_INSERTEDBY IS NULL) THEN
1697 SET NEW.ROL_INSERTEDBY = USER();
1698 END IF;
1699
1700 SET NEW.ROL_CREATED = SYSDATE();
1701 IF(NEW.ROL_CREATEDBY IS NULL) THEN
1702 SET NEW.ROL_CREATEDBY = USER();
1703 end if;
1704
1705 SET NEW.ROL_MODIFIED = SYSDATE();
1706 IF(NEW.ROL_MODIFIEDBY IS NULL) THEN
1707 SET NEW.ROL_MODIFIEDBY = USER();
1708 END IF;
1709 /*
1710 * End: Fields are updated via Trigger before insertion is completed
1711 */
1712
1713 /*
1714 * Following logic is just a copy from Existing Oracle Trigger
1715 */
1716 IF(NEW.ROL_CREATED IS NULL) THEN
1717 SET @vitROL_CREATED = FALSE;
1718 ELSE
1719 SET @vitROL_CREATED = TRUE;
1720 END IF;
1721
1722 IF(NEW.ROL_CREATEDBY IS NULL) THEN
1723 SET @vitROL_CREATEDBY = FALSE;
1724 ELSE
1725 SET @vitROL_CREATEDBY = TRUE;
1726 END IF;
1727
1728 IF(NEW.ROL_MODIFIED IS NULL) THEN
1729 SET @vitROL_MODIFIED = FALSE;
1730 ELSE
1731 SET @vitROL_MODIFIED = TRUE;
1732 END IF;
1733
1734 IF(NEW.ROL_MODIFIEDBY IS NULL) THEN
1735 SET @vitROL_MODIFIEDBY = FALSE;
1736 ELSE
1737 SET @vitROL_MODIFIEDBY = TRUE;
1738 END IF;
1739
1740 CALL CG$ROLES_ins(@vitROL_ID,FALSE);
1741 END */$$
1742
1743
1744DELIMITER ;
1745
1746/* Trigger structure for table `ROLES` */
1747
1748DELIMITER $$
1749
1750/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `roles_after_insert` */$$
1751
1752/*!50003 CREATE */ /*!50003 TRIGGER `roles_after_insert` AFTER INSERT ON `ROLES` FOR EACH ROW
1753
1754BEGIN
1755
1756 DECLARE vRole varchar(50);
1757
1758 -- Find username of person performing the INSERT into table
1759 SELECT USER() INTO vRole;
1760
1761 -- Insert record into audit table
1762 /*INSERT INTO animals
1763 ( ID,
1764 NAME)
1765 VALUES
1766 ( NEW.rol_id,
1767 vRole );*/
1768
1769END */$$
1770
1771
1772DELIMITER ;
1773
1774/* Trigger structure for table `ROLES` */
1775
1776DELIMITER $$
1777
1778/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BUR_ROLES` */$$
1779
1780/*!50003 CREATE */ /*!50003 TRIGGER `CG$BUR_ROLES` BEFORE UPDATE ON `ROLES` FOR EACH ROW BEGIN
1781 -- TI 180213 : Work in Progress
1782 DECLARE vutROL_ID INT(38);
1783 DECLARE vutROL_NAME VARCHAR(50);
1784 DECLARE vutROL_DESCRIPTION VARCHAR(255);
1785 DECLARE vutROL_INSERTEDBY VARCHAR(50);
1786 DECLARE vutROL_UPDATEDBY VARCHAR(50);
1787 DECLARE vutROL_CREATED DATETIME(6);
1788 DECLARE vutROL_CREATEDBY VARCHAR(50);
1789 DECLARE vutROL_MODIFIED DATETIME(6);
1790 DECLARE vutROL_MODIFIEDBY VARCHAR(50);
1791
1792 SET @vutROL_ID = OLD.ROL_ID;
1793 SET @vutROL_NAME = NEW.ROL_NAME;
1794 SET @vutROL_DESCRIPTION = NEW.ROL_DESCRIPTION;
1795 SET @vutROL_INSERTEDBY = NEW.ROL_INSERTEDBY;
1796 SET @vutROL_UPDATEDBY = NEW.ROL_UPDATEDBY;
1797
1798 /*
1799 * Start: Following fields are updated via Trigger before update is completed
1800 */
1801
1802 IF(NEW.ROL_UPDATEDBY IS NULL) THEN
1803 SET NEW.ROL_UPDATEDBY = USER();
1804 END IF;
1805
1806 SET NEW.ROL_MODIFIED = SYSDATE();
1807 IF(NEW.ROL_MODIFIEDBY IS NULL) THEN
1808 SET NEW.ROL_MODIFIEDBY = USER();
1809 END IF;
1810 /*
1811 * End: Fields are updated via Trigger before update is completed
1812 */
1813
1814 CALL CG$ROLES_upd(@vutROL_ID,FALSE);
1815 END */$$
1816
1817
1818DELIMITER ;
1819
1820/* Trigger structure for table `ROLES` */
1821
1822DELIMITER $$
1823
1824/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$ADR_ROLES` */$$
1825
1826/*!50003 CREATE */ /*!50003 TRIGGER `CG$ADR_ROLES` AFTER DELETE ON `ROLES` FOR EACH ROW BEGIN
1827 -- TI 180212 : Work in Progress
1828 DECLARE vROL_ID INT(38);
1829 #DECLARE vROL_NAME varchar(50);
1830
1831 SELECT OLD.ROL_ID INTO vROL_ID;
1832 #SELECT OLD.ROL_NAME INTO vROL_NAME;
1833
1834 call CG$ROLES_DEL(vROL_ID,FALSE);
1835
1836 END */$$
1837
1838
1839DELIMITER ;
1840
1841/* Trigger structure for table `SCHEDULING` */
1842
1843DELIMITER $$
1844
1845/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BIR_SCHEDULING` */$$
1846
1847/*!50003 CREATE */ /*!50003 TRIGGER `CG$BIR_SCHEDULING` BEFORE INSERT ON `SCHEDULING` FOR EACH ROW BEGIN
1848 -- AR 180215 : Work in Progress
1849 DECLARE vitSCH_ID INT(38);
1850 DECLARE vitSCH_TIME DATETIME(6);
1851 DECLARE vitSCH_TASK TEXT;
1852 DECLARE vitSCH_RESULT TEXT;
1853 DECLARE vitSCH_STATUS VARCHAR(1);
1854 DECLARE vitSCH_INSERTEDBY VARCHAR(50);
1855 DECLARE vitSCH_CREATED DATETIME(6);
1856 DECLARE vitSCH_CREATEDBY VARCHAR(30);
1857
1858 SET @vitSCH_ID = NEW.SCH_ID;
1859 SET @vitSCH_TIME = NEW.SCH_TIME;
1860 SET @vitSCH_TASK = NEW.SCH_TASK;
1861 SET @vitSCH_RESULT = NEW.SCH_RESULT;
1862 SET @vitSCH_STATUS = NEW.SCH_STATUS;
1863 SET @vitSCH_INSERTEDBY = NEW.SCH_INSERTEDBY;
1864 SET @vitSCH_CREATED = NEW.SCH_CREATED;
1865 SET @vitSCH_CREATEDBY = NEW.SCH_CREATEDBY;
1866
1867 /*
1868 * Start: Following fields value are set by Trigger
1869 */
1870 IF(NEW.SCH_INSERTEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.SCH_INSERTEDBY)) = 0)) THEN
1871 SET NEW.SCH_INSERTEDBY = USER();
1872 END IF;
1873
1874 IF(NEW.SCH_CREATEDBY IS NULL OR (CHAR_LENGTH(TRIM(NEW.SCH_CREATEDBY)) = 0)) THEN
1875 SET NEW.SCH_CREATEDBY = USER();
1876 END IF;
1877 SET NEW.SCH_CREATED = SYSDATE();
1878
1879
1880 /*
1881 * Start: Following fields value are set by Trigger
1882 */
1883 SET @vitSCH_INSERTEDBY = NEW.SCH_INSERTEDBY;
1884 SET @vitSCH_CREATED = NEW.SCH_CREATED;
1885 SET @vitSCH_CREATEDBY = NEW.SCH_CREATEDBY;
1886
1887 /*
1888 * Following logic is just a copy from Existing Oracle Trigger
1889 */
1890 IF(NEW.SCH_CREATED IS NULL) THEN
1891 SET @vitSCH_CREATED = FALSE;
1892 ELSE
1893 SET @vitSCH_CREATED = TRUE;
1894 END IF;
1895
1896 IF(NEW.SCH_CREATEDBY IS NULL) THEN
1897 SET @vitSCH_CREATEDBY = FALSE;
1898 ELSE
1899 SET @vitSCH_CREATEDBY = TRUE;
1900 END IF;
1901
1902 CALL CG$SCHEDULING_ins(@vitSCH_ID,FALSE);
1903
1904END */$$
1905
1906
1907DELIMITER ;
1908
1909/* Trigger structure for table `SCHEDULING` */
1910
1911DELIMITER $$
1912
1913/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BUR_SCHEDULING` */$$
1914
1915/*!50003 CREATE */ /*!50003 TRIGGER `CG$BUR_SCHEDULING` BEFORE UPDATE ON `SCHEDULING` FOR EACH ROW BEGIN
1916 -- AR 180215 : Work in Progress
1917 DECLARE vutSCH_ID INT(38);
1918 DECLARE vutSCH_TIME DATETIME(6);
1919 DECLARE vutSCH_TASK TEXT;
1920 DECLARE vutSCH_RESULT TEXT;
1921 DECLARE vutSCH_STATUS VARCHAR(1);
1922 DECLARE vutSCH_INSERTEDBY VARCHAR(50);
1923 DECLARE vutSCH_CREATED DATETIME(6);
1924 DECLARE vutSCH_CREATEDBY VARCHAR(30);
1925
1926 SET @vutSCH_ID = OLD.SCH_ID;
1927 SET @vutSCH_TIME = NEW.SCH_TIME;
1928 SET @vutSCH_TASK = NEW.SCH_TASK;
1929 SET @vutSCH_RESULT = NEW.SCH_RESULT;
1930 SET @vutSCH_STATUS = NEW.SCH_STATUS;
1931 SET @vutSCH_INSERTEDBY = NEW.SCH_INSERTEDBY;
1932 SET @vutSCH_CREATED = NEW.SCH_CREATED;
1933 SET @vutSCH_CREATEDBY = NEW.SCH_CREATEDBY;
1934
1935 CALL CG$SCHEDULING_upd(@vutSCH_ID,FALSE);
1936
1937END */$$
1938
1939
1940DELIMITER ;
1941
1942/* Trigger structure for table `SCHEDULING` */
1943
1944DELIMITER $$
1945
1946/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$ADR_SCHEDULING` */$$
1947
1948/*!50003 CREATE */ /*!50003 TRIGGER `CG$ADR_SCHEDULING` AFTER DELETE ON `SCHEDULING` FOR EACH ROW BEGIN
1949 -- AR 180215 : Work in Progress
1950 DECLARE vSCH_ID INT(38);
1951
1952 SELECT OLD.SCH_ID INTO vSCH_ID;
1953
1954 CALL CG$SCHEDULING_DEL(vSCH_ID,FALSE);
1955
1956END */$$
1957
1958
1959DELIMITER ;
1960
1961/* Trigger structure for table `USERCOMMENTS` */
1962
1963DELIMITER $$
1964
1965/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `TRG$BIR_USERCOMMENTS` */$$
1966
1967/*!50003 CREATE */ /*!50003 TRIGGER `TRG$BIR_USERCOMMENTS` BEFORE INSERT ON `USERCOMMENTS` FOR EACH ROW BEGIN
1968 -- TI 180308 : Work in Progress
1969
1970 /*
1971 * Start: Following fields are updated via Trigger before insertion is completed
1972 */
1973 IF(NEW.CREATED_ON IS NULL) THEN
1974 SET NEW.CREATED_ON = SYSDATE();
1975 END IF;
1976 /*
1977 * End: Fields are updated via Trigger before insertion is completed
1978 */
1979 END */$$
1980
1981
1982DELIMITER ;
1983
1984/* Trigger structure for table `USERCOMMENTS` */
1985
1986DELIMITER $$
1987
1988/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `TRG$BUR_USERCOMMENTS` */$$
1989
1990/*!50003 CREATE */ /*!50003 TRIGGER `TRG$BUR_USERCOMMENTS` BEFORE UPDATE ON `USERCOMMENTS` FOR EACH ROW BEGIN
1991
1992 /*
1993 * Start: Following fields are updated via Trigger before UPDATE is completed
1994 */
1995 IF(NEW.MODIFIED_ON IS NULL) THEN
1996 SET NEW.MODIFIED_ON = SYSDATE();
1997 END IF;
1998 /*
1999 * End: Fields are updated via Trigger before UPDATE is completed
2000 */
2001
2002 END */$$
2003
2004
2005DELIMITER ;
2006
2007/* Trigger structure for table `USERROLES` */
2008
2009DELIMITER $$
2010
2011/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BIR_USERROLES` */$$
2012
2013/*!50003 CREATE */ /*!50003 TRIGGER `CG$BIR_USERROLES` BEFORE INSERT ON `USERROLES` FOR EACH ROW BEGIN
2014 -- TI 180213 : Work in Progress
2015
2016 DECLARE vitURO_ID int(38);
2017 DECLARE vitURO_ROL_ID int(38);
2018 DECLARE vitURO_USR_ID int(38);
2019
2020 SET @vitURO_ID = NEW.URO_ID;
2021 SET @vitURO_ROL_ID = NEW.URO_ROL_ID;
2022 SET @vitURO_USR_ID = NEW.URO_USR_ID;
2023
2024 CALL CG$USERROLES_ins(@vitUSR_ID,FALSE);
2025 END */$$
2026
2027
2028DELIMITER ;
2029
2030/* Trigger structure for table `USERROLES` */
2031
2032DELIMITER $$
2033
2034/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BUR_USERROLES` */$$
2035
2036/*!50003 CREATE */ /*!50003 TRIGGER `CG$BUR_USERROLES` BEFORE UPDATE ON `USERROLES` FOR EACH ROW BEGIN
2037 -- TI 180213 : Work in Progress
2038
2039 DECLARE vutURO_ID int(38);
2040 DECLARE vutURO_ROL_ID int(38);
2041 DECLARE vutURO_USR_ID int(38);
2042
2043 SET @vutURO_ID = NEW.URO_ID;
2044 SET @vutURO_ROL_ID = NEW.URO_ROL_ID;
2045 SET @vutURO_USR_ID = NEW.URO_USR_ID;
2046
2047 CALL CG$USERROLES_upd(@vutURO_ID,FALSE);
2048
2049 END */$$
2050
2051
2052DELIMITER ;
2053
2054/* Trigger structure for table `USERROLES` */
2055
2056DELIMITER $$
2057
2058/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$ADR_USERROLES` */$$
2059
2060/*!50003 CREATE */ /*!50003 TRIGGER `CG$ADR_USERROLES` AFTER DELETE ON `USERROLES` FOR EACH ROW BEGIN
2061 -- TI 180212 : Work in Progress
2062
2063 DECLARE vURO_ID int(38);
2064
2065 SELECT OLD.URO_ID INTO vURO_ID;
2066
2067 CALL CG$USERROLES_DEL(vURO_ID,FALSE);
2068
2069 END */$$
2070
2071
2072DELIMITER ;
2073
2074/* Trigger structure for table `USERS` */
2075
2076DELIMITER $$
2077
2078/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BIR_USERS` */$$
2079
2080/*!50003 CREATE */ /*!50003 TRIGGER `CG$BIR_USERS` BEFORE INSERT ON `USERS` FOR EACH ROW BEGIN
2081 -- TI 180213 : Work in Progress
2082 DECLARE vitUSR_ID INT(38);
2083 DECLARE vitUSR_USRID VARCHAR(30);
2084 DECLARE vitUSR_EMAIL VARCHAR(100);
2085 DECLARE vitUSR_FIRSTNAME VARCHAR(50);
2086 DECLARE vitUSR_LASTNAME VARCHAR(50);
2087 DECLARE vitUSR_INSERTEDBY VARCHAR(50);
2088 DECLARE vitUSR_UPDATEDBY VARCHAR(50);
2089 DECLARE vitUSR_CREATED BOOLEAN;
2090 DECLARE vitUSR_CREATEDBY BOOLEAN;
2091 DECLARE vitUSR_MODIFIED BOOLEAN;
2092 DECLARE vitUSR_MODIFIEDBY BOOLEAN;
2093 DECLARE vitUSR_COUNTRY VARCHAR(200);
2094 DECLARE vitUSR_LOCATION VARCHAR(200);
2095
2096 SET @vitUSR_ID = NEW.USR_ID;
2097 SET @vitUSR_USRID = NEW.USR_USRID;
2098 SET @vitUSR_EMAIL = NEW.USR_EMAIL;
2099 SET @vitUSR_FIRSTNAME = NEW.USR_FIRSTNAME;
2100 SET @vitUSR_LASTNAME = NEW.USR_LASTNAME;
2101 SET @vitUSR_INSERTEDBY = NEW.USR_INSERTEDBY;
2102 SET @vitUSR_UPDATEDBY = NEW.USR_UPDATEDBY;
2103 SET @vitUSR_COUNTRY = NEW.USR_COUNTRY;
2104 SET @vitUSR_LOCATION = NEW.USR_LOCATION;
2105
2106 /*
2107 * Start: Following fields are updated via Trigger before insertion is completed
2108 */
2109 IF(NEW.USR_INSERTEDBY IS NULL) THEN
2110 SET NEW.USR_INSERTEDBY = USER();
2111 END IF;
2112
2113 SET NEW.USR_CREATED = SYSDATE();
2114 IF(NEW.USR_CREATEDBY IS NULL) THEN
2115 SET NEW.USR_CREATEDBY = USER();
2116 END IF;
2117
2118 SET NEW.USR_MODIFIED = SYSDATE();
2119 IF(NEW.USR_MODIFIEDBY IS NULL) THEN
2120 SET NEW.USR_MODIFIEDBY = USER();
2121 END IF;
2122 /*
2123 * End: Fields are updated via Trigger before insertion is completed
2124 */
2125
2126 /*
2127 * Following logic is just a copy from Existing Oracle Trigger
2128 */
2129 IF(NEW.USR_CREATED IS NULL) THEN
2130 SET @vitUSR_CREATED = FALSE;
2131 ELSE
2132 SET @vitUSR_CREATED = TRUE;
2133 END IF;
2134
2135 IF(NEW.USR_CREATEDBY IS NULL) THEN
2136 SET @vitUSR_CREATEDBY = FALSE;
2137 ELSE
2138 SET @vitUSR_CREATEDBY = TRUE;
2139 END IF;
2140
2141 IF(NEW.USR_MODIFIED IS NULL) THEN
2142 SET @vitUSR_MODIFIED = FALSE;
2143 ELSE
2144 SET @vitUSR_MODIFIED = TRUE;
2145 END IF;
2146
2147 IF(NEW.USR_MODIFIEDBY IS NULL) THEN
2148 SET @vitUSR_MODIFIEDBY = FALSE;
2149 ELSE
2150 SET @vitUSR_MODIFIEDBY = TRUE;
2151 END IF;
2152
2153 CALL CG$USERS_ins(@vitUSR_ID,FALSE);
2154 END */$$
2155
2156
2157DELIMITER ;
2158
2159/* Trigger structure for table `USERS` */
2160
2161DELIMITER $$
2162
2163/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `DI$AIR_USERS` */$$
2164
2165/*!50003 CREATE */ /*!50003 TRIGGER `DI$AIR_USERS` AFTER INSERT ON `USERS` FOR EACH ROW
2166BEGIN
2167
2168 CALL DI$CUSTOMERS_sync(NEW.USR_ID, NEW.USR_EMAIL);
2169
2170END */$$
2171
2172
2173DELIMITER ;
2174
2175/* Trigger structure for table `USERS` */
2176
2177DELIMITER $$
2178
2179/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$BUR_USERS` */$$
2180
2181/*!50003 CREATE */ /*!50003 TRIGGER `CG$BUR_USERS` BEFORE UPDATE ON `USERS` FOR EACH ROW BEGIN
2182 -- TI 180213 : Work in Progress
2183 DECLARE vutUSR_ID INT(38);
2184 DECLARE vutUSR_USRID VARCHAR(30);
2185 DECLARE vutUSR_EMAIL VARCHAR(100);
2186 DECLARE vutUSR_FIRSTNAME VARCHAR(50);
2187 DECLARE vutUSR_LASTNAME VARCHAR(50);
2188 DECLARE vutUSR_INSERTEDBY VARCHAR(50);
2189 DECLARE vutUSR_UPDATEDBY VARCHAR(50);
2190 DECLARE vutUSR_CREATED DATETIME(6);
2191 DECLARE vutUSR_CREATEDBY VARCHAR(50);
2192 DECLARE vutUSR_MODIFIED DATETIME(6);
2193 DECLARE vutUSR_MODIFIEDBY VARCHAR(50);
2194 DECLARE vutUSR_COUNTRY VARCHAR(200);
2195 DECLARE vutUSR_LOCATION VARCHAR(200);
2196
2197 SET @vutUSR_ID = OLD.USR_ID;
2198 SET @vutUSR_USRID = NEW.USR_USRID;
2199 SET @vutUSR_EMAIL = NEW.USR_EMAIL;
2200 SET @vutUSR_FIRSTNAME = NEW.USR_FIRSTNAME;
2201 SET @vutUSR_LASTNAME = NEW.USR_LASTNAME;
2202 SET @vutUSR_INSERTEDBY = NEW.USR_INSERTEDBY;
2203 SET @vutUSR_UPDATEDBY = NEW.USR_UPDATEDBY;
2204 SET @vutUSR_COUNTRY = NEW.USR_COUNTRY;
2205 SET @vutUSR_LOCATION = NEW.USR_LOCATION;
2206
2207 /*
2208 * Start: Following fields are updated via Trigger before update is completed
2209 */
2210
2211 IF(NEW.USR_UPDATEDBY IS NULL) THEN
2212 SET NEW.USR_UPDATEDBY = USER();
2213 END IF;
2214
2215 SET NEW.USR_MODIFIED = SYSDATE();
2216 IF(NEW.USR_MODIFIEDBY IS NULL) THEN
2217 SET NEW.USR_MODIFIEDBY = USER();
2218 END IF;
2219 /*
2220 * End: Fields are updated via Trigger before update is completed
2221 */
2222
2223 CALL CG$USERS_upd(@vutUSR_ID,FALSE);
2224 END */$$
2225
2226
2227DELIMITER ;
2228
2229/* Trigger structure for table `USERS` */
2230
2231DELIMITER $$
2232
2233/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `DI$AUR_USERS` */$$
2234
2235/*!50003 CREATE */ /*!50003 TRIGGER `DI$AUR_USERS` AFTER UPDATE ON `USERS` FOR EACH ROW
2236BEGIN
2237
2238 CALL DI$CUSTOMERS_sync(NEW.USR_ID, OLD.USR_EMAIL);
2239
2240END */$$
2241
2242
2243DELIMITER ;
2244
2245/* Trigger structure for table `USERS` */
2246
2247DELIMITER $$
2248
2249/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `CG$ADR_USERS` */$$
2250
2251/*!50003 CREATE */ /*!50003 TRIGGER `CG$ADR_USERS` AFTER DELETE ON `USERS` FOR EACH ROW BEGIN
2252 -- TI 180212 : Work in Progress
2253 DECLARE vUSR_ID INT(38);
2254 #DECLARE vUSR_NAME varchar(50);
2255
2256 SELECT OLD.USR_ID INTO vUSR_ID;
2257 #SELECT OLD.USR_NAME INTO vUSR_NAME;
2258
2259 CALL CG$USERS_DEL(vUSR_ID,FALSE);
2260
2261 END */$$
2262
2263
2264DELIMITER ;
2265
2266/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2267/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2268/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2269/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;