如题,请问下面的写法可不可以:create or replace trigger BK_MTL_TRANSACTIONS_001
before insert on SJH_BBK_001 for each row
pragma autonomous_transaction; --将触发器声明为自治事务
declare
attr1 varchar2(20);
attr2 varchar2(20);
attr3 varchar2(20);
attr4 varchar2(20);
len number;
CNT NUMBER;
begin
--判断外协工单抛出的P/R转成的P/O在接收事务处理中的类型是否为入库
SELECT COUNT(*) INTO CNT FROM RCV_TRANSACTIONS
WHERE WIP_ENTITY_ID= :new.TRANSACTION_SOURCE_ID
AND TRANSACTION_TYPE='DELIVER'; IF(CNT=0) THEN RETURN; END IF;
SELECT NVL(M.ATTRIBUTE3, ''), NVL(M.ATTRIBUTE2, '9'), NVL(M.ATTRIBUTE3, '0') INTO ATTR1,ATTR2,ATTR3
FROM MTL_TRANSACTION_TYPES M
WHERE TRANSACTION_TYPE_ID = :NEW.TRANSACTION_TYPE_ID;--取出编码信息 LEN:=TO_NUMBER(ATTR2)-LENGTH(ATTR1)-4;--计算出流水号位数
ATTR3:=TO_CHAR(TO_NUMBER(ATTR3)+1);--当前流水号加一
ATTR4:=ATTR1||TO_CHAR(SYSDATE,'YYMM')||LPAD(ATTR3,LEN,'0'); --产生编码
UPDATE MTL_TRANSACTION_TYPES SET ATTRIBUTE3=ATTR3
WHERE TRANSACTION_TYPE_ID=:NEW.TRANSACTION_TYPE_ID;---更新当前流水号
COMMIT; -- allowed only in autonomous triggers :NEW.ATTRIBUTE5:=ATTR4; end;
before insert on SJH_BBK_001 for each row
pragma autonomous_transaction; --将触发器声明为自治事务
declare
attr1 varchar2(20);
attr2 varchar2(20);
attr3 varchar2(20);
attr4 varchar2(20);
len number;
CNT NUMBER;
begin
--判断外协工单抛出的P/R转成的P/O在接收事务处理中的类型是否为入库
SELECT COUNT(*) INTO CNT FROM RCV_TRANSACTIONS
WHERE WIP_ENTITY_ID= :new.TRANSACTION_SOURCE_ID
AND TRANSACTION_TYPE='DELIVER'; IF(CNT=0) THEN RETURN; END IF;
SELECT NVL(M.ATTRIBUTE3, ''), NVL(M.ATTRIBUTE2, '9'), NVL(M.ATTRIBUTE3, '0') INTO ATTR1,ATTR2,ATTR3
FROM MTL_TRANSACTION_TYPES M
WHERE TRANSACTION_TYPE_ID = :NEW.TRANSACTION_TYPE_ID;--取出编码信息 LEN:=TO_NUMBER(ATTR2)-LENGTH(ATTR1)-4;--计算出流水号位数
ATTR3:=TO_CHAR(TO_NUMBER(ATTR3)+1);--当前流水号加一
ATTR4:=ATTR1||TO_CHAR(SYSDATE,'YYMM')||LPAD(ATTR3,LEN,'0'); --产生编码
UPDATE MTL_TRANSACTION_TYPES SET ATTRIBUTE3=ATTR3
WHERE TRANSACTION_TYPE_ID=:NEW.TRANSACTION_TYPE_ID;---更新当前流水号
COMMIT; -- allowed only in autonomous triggers :NEW.ATTRIBUTE5:=ATTR4; end;
:NEW.ATTRIBUTE5:=ATTR4;