带大小写和变量的oracle after触发器

llew8vvj 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(367)

我正在尝试建立一个触发器，插入后更新列 ID_TREE 根据一些标准：
表dod\U ct\l

RID VARCHAR2(16) --PK
ID_TREE NUMBER(16)
FLAGS_S VARCHAR2(254)
DAT_OD DATE(7) --> FROM
DAT_DO DATE(7) --> TO

表dod\ U ct\ o

RID_O NUMBER(16) --PK
ID_R NUMBER(16) --PK
KOD_ID NUMBER(22) --FK
RID_CT_O VARCHAR2(16)

表ct\ o

RID_OBJ VARCHAR2(16) --PK
KOD_ID NUMBER(22) --FK
S_STAMP VARCHAR(254)
TYP NUMBER (22)
NAVOZ_OD DATE(7) --> FROM
NAVOZ_DO DATE(7) --> TO

所以我想在table上扣个扳机 DOD_CT_L 它将更新它并设置列 ID_TREE 至 4401 或者 4402 . 标准到 4401 ：项目( kod_id )这个上校：( DOD_CT_O.RID_CT_O = CT_O.RID_OBJ )，必须匹配。如果为真，则设置新值。
试验计划：

CREATE OR REPLACE TRIGGER DOD_CT_L_TREE
AFTER INSERT OR UPDATE on DOD_CT_L
FOR EACH ROW

DECLARE

ct_count number(2)

when (new.FLAGS_S in (0,4) )

BEGIN

select 1 into ct_count from dod_ct_o o where o.rid_ct_o in (
 select rid_obj from ct_o where kod_id = o.kod_id and s_stamp = '0' and typ in (10,11) 
 and ( dat_do Is Null Or dat_do >= trunc(Sysdate) ) group by rid_obj) group by 1;

:new.id_tree :=
 CASE
  WHEN  :new.ct_count > 0 and (:new.NAVOZ_DO > trunc(sysdate) or :new.NAVOZ_DO is null) 
   then '4401'
  WHEN  :new.ct_count >= 0 and :new.NAVOZ_DO <= trunc(sysdate) then '4402'

 END;
END;
/

我还没测试过，但它看起来像是可运行的吗(我不这么认为。）

sql oracle triggers

来源：https://stackoverflow.com/questions/62063036/oracle-after-trigger-with-case-and-variable

1条答案

按热度按时间

cvxl0en21#

代码中存在多个问题。
when子句的位置不正确
ct\U计数（2）后缺少逗号
使用 :new 与 CT_COUNT . :new 仅与创建触发器的表的列一起使用
您需要使用以下代码（请参阅内联注解）：

CREATE OR REPLACE TRIGGER DOD_CT_L_TREE AFTER
    INSERT OR UPDATE ON DOD_CT_L
    FOR EACH ROW
WHEN ( NEW.FLAGS_S IN ( 0,4) ) -- position of WHEN clause was worng in your code
DECLARE 
CT_COUNT NUMBER(2); -- missing comma
BEGIN
    SELECT COUNT(1) -- use count function and removed GROUP BY 1
    INTO CT_COUNT
    FROM DOD_CT_O O
    WHERE
        O.RID_CT_O IN (
            SELECT RID_OBJ
            FROM CT_O
            WHERE KOD_ID = O.KOD_ID
                AND S_STAMP = '0'
                    AND TYP IN (10, 11)
                        AND ( DAT_DO IS NULL
                              OR DAT_DO >= TRUNC(SYSDATE) )
            GROUP BY RID_OBJ
        );

    :NEW.ID_TREE :=
        CASE --:new from :new.CT_COUNT is not correct, use only CT_COUNT
            WHEN CT_COUNT > 0 AND ( :NEW.NAVOZ_DO > TRUNC(SYSDATE) OR :NEW.NAVOZ_DO IS NULL ) THEN
                '4401'
            WHEN CT_COUNT >= 0 AND :NEW.NAVOZ_DO <= TRUNC(SYSDATE) THEN
                '4402'
        END;

END;
/

赞(0）回复(0）举报 2021-08-13

我来回答

带大小写和变量的oracle after触发器

1条答案

相关问题

热门标签

最新问答