1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
| create or replace
TRIGGER SPEC_ORDER
AFTER UPDATE OF ORDER ON STATUS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
WHEN (new.status = 3)
DECLARE
-- Toutes les variables sont de type number
v_spec1_id spec.spec1_id%TYPE;
v_spec2_id spec.spec2_id%TYPE;
v_spec_status spec.status%TYPE;
CURSOR spec1 IS
SELECT id
FROM topology
WHERE id = :old.spec1_id
ORDER BY id;
BEGIN
OPEN spec1;
dbms_output.put_line('Phase 1 after open spec1' );
LOOP
dbms_output.put_line('Phase 2 inside loop' );
FETCH spec INTO v_spec1_id;
dbms_output.put_line('Phase 3 the content of v_spec1_id :' ||v_spec1_id);
EXIT WHEN spec1%NOTFOUND;
SELECT id, status
INTO v_spec1_id, v_spec_status
FROM spec
WHERE spec_id = v_spec1_id;
dbms_output.put_line('Phase1 Inside the third part of trigger v_spec_status :' ||v_spec_status);
IF v_spec_status = 6 THEN
INSERT INTO outside_order ( order_id,
sla_id,
order_type,
order_from,
order_dt,
order_status,
expected_delivery_dt,
external_ref,
commercial_id,
qty,
spec1_id,
pre_id,
p_spec_id,
p_pre_id,
spec2_id,
production_location,
delivered_to,
actual_delivery_dt,
commentry)
VALUES
(order_id_seq.nextval,
2,
'INTERN',
'Database',
sysdate,
'IN PROGRESS',
NULL,
NULL,
NULL,
NULL,
v_spec1_id,
NULL,
NULL,
NULL,
:old.spec_id,
'TEST',
NULL,
NULL,
NULL);
END IF;
END LOOP;
END; |
Partager