问题来源:当记录表新增连续三个不良,做出预警。
解决思路:在记录表新增数据时,判断是否连续三次出现不良,如果是,进行不良处理。
创建连续不良 触发器
CREATE OR REPLACE TRIGGER TRI_MDC_PROWORK_INSERT
AFTER INSERT ON MDC_PROWORK
FOR EACH ROW --在插入行后触发
DECLARE
V_COUNTNO NUMBER := 0; --定义变量
QTY NUMBER := 0;
ITEM_KEY VARCHAR2(32);
SQTY NUMBER := 0;
BEGINDATE VARCHAR2(19);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF :NEW.PROCESS_QUALITY_STATE = '不良品' OR
:NEW.PROCESS_QUALITY_STATE = '不合格' THEN
SELECT E.PRODUCT_KEY
INTO ITEM_KEY
FROM MSCH_DISPATCH E
WHERE E.DISPATCH_KEY = :NEW.DISPATCH_KEY;
SELECT NVL(I.SERIAL_QTY, 10)
INTO SQTY
FROM MBDM_ITEM I
WHERE I.ITEM_KEY = ITEM_KEY;
SELECT COUNT(1)
INTO QTY
FROM MDC_SERIAL_ERROR A
WHERE A.WORKORGAN_KEY = :NEW.WORKORGAN_KEY
AND A.CURRENT_PROCESS = :NEW.CURRENT_PROCESS
ORDER BY A.TRX_DATE DESC;
IF QTY > 0 THEN
SELECT TRX_DATE
INTO BEGINDATE
FROM (SELECT A.TRX_DATE
FROM MDC_SERIAL_ERROR A
WHERE A.WORKORGAN_KEY = :NEW.WORKORGAN_KEY
AND A.CURRENT_PROCESS = :NEW.CURRENT_PROCESS
ORDER BY A.TRX_DATE DESC)
WHERE ROWNUM = 1;
ELSE
SELECT TO_CHAR(SYSDATE - 1 / 24, 'YYYY-MM-DD HH24:MI:SS')
INTO BEGINDATE
FROM DUAL;
END IF;
SELECT COUNT(*)
INTO V_COUNTNO
FROM (SELECT *
FROM (SELECT PROCESS_QUALITY_STATE
FROM MDC_PROWORK D
WHERE D.WORKORGAN_KEY = :NEW.WORKORGAN_KEY
AND D.CURRENT_PROCESS = :NEW.CURRENT_PROCESS
AND D.TRX_DATE > BEGINDATE
ORDER BY TRX_DATE DESC) T
WHERE ROWNUM < SQTY) T1
WHERE PROCESS_QUALITY_STATE = '不良品'
OR PROCESS_QUALITY_STATE = '不合格';
IF V_COUNTNO >= SQTY - 1 THEN
INSERT INTO MDC_SERIAL_ERROR
SELECT SUBSTR(SYS_GUID(), 0, 32),
'1',
:NEW.WORKORGAN_KEY,
:NEW.CURRENT_PROCESS,
ITEM_KEY,
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
V_COUNTNO,
'Y',
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
:NEW.TRX_USER_KEY,
'1'
FROM DUAL;
END IF;
COMMIT;
END IF;
END;