78 lines
2.9 KiB
MySQL
78 lines
2.9 KiB
MySQL
-- Version: 2025.7.31 14:52:27
|
||
Drop Table T_LOV_LIMIT@
|
||
|
||
Create Table T_LOV_LIMIT(
|
||
plantType VARCHAR(8) NOT NULL,
|
||
limitName VARCHAR(60) NOT NULL,
|
||
CheckType DECIMAL(10),
|
||
Factor DECIMAL(10),
|
||
LowerLimit DECIMAL(10),
|
||
UpperLimit DECIMAL(10),
|
||
Enumerate VARCHAR(256),
|
||
WarnLevel DECIMAL(10),
|
||
ModDescr VARCHAR(256),
|
||
toc TIMESTAMP,
|
||
tom TIMESTAMP,
|
||
mop VARCHAR(120),
|
||
mou VARCHAR(30),
|
||
CONSTRAINT pk_T_LOV_LIMIT PRIMARY KEY (plantType, limitName)
|
||
)
|
||
IN TB8KUSREMS INDEX IN TB8KIDXEMS@
|
||
Alter Table T_LOV_LIMIT ALTER COLUMN CheckType SET WITH Default 0@
|
||
Alter Table T_LOV_LIMIT ALTER COLUMN Factor SET WITH Default 1@
|
||
Alter Table T_LOV_LIMIT ALTER COLUMN WarnLevel SET WITH Default 10@
|
||
|
||
COMMENT ON Table T_LOV_LIMIT IS '极限值检查表'@
|
||
|
||
COMMENT ON T_LOV_LIMIT (plantType IS 'Such as "PDI"，"L2"， "L3"')@
|
||
COMMENT ON T_LOV_LIMIT (limitName IS 'name of the value')@
|
||
COMMENT ON T_LOV_LIMIT (CheckType IS '0:不检查 1:检查数字上下限 2:检查是全部为可见字符 3:字符串是否包含在Enumerate字段内 4:检查字符串是否是纯数字 5:检查字符串是否是纯字母 6:字符串是否满足Enumerate的正则表达式 7:字符串是否满足Enumerate的math公式')@
|
||
COMMENT ON T_LOV_LIMIT (Factor IS '整数和小数转换因子')@
|
||
COMMENT ON T_LOV_LIMIT (LowerLimit IS 'CheckType为1时的检查下限')@
|
||
COMMENT ON T_LOV_LIMIT (UpperLimit IS 'CheckType为1时的检查上限')@
|
||
COMMENT ON T_LOV_LIMIT (Enumerate IS '用于CheckType为3和7时使用')@
|
||
COMMENT ON T_LOV_LIMIT (WarnLevel IS '检查失败后的返回结果')@
|
||
COMMENT ON T_LOV_LIMIT (ModDescr IS 'description of modification')@
|
||
|
||
/****************************************************************************
|
||
INSERT TRIGGER FOR TABLE
|
||
T_LOV_LIMIT
|
||
*****************************************************************************/
|
||
DROP TRIGGER ins_b_T_LOV_LIMIT@
|
||
|
||
CREATE OR REPLACE TRIGGER ins_b_T_LOV_LIMIT
|
||
BEFORE INSERT ON T_LOV_LIMIT REFERENCING NEW AS NEWROW FOR EACH ROW
|
||
MODE DB2SQL
|
||
BEGIN ATOMIC
|
||
DECLARE programm VARCHAR(120);
|
||
DECLARE auth VARCHAR(30);
|
||
|
||
set (programm,auth) = (SELECT APPL_NAME,AUTHID FROM SYSIBMADM.APPLICATIONS WHERE APPL_ID = (SELECT APPLICATION_ID() FROM dual));
|
||
|
||
set NEWROW.toc = (values sysdate);
|
||
set NEWROW.tom = (values sysdate);
|
||
set NEWROW.mop = programm;
|
||
set NEWROW.mou = auth;
|
||
END
|
||
@
|
||
/****************************************************************************
|
||
UPDATE TRIGGER FOR TABLE
|
||
T_LOV_LIMIT
|
||
*****************************************************************************/
|
||
DROP TRIGGER upd_b_T_LOV_LIMIT@
|
||
|
||
CREATE OR REPLACE TRIGGER upd_b_T_LOV_LIMIT
|
||
BEFORE UPDATE ON T_LOV_LIMIT REFERENCING NEW AS NEWROW FOR EACH ROW
|
||
MODE DB2SQL
|
||
BEGIN ATOMIC
|
||
DECLARE programm VARCHAR(120);
|
||
DECLARE auth VARCHAR(30);
|
||
|
||
set (programm,auth) = (SELECT APPL_NAME,AUTHID FROM SYSIBMADM.APPLICATIONS WHERE APPL_ID = (SELECT APPLICATION_ID() FROM dual));
|
||
|
||
set NEWROW.tom = (values sysdate);
|
||
set NEWROW.mop = programm;
|
||
set NEWROW.mou = auth;
|
||
END
|
||
@
|