oracle 触发器,拆单,求问 5C
 CREATE OR REPLACE TRIGGER SPLITSINGLETRIGGER
  BEFORE INSERT ON CT_CUS_GDSHOPGUIDBILLENTRY
  FOR EACH ROW
DECLARE
  V_NEWID VARCHAR2(50);
  V_NUMBER VARCHAR2(50);
BEGIN
  SELECT FID
    INTO V_NEWID
    FROM CREFID
   WHERE FSTATE = 1
     AND ROWNUM = 1;
      SELECT ABS(MOD(DBMS_RANDOM.RANDOM,50)) INTO V_NUMBER FROM DUAL;
  IF :NEW.FSEQ >= 2 THEN
    --当选择的商品数大于等于2 ,给表头插入新的一条数据
    INSERT INTO CT_CUS_GDSHOPGUIDBILL
      SELECT FCREATORID,
             FCREATETIME,
             FLASTUPDATEUSERID,
             FLASTUPDATETIME,
             FCONTROLUNITID,
             FNUMBER || TO_CHAR(V_NUMBER), --修改FNUMBER
             FBIZDATE,
             FHANDLERID,
             FDESCRIPTION,
             FHASEFFECTED,
             FAUDITORID,
             FSOURCEBILLID,
             FSOURCEFUNCTION,
             V_NEWID, --修改FID
             FFIVOUCHERED,
             CFFICOMPANYID,
             CFGUIDERNAMEID,
             CFGUIDERNAMESHOW,
             CFGUIDERID,
             CFBILLTYPE,
             CFBILLSTATE,
             CFSGBILLSTATES,
             CFGDSHOPGUIDERNUM,
             CFORGANIZATIONUNIT,
             CFAUDITDATE,
             CFDELIVERYORDER,
             CFCONFIRM,
             CFTOREVIEWDATE,
             CFCONFIRMDATE,
             CFTOREVIEW,
             FENTITYCUSTOMER,
             FENTITYCUSTOMERADDRESS,
             FENTITYCUSTOMERTEL,
             CFBUSFEEDBACK,
             CFFINFEEDBACK,
             CFYANZHENGMA,
             CFOSMDANHAO
        FROM CT_CUS_GDSHOPGUIDBILL
       WHERE FID = :NEW.FPARENTID;
    :NEW.FPARENTID := V_NEWID;
    :NEW.FSEQ      := 1;
    UPDATE CREFID SET FSTATE = 2 WHERE FID = V_NEWID;--修改state
  END IF;
END;

触发器现在是生成随机数,不过还是FNUMBER有可能会重复
提交商品不止一个的话本来是想给fnumber加上一个有顺序的数字
比如拆两条,有一条fnumber+2
拆三条,一条+2,一条+3
但是传过来的fseq(本来是序号)可能由于先拆两单,再拆导致序号不固定,
出现重复的fnumber,有什么办法,让fnumber可以按顺序来,

0

3个回答

可以用 ROW_NUMBER() OVER(ORDER BY FNUMBER) 生成连接的序号
或用 row_number() OVER (PARTITION BY FNUMBER ORDER BY FNUMBER) +FNUMBER 做序号

0
wjy407199
半城风月半城雪 这没用啊,里面数据那么多,怎么知道哪条是自己增的
一年多之前 回复

如果不想他重复且自动累加,可以借助oracle 的序列sequence,定义个序列,每次获取nextval

0

SEQUENCE中添加一个自定义序列,然后用FNUMBER获取序列值。

0
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!