CREATE OR REPLACE TRIGGER SFCS.EF_STANDARD_WORK_T
AFTER INSERT OR UPDATE
ON SFCS.EF_STANDARD_WORK_HOURS
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN (NEW.GROUP_ID IN (18,19,23,25,57))
DECLARE
tmpVar NUMBER;
models varchar(40);
Part_no varchar(20);
counts1 number;
counts2 number;
standard_work_time number;
CURSOR C_partno ;/******************************************************************************
NAME: EF_STANDARD_WORK_T
PURPOSE: REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2008/8/29 1. Created this trigger. NOTES: Automatically available Auto Replace Keywords:
Object Name: EF_STANDARD_WORK_T
Sysdate: 2008/8/29
Date and Time: 2008/8/29, 上午 08:02:49, and 2008/8/29 上午 08:02:49
Username: (set in TOAD Options, Proc Templates)
Table Name: EF_STANDARD_WORK_HOURS (set in the "New PL/SQL Object" dialog)
Trigger Options: (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
if new.group_id in (18,19,23) then
select sum(standard_work_time) into standard_work_time
from ef_standard_work_hours
where model=:new.model
and group_id in (18,19,23);
select nvl(count(*),0) into counts2 from inv_pn where model=:new.model and SUBSTR (part_no, 1, 2)='70';
if counts2>0 then
CURSOR C_partno is select part_no from inv_pn where model=:new.model and SUBSTR (part_no, 1, 2)='70';
OPEN c_partno;
LOOP
FETCH c_partno INTO part_no;
EXIT WHEN c_partno% NOTFOUND;
insert into ef_standard_work values (ERP_STANDARD_WORK_sequence.nextval,:new.model,part_no,standard_work_time,3,sysdate,sysdate,0);
end loop;
close c_partno;
end if;
else
begin
select sum(standard_work_time) into standard_work_time
from ef_standard_work_hours
where model=:new.model
and group_id in (25,27);
SELECT nvl(count(*),0) into counts1
FROM ef_standard_work
WHERE model = :new.model
AND SUBSTR (part_no, 1, 2)='95'
and a.model= :new.model;
select nvl(count(*),0) into counts2
from inv_pn
where model=:new.model
and SUBSTR (part_no, 1, 2)='95';
if counts2>0 then
declare
CURSOR C_partno is select part_no from inv_pn where model=:new.model and SUBSTR (part_no, 1, 2)='95';
begin
OPEN c_partno;
LOOP
FETCH c_partno INTO part_no;
EXIT WHEN c_partno% NOTFOUND;
insert into ef_standard_work values
(ERP_STANDARD_WORK_sequence.nextval,:new.model,part_no,standard_work_time,3,sysdate,sysdate,0);
end loop;
close c_partno;
end;
end if;
end;
end if; tmpVar := 0;
ELECT MySeq.NEXTVAL INTO tmpVar FROM dual;
:NEW.SequenceColumn := tmpVar;
:NEW.CreatedDate := SYSDATE;
:NEW.CreatedUser := USER; EXCEPTION
WHEN OTHERS THEN
RAISE;
END EF_STANDARD_WORK_T;
AFTER INSERT OR UPDATE
ON SFCS.EF_STANDARD_WORK_HOURS
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN (NEW.GROUP_ID IN (18,19,23,25,57))
DECLARE
tmpVar NUMBER;
models varchar(40);
Part_no varchar(20);
counts1 number;
counts2 number;
standard_work_time number;
CURSOR C_partno ;/******************************************************************************
NAME: EF_STANDARD_WORK_T
PURPOSE: REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2008/8/29 1. Created this trigger. NOTES: Automatically available Auto Replace Keywords:
Object Name: EF_STANDARD_WORK_T
Sysdate: 2008/8/29
Date and Time: 2008/8/29, 上午 08:02:49, and 2008/8/29 上午 08:02:49
Username: (set in TOAD Options, Proc Templates)
Table Name: EF_STANDARD_WORK_HOURS (set in the "New PL/SQL Object" dialog)
Trigger Options: (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
if new.group_id in (18,19,23) then
select sum(standard_work_time) into standard_work_time
from ef_standard_work_hours
where model=:new.model
and group_id in (18,19,23);
select nvl(count(*),0) into counts2 from inv_pn where model=:new.model and SUBSTR (part_no, 1, 2)='70';
if counts2>0 then
CURSOR C_partno is select part_no from inv_pn where model=:new.model and SUBSTR (part_no, 1, 2)='70';
OPEN c_partno;
LOOP
FETCH c_partno INTO part_no;
EXIT WHEN c_partno% NOTFOUND;
insert into ef_standard_work values (ERP_STANDARD_WORK_sequence.nextval,:new.model,part_no,standard_work_time,3,sysdate,sysdate,0);
end loop;
close c_partno;
end if;
else
begin
select sum(standard_work_time) into standard_work_time
from ef_standard_work_hours
where model=:new.model
and group_id in (25,27);
SELECT nvl(count(*),0) into counts1
FROM ef_standard_work
WHERE model = :new.model
AND SUBSTR (part_no, 1, 2)='95'
and a.model= :new.model;
select nvl(count(*),0) into counts2
from inv_pn
where model=:new.model
and SUBSTR (part_no, 1, 2)='95';
if counts2>0 then
declare
CURSOR C_partno is select part_no from inv_pn where model=:new.model and SUBSTR (part_no, 1, 2)='95';
begin
OPEN c_partno;
LOOP
FETCH c_partno INTO part_no;
EXIT WHEN c_partno% NOTFOUND;
insert into ef_standard_work values
(ERP_STANDARD_WORK_sequence.nextval,:new.model,part_no,standard_work_time,3,sysdate,sysdate,0);
end loop;
close c_partno;
end;
end if;
end;
end if; tmpVar := 0;
ELECT MySeq.NEXTVAL INTO tmpVar FROM dual;
:NEW.SequenceColumn := tmpVar;
:NEW.CreatedDate := SYSDATE;
:NEW.CreatedUser := USER; EXCEPTION
WHEN OTHERS THEN
RAISE;
END EF_STANDARD_WORK_T;
解决方案 »
- 怎么更新一个为null的值?
- 在最外层使用select * from 会降低性能吗
- oracle中对树形结构的查询,我想除了得到一列显示起始节点到当前节点的完整路径path外,还要有一列class2,如何实现?
- 控制文件时间不一致等问题如何解决啊?
- 将SQLSERVER存储过程改为ORACLE的存储过程
- 调用过程出错,大家来看看!
- 菜鸟问题:网站要发布了,数据库怎么迁移/发布?
- 具有sysdba权限的用户sys登陆oracle817时为何无法用sysdba连接?
- 开始学oracle看哪本书比较好?怎么入门?
- Oracle单行子查询返回多于一个行
- 求教,我想从所有表中查一个数据怎么办?
- 求一SQL,二级序号的问题
原因是存在双字节的空格和有个new没有给":"
修改如下:
CREATE OR REPLACE TRIGGER SFCS.EF_STANDARD_WORK_T
AFTER INSERT OR UPDATE ON SFCS.EF_STANDARD_WORK_HOURS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (NEW.GROUP_ID IN (18, 19, 23, 25, 57))
DECLARE
TMPVAR NUMBER;
MODELS VARCHAR(40);
PART_NO VARCHAR(20);
COUNTS1 NUMBER;
COUNTS2 NUMBER;
STANDARD_WORK_TIME NUMBER;
CURSOR C_PARTNO;
BEGIN
IF :NEW.GROUP_ID IN (18, 19, 23) THEN
SELECT SUM(STANDARD_WORK_TIME)
INTO STANDARD_WORK_TIME
FROM EF_STANDARD_WORK_HOURS
WHERE MODEL = :NEW.MODEL
AND GROUP_ID IN (18, 19, 23);
SELECT NVL(COUNT(*), 0)
INTO COUNTS2
FROM INV_PN
WHERE MODEL = :NEW.MODEL
AND SUBSTR(PART_NO, 1, 2) = '70';
IF COUNTS2 > 0 THEN
CURSOR C_PARTNO IS
SELECT PART_NO
FROM INV_PN
WHERE MODEL = :NEW.MODEL
AND SUBSTR(PART_NO, 1, 2) = '70';
OPEN C_PARTNO;
LOOP
FETCH C_PARTNO
INTO PART_NO;
EXIT WHEN C_PARTNO% NOTFOUND;
INSERT INTO EF_STANDARD_WORK
VALUES
(ERP_STANDARD_WORK_SEQUENCE.NEXTVAL,
:NEW.MODEL,
PART_NO,
STANDARD_WORK_TIME,
3,
SYSDATE,
SYSDATE,
0);
END LOOP;
CLOSE C_PARTNO;
END IF;
ELSE
BEGIN
SELECT SUM(STANDARD_WORK_TIME)
INTO STANDARD_WORK_TIME
FROM EF_STANDARD_WORK_HOURS
WHERE MODEL = :NEW.MODEL
AND GROUP_ID IN (25, 27);
SELECT NVL(COUNT(*), 0)
INTO COUNTS1
FROM EF_STANDARD_WORK
WHERE MODEL = :NEW.MODEL
AND SUBSTR(PART_NO, 1, 2) = '95'
AND A.MODEL = :NEW.MODEL;
SELECT NVL(COUNT(*), 0)
INTO COUNTS2
FROM INV_PN
WHERE MODEL = :NEW.MODEL
AND SUBSTR(PART_NO, 1, 2) = '95';
IF COUNTS2 > 0 THEN
DECLARE
CURSOR C_PARTNO IS
SELECT PART_NO
FROM INV_PN
WHERE MODEL = :NEW.MODEL
AND SUBSTR(PART_NO, 1, 2) = '95';
BEGIN
OPEN C_PARTNO;
LOOP
FETCH C_PARTNO
INTO PART_NO;
EXIT WHEN C_PARTNO% NOTFOUND;
INSERT INTO EF_STANDARD_WORK
VALUES
(ERP_STANDARD_WORK_SEQUENCE.NEXTVAL,
:NEW.MODEL,
PART_NO,
STANDARD_WORK_TIME,
3,
SYSDATE,
SYSDATE,
0);
END LOOP;
CLOSE C_PARTNO;
END;
END IF;
END;
END IF; TMPVAR := 0;
ELECT MYSEQ.NEXTVAL
INTO TMPVAR FROM DUAL;
:NEW.SEQUENCECOLUMN := TMPVAR;
:NEW.CREATEDDATE := SYSDATE;
:NEW.CREATEDUSER := USER;EXCEPTION
WHEN OTHERS THEN
RAISE;
END EF_STANDARD_WORK_T;
/
這個new,要冒號?
還有,你說的雙字節問題在哪
xxx FETCH c_partno INTO part_no;
xx EXIT WHEN c_partno% NOTFOUND;
自治事务触发器:
CREATE OR REPLACE TRIGGER SFCS.EF_STANDARD_WORK_T
AFTER INSERT OR UPDATE ON SFCS.EF_STANDARD_WORK_HOURS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (NEW.GROUP_ID IN (18, 19, 23, 25, 57))
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
TMPVAR NUMBER;
MODELS VARCHAR(40);
PART_NO VARCHAR(20);
COUNTS1 NUMBER;
COUNTS2 NUMBER;
STANDARD_WORK_TIME NUMBER;
CURSOR C_PARTNO;
BEGIN
IF :NEW.GROUP_ID IN (18, 19, 23) THEN
SELECT SUM(STANDARD_WORK_TIME)
INTO STANDARD_WORK_TIME
FROM EF_STANDARD_WORK_HOURS
WHERE MODEL = :NEW.MODEL
AND GROUP_ID IN (18, 19, 23);
SELECT NVL(COUNT(*), 0)
INTO COUNTS2
FROM INV_PN
WHERE MODEL = :NEW.MODEL
AND SUBSTR(PART_NO, 1, 2) = '70';
IF COUNTS2 > 0 THEN
CURSOR C_PARTNO IS
SELECT PART_NO
FROM INV_PN
WHERE MODEL = :NEW.MODEL
AND SUBSTR(PART_NO, 1, 2) = '70';
OPEN C_PARTNO;
LOOP
FETCH C_PARTNO
INTO PART_NO;
EXIT WHEN C_PARTNO% NOTFOUND;
INSERT INTO EF_STANDARD_WORK
VALUES
(ERP_STANDARD_WORK_SEQUENCE.NEXTVAL,
:NEW.MODEL,
PART_NO,
STANDARD_WORK_TIME,
3,
SYSDATE,
SYSDATE,
0);
END LOOP;
CLOSE C_PARTNO;
END IF;
ELSE
BEGIN
SELECT SUM(STANDARD_WORK_TIME)
INTO STANDARD_WORK_TIME
FROM EF_STANDARD_WORK_HOURS
WHERE MODEL = :NEW.MODEL
AND GROUP_ID IN (25, 27);
SELECT NVL(COUNT(*), 0)
INTO COUNTS1
FROM EF_STANDARD_WORK
WHERE MODEL = :NEW.MODEL
AND SUBSTR(PART_NO, 1, 2) = '95'
AND A.MODEL = :NEW.MODEL;
SELECT NVL(COUNT(*), 0)
INTO COUNTS2
FROM INV_PN
WHERE MODEL = :NEW.MODEL
AND SUBSTR(PART_NO, 1, 2) = '95';
IF COUNTS2 > 0 THEN
DECLARE
CURSOR C_PARTNO IS
SELECT PART_NO
FROM INV_PN
WHERE MODEL = :NEW.MODEL
AND SUBSTR(PART_NO, 1, 2) = '95';
BEGIN
OPEN C_PARTNO;
LOOP
FETCH C_PARTNO
INTO PART_NO;
EXIT WHEN C_PARTNO% NOTFOUND;
INSERT INTO EF_STANDARD_WORK
VALUES
(ERP_STANDARD_WORK_SEQUENCE.NEXTVAL,
:NEW.MODEL,
PART_NO,
STANDARD_WORK_TIME,
3,
SYSDATE,
SYSDATE,
0);
END LOOP;
CLOSE C_PARTNO;
END;
END IF;
END;
END IF; TMPVAR := 0;
ELECT MYSEQ.NEXTVAL
INTO TMPVAR FROM DUAL;
:NEW.SEQUENCECOLUMN := TMPVAR;
:NEW.CREATEDDATE := SYSDATE;
:NEW.CREATEDUSER := USER;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END EF_STANDARD_WORK_T;
/