80 lines
2.7 KiB
MySQL
80 lines
2.7 KiB
MySQL
-- Version: 2025.7.31 14:52:27
|
||
Drop Table T_RULE_CFG@
|
||
|
||
Create Table T_RULE_CFG(
|
||
RuleId VARCHAR(50) NOT NULL,
|
||
RuleGroup VARCHAR(50) NOT NULL,
|
||
AlgId DECIMAL(10) NOT NULL,
|
||
Flag DECIMAL(1),
|
||
RuleName VARCHAR(200),
|
||
RuleParam VARCHAR(3000),
|
||
EqpId VARCHAR(20),
|
||
Remark VARCHAR(3000),
|
||
PaddingUp DECIMAL(8,2),
|
||
PaddingDown DECIMAL(8,2),
|
||
DeWeight DECIMAL(8,2),
|
||
toc TIMESTAMP,
|
||
tom TIMESTAMP,
|
||
mop VARCHAR(120),
|
||
mou VARCHAR(30),
|
||
CONSTRAINT pk_T_RULE_CFG PRIMARY KEY (RuleId, RuleGroup, AlgId)
|
||
)
|
||
IN TB8KUSREMS INDEX IN TB8KIDXEMS@
|
||
Alter Table T_RULE_CFG ALTER COLUMN Flag SET WITH Default 1@
|
||
|
||
COMMENT ON Table T_RULE_CFG IS '设备规则配置'@
|
||
|
||
COMMENT ON T_RULE_CFG (RuleId IS '规则id')@
|
||
COMMENT ON T_RULE_CFG (RuleGroup IS '规则分组')@
|
||
COMMENT ON T_RULE_CFG (AlgId IS '算法id')@
|
||
COMMENT ON T_RULE_CFG (Flag IS '可用标记')@
|
||
COMMENT ON T_RULE_CFG (RuleName IS '报警名称')@
|
||
COMMENT ON T_RULE_CFG (RuleParam IS '规则参数 { "tags": ["tagname1"，"tagname2"]， "limit_alarm": { "max": 11， "min": 22， "content": "" }， "limit_error": { "max": 11， "min": 22， "content": "" }， "time": 0 }')@
|
||
COMMENT ON T_RULE_CFG (EqpId IS '设备编号')@
|
||
COMMENT ON T_RULE_CFG (Remark IS '备注说明')@
|
||
COMMENT ON T_RULE_CFG (PaddingUp IS '区间上限加')@
|
||
COMMENT ON T_RULE_CFG (PaddingDown IS '区间下限减')@
|
||
COMMENT ON T_RULE_CFG (DeWeight IS '扣分权重')@
|
||
|
||
/****************************************************************************
|
||
INSERT TRIGGER FOR TABLE
|
||
T_RULE_CFG
|
||
*****************************************************************************/
|
||
DROP TRIGGER ins_b_T_RULE_CFG@
|
||
|
||
CREATE OR REPLACE TRIGGER ins_b_T_RULE_CFG
|
||
BEFORE INSERT ON T_RULE_CFG 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_RULE_CFG
|
||
*****************************************************************************/
|
||
DROP TRIGGER upd_b_T_RULE_CFG@
|
||
|
||
CREATE OR REPLACE TRIGGER upd_b_T_RULE_CFG
|
||
BEFORE UPDATE ON T_RULE_CFG 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
|
||
@
|