mysql触发器中case语句_一个很好的触发器例子（case when）

CREATE OR REPLACE TRIGGER TR_CGD1

BEFORE UPDATE OR INSERT OR DELETE ON BB_MJ_CGD1_TB

FOR EACH ROW

DECLARE

V_COUNT NUMBER;

BEGIN

CASE

WHEN UPDATING OR INSERTING THEN

IF :NEW.DJZT = '结束' THEN

:NEW.DJZT := '结束';

ELSE

SELECT COUNT(*)

INTO V_COUNT

FROM BB_MJ_KCRKD2_TB RKD2

WHERE CGD2ID IN

(SELECT ID FROM BB_MJ_CGD2_TB WHERE CGD1ID = :NEW.ID);

END IF;

WHEN DELETING THEN

SELECT COUNT(*)

INTO V_COUNT

FROM BB_MJ_KCRKD2_TB RKD2

WHERE CGD2ID IN

(SELECT ID FROM BB_MJ_CGD2_TB WHERE CGD1ID = :OLD.ID);

END CASE;

IF V_COUNT > 0 THEN

RAISE_APPLICATION_ERROR(-20002, '订单已入库，不可修改');

END IF;

END;