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;
原因是存在双字节的空格和有个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;
/