Home » SQL & PL/SQL » SQL & PL/SQL » Complex trigger (Oracle 10.0.2.1)
| Complex trigger [message #553460] |
Fri, 04 May 2012 14:52  |
|
|
I have to re write a trigger to delete the data from one table which has relevant records in three other tables for example,i have tables ot_req,ot_po,ot_po_breakup,ot_ship,ot_ship_breakup.
ot_req is a requesition , where it stores information about what needs to be procured,ot_po is purchase order table which retrieves data based on ot_Req , whenever there is a data inserted into ot_po automatically the data in ot_po_breakup gets populated automatically.
user will enter the details of goods that will be comming in ot_ship(stores information on what materials will be comming ,goods in transit) and whenever the user enters the data into this table ,data in ot_ship_breakup gets populated automatically and gets deleted whenever the record is deleted from ot_ship.
The problem is whenever i delete the record from ot_ship,the record will also get deleted from ot_ship_breakup and hence i cannot remove values from ot_po_breakup which are based on ot_ship and ot_ship_breakup i tried but its failing.
SCOTT@orcl_11gR2> CREATE TABLE OT_REQ
2 (
3 RI_ITEM VARCHAR2(20 BYTE),
4 RI_SYS_ID NUMBER,
5 RI_QTY NUMBER
6 )
7 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE OT_PO
2 (
3 PI_ITEM VARCHAR2(12 BYTE),
4 PI_QTY NUMBER,
5 PI_SYS_ID NUMBER
6 )
7 /
Table created
SCOTT@orcl_11gR2> CREATE TABLE OT_PO_BREAKUP
2 (
3 PB_RI_SYS_ID NUMBER,
4 PB_PI_SYS_ID NUMBER,
5 PB_RI_QTY NUMBER,
6 PB_VES1Q NUMBER,
7 PB_VES2Q NUMBER,
8 PB_VES3Q NUMBER,
9 PRB_SYS_ID NUMBER
10 )
11 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE OT_SHIP
2 (
3 SI_ITEM VARCHAR2(12 BYTE),
4 SI_QTY NUMBER,
5 SI_SYS_ID NUMBER
6 )
7 /
SCOTT@orcl_11gR2> CREATE TABLE OT_SHIP_BREAKUP
2 (
3 SB_RI_SYS_ID NUMBER,
4 SB_PRB_SYS_ID NUMBER,
5 SB_REF_QTY NUMBER,
6 )
7 /
Table created.
SCOTT@orcl_11gR2> insert into ot_req values ('aa',1,20)
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> SELECT * FROM ot_req
2 /
RI_ITEM RI_SYS_ID RI_QTY
-------------------- ---------- ----------
aa 1 20
--Whenever a record gets inserted into ot_po automatically ot_po_breakup gets populated based on data from ot_po.pi_sys_id
and ot_req.ri_sys_id as below
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER ot_PO_AFT
2 AFTER INSERT ON ot_PO FOR EACH ROW
3 BEGIN
4 INSERT INTO ot_po_breakup (pb_ri_sys_id, pb_ri_qty, pb_pi_sys_id,prb_sys_id)
5 VALUES (:NEW.ri_sys_id, :NEW.ri_qty, :NEW.PI_SYS_ID,prb_sys_id.nextval);
6 END ot_PO_AFT;
9 /
SCOTT@orcl_11gR2> INSERT INTO OT_PO values('aa',10,33)
2 /
1 row created.
SCOTT@orcl_11gR2> INSERT INTO OT_PO VALUES ('aa',10,34)
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
SCOTT@orcl_11gR2> SELECT * FROM ot_po_breakup
2 /
PB_RI_SYS_ID PB_PI_SYS_ID PB_RI_QTY PB_VES1Q PB_VES2Q PB_VES3Q PRB_SYS_ID
------------ ------------ ---------- ---------- ---------- ---------- ----------
1 33 20 1
1 34 20 2
2 rows selected.
-- now the user will enter the data in ot_ship based on first po that is for pi_sys_id 33
SCOTT@orcl_11gR2> INSERT INTO OT_ship values('aa',10,44)
2 /
1 row created.
--trigger to populate ot_ship_breakup where there will data stored automatically and will be deleted when the record is
--deleted from ot_ship table
SCOTT@orcl_11gR2> SELECT * FROM ot_ship_breakup
2 /
SB_RI_SYS_ID SB_PRB_SYS_ID SB_REF_QTY SB_SI_SYS_ID
------------ ------------ ---------- ------------
1 33 10 44
1 row selected.
--the problem is whenever i delete the record from ot_ship,the record will also get deleted from ot_ship_breakup and hence i cannot remove values from ot_po_breakup which are based on ot_ship and ot_ship_breakup i tried but its failing.
/* Formatted on 2012/05/04 23:13 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TRIGGER ot_ship_del
AFTER DELETE
ON ot_ship
FOR EACH ROW
DECLARE
v_pi_qty NUMBER;
v_least NUMBER;
v_pb_ri_qty NUMBER;
v_sys_id NUMBER;
CURSOR c2
IS
SELECT sb_ri_sys_id, sb_prb_sys_id
FROM ot_ship_breakup
WHERE sb_si_sys_id = :OLD.si_sys_id;
BEGIN
v_pi_qty := NVL (:OLD.si_qty, 0);
v_sys_id := :OLD.si_sys_id; -- quantity to remove
-- remove old values:
FOR s IN c2
LOOP
FOR r IN (SELECT *
FROM ot_po_breakup
WHERE pb_ri_sys_id = s.sb_ri_sys_id
AND prb_sys_id = s.sb_prb_sys_id)
LOOP
IF v_pi_qty > 0
THEN
v_least := LEAST (v_pi_qty, NVL (r.pb_ves3q, 0));
UPDATE ot_po_breakup
SET pb_ves3q = pb_ves3q - v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
v_pi_qty := v_pi_qty - v_least;
v_least := LEAST (v_pi_qty, NVL (r.pb_ves2q, 0));
UPDATE ot_po_breakup
SET pb_ves2q = pb_ves2q - v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
v_pi_qty := v_pi_qty - v_least;
v_least := LEAST (v_pi_qty, NVL (r.pb_ves1q, 0));
UPDATE ot_po_breakup
SET pb_ves1q = pb_ves1q - v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
v_pi_qty := v_pi_qty - v_least;
END IF;
END LOOP;
END LOOP;
END ot_ship_del;
[EDITED by LF: removed superfluous empty lines]
[Updated on: Fri, 04 May 2012 17:19] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Complex trigger [message #553473 is a reply to message #553460] |
Fri, 04 May 2012 19:11  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You need to stop faking output and do a real copy and paste.
The following cannot be a real copy and paste, because you have a comma after the last column, so it could not create a table.
SCOTT@orcl_11gR2> CREATE TABLE OT_SHIP_BREAKUP
2 (
3 SB_RI_SYS_ID NUMBER,
4 SB_PRB_SYS_ID NUMBER,
5 SB_REF_QTY NUMBER,
6 )
7 /
Table created.
The following is invalid based on the table structure that you posted, because there isn't any ri_sys_id or ri_qty in the ot_po table.
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER ot_PO_AFT
2 AFTER INSERT ON ot_PO FOR EACH ROW
3 BEGIN
4 INSERT INTO ot_po_breakup (pb_ri_sys_id, pb_ri_qty, pb_pi_sys_id,prb_sys_id)
5 VALUES (:NEW.ri_sys_id, :NEW.ri_qty, :NEW.PI_SYS_ID,prb_sys_id.nextval);
6 END ot_PO_AFT;
9 /
You say that your problem is with a delete, but you have not posted any delete statement or what happens when you run it or what you want to happen when you run it.
|
|
|
|
| Re: Complex trigger [message #553505 is a reply to message #553473] |
Sat, 05 May 2012 14:21 |
|
|
Hi i am really sorry for faking those statements, i have a real test case as below but the problem is there is a trigger written at form level which deletes the data from ot_ship_breakup before my ot_ship_del trigger fires and nothing happens.Please check the code below and one more problem is while updating on ot_po i need to change or update the ot_po.pi_qty values then only that trigger ot_po_air gets fired but i want that trigger to do the work automatically because the data in ot_po will get populated based on form trigger . can we combine this insert and update together.
SQL> CREATE TABLE ot_req
2 (
3 ri_item VARCHAR2(20 BYTE),
4 ri_sys_id NUMBER,
5 ri_qty NUMBER
6 )
7 /
Table created.
SQL> INSERT INTO OT_REQ VALUES ('AA',1,20);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> CREATE TABLE ot_po
2 (
3 pi_item VARCHAR2(12 BYTE),
4 pi_qty NUMBER,
5 pi_sys_id NUMBER
6 );
Table created.
SQL> CREATE SEQUENCE pb_sys_id
2 START WITH 1
3 MAXVALUE 999999999999999999999999999
4 MINVALUE 1
5 NOCYCLE
6 CACHE 20
7 NOORDER;
Sequence created.
SQL> CREATE TABLE OT_PO_BREAKUP
2 (
3 PB_RI_SYS_ID NUMBER,
4 PB_PI_SYS_ID NUMBER,
5 PB_RI_QTY NUMBER,
6 PB_VES1Q NUMBER,
7 PB_VES2Q NUMBER,
8 PB_VES3Q NUMBER,
9 PB_SYS_ID NUMBER
10 )
11 /
Table created.
SQL> CREATE OR REPLACE TRIGGER ot_po_aft
2 AFTER INSERT
3 ON ot_po
4 FOR EACH ROW
5 DECLARE
6 m_val NUMBER;
7
8 CURSOR c1
9 IS
10 SELECT ri_sys_id, ri_qty,ri_item
11 FROM ot_req
12 WHERE ri_item = :NEW.pi_item;
13
14 BEGIN
15 SELECT pb_sys_id.NEXTVAL
16 INTO m_val
17 FROM DUAL;
18 FOR I IN C1
19 LOOP
20 INSERT INTO ot_po_breakup
21 VALUES (I.RI_sys_id, :NEW.pi_sys_id, I.RI_QTY, NULL, NULL, NULL, M_VAL);
22 END LOOP;
23 END;
24 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER ot_po_air
2 AFTER update
3 ON ot_po
4 FOR EACH ROW
5 DECLARE
6 v_pi_qty NUMBER := NVL (:NEW.pi_qty, 0); -- quantity to distribute
7 v_pb_ri_qty NUMBER; -- space available
8 v_least NUMBER;
9 -- least of quantity to distribute and space available
10 BEGIN
11 -- fill ot_po_breakup rows in order of pb_ri_sys_id:
12 FOR r IN (SELECT *
13 FROM ot_po_breakup
14 ORDER BY pb_ri_sys_id)
15 LOOP
16 -- calculate currently available space in this row:
17 v_pb_ri_qty :=
18 NVL (r.pb_ri_qty, 0)
19 - NVL (r.pb_ves1q, 0)
20 - NVL (r.pb_ves2q, 0)
21 - NVL (r.pb_ves3q, 0);
22 -- calculate least of quantity to distribute and space available:
23 v_least := LEAST (v_pi_qty, v_pb_ri_qty);
24
25 -- fill vessels with above qantity in order: pb_ves1q, pb_ves2q, pb_ves3q:
26 IF v_least > 0
27 THEN
28 IF NVL (r.pb_ves1q, 0) = 0
29 THEN
30 UPDATE ot_po_breakup
31 SET pb_ves1q = v_least
32 WHERE pb_ri_sys_id = r.pb_ri_sys_id
33 AND pb_pi_sys_id = r.pb_pi_sys_id;
34 ELSIF NVL (r.pb_ves2q, 0) = 0
35 THEN
36 UPDATE ot_po_breakup
37 SET pb_ves2q = v_least
38 WHERE pb_ri_sys_id = r.pb_ri_sys_id
39 AND pb_pi_sys_id = r.pb_pi_sys_id;
40 ELSIF NVL (r.pb_ves3q, 0) = 0
41 THEN
42 UPDATE ot_po_breakup
43 SET pb_ves3q = v_least
44 WHERE pb_ri_sys_id = r.pb_ri_sys_id
45 AND pb_pi_sys_id = r.pb_pi_sys_id;
46 END IF;
47
48 -- update quantity to distribute:
49 v_pi_qty := v_pi_qty - v_least;
50 END IF;
51 END LOOP;
52 END ot_po_air;
53 /
Trigger created.
SQL> insert into ot_po values('AA',10,33);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM OT_PO;
PI_ITEM PI_QTY PI_SYS_ID
------------ ---------- ----------
AA 10 33
SQL> SELECT * FROM OT_PO_BREAKUP;
PB_RI_SYS_ID PB_PI_SYS_ID PB_RI_QTY PB_VES1Q PB_VES2Q PB_VES3Q PB_SYS_ID
------------ ------------ ---------- ---------- ---------- ---------- ----------
1 33 20 10 2
SQL> CREATE TABLE ot_ship
2 (
3 si_item VARCHAR2(12 BYTE),
4 si_qty NUMBER,
5 si_sys_id NUMBER
6 );
Table created.
SQL> CREATE TABLE OT_SHIP_BREAKUP
2 (
3 SB_RI_SYS_ID NUMBER,
4 SB_PB_SYS_ID NUMBER,
5 SB_SI_SYS_ID NUMBER,
6 SB_REF_QTY NUMBER
7 );
Table created.
SQL> CREATE OR REPLACE TRIGGER ot_ship_ins_break
2 AFTER INSERT
3 ON ot_ship
4 FOR EACH ROW
5 DECLARE
6 m_val NUMBER;
7
8 CURSOR c1
9 IS
10 SELECT ri_sys_id, prb_sys_id
11 FROM ot_req, ot_po_breakup
12 WHERE ri_sys_id = pb_ri_sys_id AND ri_item = :NEW.si_item;
13 BEGIN
14 FOR i IN c1
15 LOOP
16 INSERT INTO ot_ship_breakup
17 VALUES (i.ri_sys_id, i.prb_sys_id, :NEW.si_sys_id, :NEW.si_qty);
18 END LOOP;
19 END;
20 /
Trigger created.
SQL> insert into ot_ship values ('AA',10,44);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM OT_SHIP;
SI_ITEM SI_QTY SI_SYS_ID
------------ ---------- ----------
AA 10 44
SQL> SELECT * FROM OT_SHIP_BREAKUP;
SB_RI_SYS_ID SB_PB_SYS_ID SB_SI_SYS_ID SB_REF_QTY
------------ ------------ ------------ ----------
1 2 44 10
SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER ot_ship_del
2 AFTER DELETE
3 ON ot_ship
4 FOR EACH ROW
5 DECLARE
6 v_pi_qty NUMBER;
7 v_least NUMBER;
8 v_pb_ri_qty NUMBER;
9 v_sys_id NUMBER;
10 CURSOR c2
11 IS
12 SELECT sb_ri_sys_id, sb_pb_sys_id
13 FROM ot_ship_breakup
14 WHERE sb_si_sys_id = :OLD.si_sys_id;
15 BEGIN
16 v_pi_qty := NVL (:OLD.si_qty, 0);
17 v_sys_id := :OLD.si_sys_id; -- quantity to remove
18 -- remove old values:
19 FOR s IN c2
20 LOOP
21 FOR r IN (SELECT *
22 FROM ot_po_breakup
23 WHERE pb_ri_sys_id = s.sb_ri_sys_id
24 AND prb_sys_id = s.sb_pb_sys_id)
25 LOOP
26 IF v_pi_qty > 0
27 THEN
28 v_least := LEAST (v_pi_qty, NVL (r.pb_ves3q, 0));
29 UPDATE ot_po_breakup
30 SET pb_ves3q = pb_ves3q - v_least
31 WHERE pb_ri_sys_id = r.pb_ri_sys_id
32 AND pb_pi_sys_id = r.pb_pi_sys_id;
33 v_pi_qty := v_pi_qty - v_least;
34 v_least := LEAST (v_pi_qty, NVL (r.pb_ves2q, 0));
35 UPDATE ot_po_breakup
36 SET pb_ves2q = pb_ves2q - v_least
37 WHERE pb_ri_sys_id = r.pb_ri_sys_id
38 AND pb_pi_sys_id = r.pb_pi_sys_id;
39 v_pi_qty := v_pi_qty - v_least;
40 v_least := LEAST (v_pi_qty, NVL (r.pb_ves1q, 0));
41 UPDATE ot_po_breakup
42 SET pb_ves1q = pb_ves1q - v_least
43 WHERE pb_ri_sys_id = r.pb_ri_sys_id
44 AND pb_pi_sys_id = r.pb_pi_sys_id;
45 v_pi_qty := v_pi_qty - v_least;
46 END IF;
47 END LOOP;
48 END LOOP;
49* END ot_ship_del;
50 /
Trigger created.
SQL> delete from ot_ship;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from ot_po_breakup;
PB_RI_SYS_ID PB_PI_SYS_ID PB_RI_QTY PB_VES1Q PB_VES2Q PB_VES3Q PRB_SYS_ID
------------ ------------ ---------- ---------- ---------- ---------- ----------
1 33 20 2
SQL> spool off;
|
|
|
|
|
|
| Re: Complex trigger [message #553512 is a reply to message #553507] |
Sun, 06 May 2012 00:57 |
|
|
Hi actually i ran the test case first before making some changed and then copied it from the spool file, i have modified the column in trigger,please check now.I am really sorry for that.i have a real test case as below but the problem is there is a trigger written at form level which deletes the data from ot_ship_breakup before my ot_ship_del trigger fires and nothing happens.Please check the code below and one more problem is while updating on ot_po i need to change or update the ot_po.pi_qty values then only that trigger ot_po_air gets fired but i want that trigger to do the work automatically because the data in ot_po will get populated based on form trigger . can we combine this insert and update together.
SQL> CREATE TABLE ot_req
2 (
3 ri_item VARCHAR2(20 BYTE),
4 ri_sys_id NUMBER,
5 ri_qty NUMBER
6 )
7 /
Table created.
SQL> INSERT INTO OT_REQ VALUES ('AA',1,20);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> CREATE TABLE ot_po
2 (
3 pi_item VARCHAR2(12 BYTE),
4 pi_qty NUMBER,
5 pi_sys_id NUMBER
6 );
Table created.
SQL> CREATE SEQUENCE pb_sys_id
2 START WITH 1
3 MAXVALUE 999999999999999999999999999
4 MINVALUE 1
5 NOCYCLE
6 CACHE 20
7 NOORDER;
Sequence created.
SQL> CREATE TABLE OT_PO_BREAKUP
2 (
3 PB_RI_SYS_ID NUMBER,
4 PB_PI_SYS_ID NUMBER,
5 PB_RI_QTY NUMBER,
6 PB_VES1Q NUMBER,
7 PB_VES2Q NUMBER,
8 PB_VES3Q NUMBER,
9 PB_SYS_ID NUMBER
10 )
11 /
Table created.
SQL> CREATE OR REPLACE TRIGGER ot_po_aft
2 AFTER INSERT
3 ON ot_po
4 FOR EACH ROW
5 DECLARE
6 m_val NUMBER;
7
8 CURSOR c1
9 IS
10 SELECT ri_sys_id, ri_qty,ri_item
11 FROM ot_req
12 WHERE ri_item = :NEW.pi_item;
13
14 BEGIN
15 SELECT pb_sys_id.NEXTVAL
16 INTO m_val
17 FROM DUAL;
18 FOR I IN C1
19 LOOP
20 INSERT INTO ot_po_breakup
21 VALUES (I.RI_sys_id, :NEW.pi_sys_id, I.RI_QTY, NULL, NULL, NULL, M_VAL);
22 END LOOP;
23 END;
24 /
Trigger created.
SQL> insert into ot_po values('AA',10,33);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM OT_PO;
PI_ITEM PI_QTY PI_SYS_ID
------------ ---------- ----------
AA 10 33
SQL> SELECT * FROM OT_PO_BREAKUP;
PB_RI_SYS_ID PB_PI_SYS_ID PB_RI_QTY PB_VES1Q PB_VES2Q PB_VES3Q PB_SYS_ID
------------ ------------ ---------- ---------- ---------- ---------- ----------
1 33 20 2
SQL> CREATE OR REPLACE TRIGGER ot_po_air
2 AFTER update
3 ON ot_po
4 FOR EACH ROW
5 DECLARE
6 v_pi_qty NUMBER := NVL (:NEW.pi_qty, 0); -- quantity to distribute
7 v_pb_ri_qty NUMBER; -- space available
8 v_least NUMBER;
9 -- least of quantity to distribute and space available
10 BEGIN
11 -- fill ot_po_breakup rows in order of pb_ri_sys_id:
12 FOR r IN (SELECT *
13 FROM ot_po_breakup
14 ORDER BY pb_ri_sys_id)
15 LOOP
16 -- calculate currently available space in this row:
17 v_pb_ri_qty :=
18 NVL (r.pb_ri_qty, 0)
19 - NVL (r.pb_ves1q, 0)
20 - NVL (r.pb_ves2q, 0)
21 - NVL (r.pb_ves3q, 0);
22 -- calculate least of quantity to distribute and space available:
23 v_least := LEAST (v_pi_qty, v_pb_ri_qty);
24
25 -- fill vessels with above qantity in order: pb_ves1q, pb_ves2q, pb_ves3q:
26 IF v_least > 0
27 THEN
28 IF NVL (r.pb_ves1q, 0) = 0
29 THEN
30 UPDATE ot_po_breakup
31 SET pb_ves1q = v_least
32 WHERE pb_ri_sys_id = r.pb_ri_sys_id
33 AND pb_pi_sys_id = r.pb_pi_sys_id;
34 ELSIF NVL (r.pb_ves2q, 0) = 0
35 THEN
36 UPDATE ot_po_breakup
37 SET pb_ves2q = v_least
38 WHERE pb_ri_sys_id = r.pb_ri_sys_id
39 AND pb_pi_sys_id = r.pb_pi_sys_id;
40 ELSIF NVL (r.pb_ves3q, 0) = 0
41 THEN
42 UPDATE ot_po_breakup
43 SET pb_ves3q = v_least
44 WHERE pb_ri_sys_id = r.pb_ri_sys_id
45 AND pb_pi_sys_id = r.pb_pi_sys_id;
46 END IF;
47
48 -- update quantity to distribute:
49 v_pi_qty := v_pi_qty - v_least;
50 END IF;
51 END LOOP;
52 END ot_po_air;
53 /
Trigger created.
--the following command should be done while insert itself as i need to update it then it will change.i mean i want it automatic during insert which is from form.
SQL> update ot_po set pi_qty=10 where pi_sys_id=33
1 row Updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM OT_PO;
PI_ITEM PI_QTY PI_SYS_ID
------------ ---------- ----------
AA 10 33
SQL> SELECT * FROM OT_PO_BREAKUP;
PB_RI_SYS_ID PB_PI_SYS_ID PB_RI_QTY PB_VES1Q PB_VES2Q PB_VES3Q PB_SYS_ID
------------ ------------ ---------- ---------- ---------- ---------- ----------
1 33 20 10 2
SQL> CREATE TABLE ot_ship
2 (
3 si_item VARCHAR2(12 BYTE),
4 si_qty NUMBER,
5 si_sys_id NUMBER
6 );
Table created.
SQL> CREATE TABLE OT_SHIP_BREAKUP
2 (
3 SB_RI_SYS_ID NUMBER,
4 SB_PB_SYS_ID NUMBER,
5 SB_SI_SYS_ID NUMBER,
6 SB_REF_QTY NUMBER
7 );
Table created.
SQL> CREATE OR REPLACE TRIGGER ot_ship_ins_break
2 AFTER INSERT
3 ON ot_ship
4 FOR EACH ROW
5 DECLARE
6 m_val NUMBER;
7
8 CURSOR c1
9 IS
10 SELECT ri_sys_id, pb_sys_id
11 FROM ot_req, ot_po_breakup
12 WHERE ri_sys_id = pb_ri_sys_id AND ri_item = :NEW.si_item;
13 BEGIN
14 FOR i IN c1
15 LOOP
16 INSERT INTO ot_ship_breakup
17 VALUES (i.ri_sys_id, i.pb_sys_id, :NEW.si_sys_id, :NEW.si_qty);
18 END LOOP;
19 END;
20 /
Trigger created.
SQL> insert into ot_ship values ('AA',10,44);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM OT_SHIP;
SI_ITEM SI_QTY SI_SYS_ID
------------ ---------- ----------
AA 10 44
SQL> SELECT * FROM OT_SHIP_BREAKUP;
SB_RI_SYS_ID SB_PB_SYS_ID SB_SI_SYS_ID SB_REF_QTY
------------ ------------ ------------ ----------
1 2 44 10
SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER ot_ship_del
2 AFTER DELETE
3 ON ot_ship
4 FOR EACH ROW
5 DECLARE
6 v_pi_qty NUMBER;
7 v_least NUMBER;
8 v_pb_ri_qty NUMBER;
9 v_sys_id NUMBER;
10 CURSOR c2
11 IS
12 SELECT sb_ri_sys_id, sb_pb_sys_id
13 FROM ot_ship_breakup
14 WHERE sb_si_sys_id = :OLD.si_sys_id;
15 BEGIN
16 v_pi_qty := NVL (:OLD.si_qty, 0);
17 v_sys_id := :OLD.si_sys_id; -- quantity to remove
18 -- remove old values:
19 FOR s IN c2
20 LOOP
21 FOR r IN (SELECT *
22 FROM ot_po_breakup
23 WHERE pb_ri_sys_id = s.sb_ri_sys_id
24 AND pb_sys_id = s.sb_pb_sys_id)
25 LOOP
26 IF v_pi_qty > 0
27 THEN
28 v_least := LEAST (v_pi_qty, NVL (r.pb_ves3q, 0));
29 UPDATE ot_po_breakup
30 SET pb_ves3q = pb_ves3q - v_least
31 WHERE pb_ri_sys_id = r.pb_ri_sys_id
32 AND pb_pi_sys_id = r.pb_pi_sys_id;
33 v_pi_qty := v_pi_qty - v_least;
34 v_least := LEAST (v_pi_qty, NVL (r.pb_ves2q, 0));
35 UPDATE ot_po_breakup
36 SET pb_ves2q = pb_ves2q - v_least
37 WHERE pb_ri_sys_id = r.pb_ri_sys_id
38 AND pb_pi_sys_id = r.pb_pi_sys_id;
39 v_pi_qty := v_pi_qty - v_least;
40 v_least := LEAST (v_pi_qty, NVL (r.pb_ves1q, 0));
41 UPDATE ot_po_breakup
42 SET pb_ves1q = pb_ves1q - v_least
43 WHERE pb_ri_sys_id = r.pb_ri_sys_id
44 AND pb_pi_sys_id = r.pb_pi_sys_id;
45 v_pi_qty := v_pi_qty - v_least;
46 END IF;
47 END LOOP;
48 END LOOP;
49* END ot_ship_del;
50 /
Trigger created.
SQL> delete from ot_ship;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from ot_po_breakup;
PB_RI_SYS_ID PB_PI_SYS_ID PB_RI_QTY PB_VES1Q PB_VES2Q PB_VES3Q PB_SYS_ID
------------ ------------ ---------- ---------- ---------- ---------- ----------
1 33 20 2
SQL> spool off;
[Updated on: Sun, 06 May 2012 01:04] Report message to a moderator |
|
|
|
| Re: Complex trigger [message #553550 is a reply to message #553512] |
Sun, 06 May 2012 13:46 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
arif_md2009 wrote on Sat, 05 May 2012 22:57
... the problem is there is a trigger written at form level which deletes the data from ot_ship_breakup before my ot_ship_del trigger fires and nothing happens.
In your ot_ship_del trigger, instead of selecting from the ot_ship_breakup table:
SELECT sb_ri_sys_id, sb_pb_sys_id
FROM ot_ship_breakup
WHERE sb_si_sys_id = :OLD.si_sys_id
you can select from the ot_req and ot_po_breakup tables:
SELECT ot_req.ri_sys_id AS sb_ri_sys_id,
ot_po_breakup.pb_sys_id AS sb_pb_sys_id
FROM ot_req, ot_po_breakup
WHERE ot_req.ri_sys_id = ot_po_breakup.pb_ri_sys_id
AND ot_req.ri_item = :OLD.si_item
So, your ot_ship_del trigger would be:
CREATE OR REPLACE TRIGGER ot_ship_del
AFTER DELETE
ON ot_ship
FOR EACH ROW
DECLARE
v_pi_qty NUMBER;
v_least NUMBER;
v_pb_ri_qty NUMBER;
v_sys_id NUMBER;
BEGIN
-- form trigger deletes from ot_ship_breakup:
-- DELETE FROM ot_ship_breakup WHERE sb_si_sys_id = :OLD.si_sys_id;
v_pi_qty := NVL (:OLD.si_qty, 0);
v_sys_id := :OLD.si_sys_id; -- quantity to remove
-- remove old values:
FOR s IN
(SELECT ot_req.ri_sys_id AS sb_ri_sys_id,
ot_po_breakup.pb_sys_id AS sb_pb_sys_id
FROM ot_req, ot_po_breakup
WHERE ot_req.ri_sys_id = ot_po_breakup.pb_ri_sys_id
AND ot_req.ri_item = :OLD.si_item)
LOOP
FOR r IN
(SELECT *
FROM ot_po_breakup
WHERE pb_ri_sys_id = s.sb_ri_sys_id
AND pb_sys_id = s.sb_pb_sys_id)
LOOP
IF v_pi_qty > 0
THEN
v_least := LEAST (v_pi_qty, NVL (r.pb_ves3q, 0));
UPDATE ot_po_breakup
SET pb_ves3q = pb_ves3q - v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
v_pi_qty := v_pi_qty - v_least;
v_least := LEAST (v_pi_qty, NVL (r.pb_ves2q, 0));
UPDATE ot_po_breakup
SET pb_ves2q = pb_ves2q - v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
v_pi_qty := v_pi_qty - v_least;
v_least := LEAST (v_pi_qty, NVL (r.pb_ves1q, 0));
UPDATE ot_po_breakup
SET pb_ves1q = pb_ves1q - v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
v_pi_qty := v_pi_qty - v_least;
END IF;
END LOOP;
END LOOP;
END ot_ship_del;
/
SHOW ERRORS
-- full script:
drop trigger ot_ship_del
/
drop trigger ot_ship_ins_break
/
drop trigger ot_po_air
/
drop trigger ot_po_aft
/
drop sequence pb_sys_id
/
drop table ot_ship_breakup
/
drop table ot_ship
/
drop table ot_po_breakup
/
drop table ot_po
/
drop table ot_req
/
-- tables:
CREATE TABLE ot_req
(
ri_item VARCHAR2(20 BYTE),
ri_sys_id NUMBER,
ri_qty NUMBER
)
/
CREATE TABLE ot_po
(
pi_item VARCHAR2(12 BYTE),
pi_qty NUMBER,
pi_sys_id NUMBER
)
/
CREATE TABLE OT_PO_BREAKUP
(
PB_RI_SYS_ID NUMBER,
PB_PI_SYS_ID NUMBER,
PB_RI_QTY NUMBER,
PB_VES1Q NUMBER,
PB_VES2Q NUMBER,
PB_VES3Q NUMBER,
PB_SYS_ID NUMBER
)
/
CREATE TABLE ot_ship
(
si_item VARCHAR2(12 BYTE),
si_qty NUMBER,
si_sys_id NUMBER
)
/
CREATE TABLE OT_SHIP_BREAKUP
(
SB_RI_SYS_ID NUMBER,
SB_PB_SYS_ID NUMBER,
SB_SI_SYS_ID NUMBER,
SB_REF_QTY NUMBER
)
/
-- sequence:
CREATE SEQUENCE pb_sys_id
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER
/
-- triggers:
CREATE OR REPLACE TRIGGER ot_po_aft
AFTER INSERT
ON ot_po
FOR EACH ROW
DECLARE
m_val NUMBER;
CURSOR c1
IS
SELECT ri_sys_id, ri_qty,ri_item
FROM ot_req
WHERE ri_item = :NEW.pi_item;
BEGIN
SELECT pb_sys_id.NEXTVAL
INTO m_val
FROM DUAL;
FOR I IN C1
LOOP
INSERT INTO ot_po_breakup
VALUES (I.RI_sys_id, :NEW.pi_sys_id, I.RI_QTY, NULL, NULL, NULL, M_VAL);
END LOOP;
END;
/
SHOW ERRORS
CREATE OR REPLACE TRIGGER ot_po_air
AFTER update
ON ot_po
FOR EACH ROW
DECLARE
v_pi_qty NUMBER := NVL (:NEW.pi_qty, 0); -- quantity to distribute
v_pb_ri_qty NUMBER; -- space available
v_least NUMBER;
-- least of quantity to distribute and space available
BEGIN
-- fill ot_po_breakup rows in order of pb_ri_sys_id:
FOR r IN (SELECT *
FROM ot_po_breakup
ORDER BY pb_ri_sys_id)
LOOP
-- calculate currently available space in this row:
v_pb_ri_qty :=
NVL (r.pb_ri_qty, 0)
- NVL (r.pb_ves1q, 0)
- NVL (r.pb_ves2q, 0)
- NVL (r.pb_ves3q, 0);
-- calculate least of quantity to distribute and space available:
v_least := LEAST (v_pi_qty, v_pb_ri_qty);
-- fill vessels with above qantity in order: pb_ves1q, pb_ves2q, pb_ves3q:
IF v_least > 0
THEN
IF NVL (r.pb_ves1q, 0) = 0
THEN
UPDATE ot_po_breakup
SET pb_ves1q = v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
ELSIF NVL (r.pb_ves2q, 0) = 0
THEN
UPDATE ot_po_breakup
SET pb_ves2q = v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
ELSIF NVL (r.pb_ves3q, 0) = 0
THEN
UPDATE ot_po_breakup
SET pb_ves3q = v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
END IF;
-- update quantity to distribute:
v_pi_qty := v_pi_qty - v_least;
END IF;
END LOOP;
END ot_po_air;
/
SHOW ERRORS
CREATE OR REPLACE TRIGGER ot_ship_ins_break
AFTER INSERT
ON ot_ship
FOR EACH ROW
DECLARE
m_val NUMBER;
CURSOR c1
IS
SELECT ri_sys_id, pb_sys_id
FROM ot_req, ot_po_breakup
WHERE ri_sys_id = pb_ri_sys_id AND ri_item = :NEW.si_item;
BEGIN
FOR i IN c1
LOOP
INSERT INTO ot_ship_breakup
VALUES (i.ri_sys_id, i.pb_sys_id, :NEW.si_sys_id, :NEW.si_qty);
END LOOP;
END;
/
SHOW ERRORS
CREATE OR REPLACE TRIGGER ot_ship_del
AFTER DELETE
ON ot_ship
FOR EACH ROW
DECLARE
v_pi_qty NUMBER;
v_least NUMBER;
v_pb_ri_qty NUMBER;
v_sys_id NUMBER;
BEGIN
-- form trigger deletes from ot_ship_breakup:
-- DELETE FROM ot_ship_breakup WHERE sb_si_sys_id = :OLD.si_sys_id;
v_pi_qty := NVL (:OLD.si_qty, 0);
v_sys_id := :OLD.si_sys_id; -- quantity to remove
-- remove old values:
FOR s IN
(SELECT ot_req.ri_sys_id AS sb_ri_sys_id,
ot_po_breakup.pb_sys_id AS sb_pb_sys_id
FROM ot_req, ot_po_breakup
WHERE ot_req.ri_sys_id = ot_po_breakup.pb_ri_sys_id
AND ot_req.ri_item = :OLD.si_item)
LOOP
FOR r IN
(SELECT *
FROM ot_po_breakup
WHERE pb_ri_sys_id = s.sb_ri_sys_id
AND pb_sys_id = s.sb_pb_sys_id)
LOOP
IF v_pi_qty > 0
THEN
v_least := LEAST (v_pi_qty, NVL (r.pb_ves3q, 0));
UPDATE ot_po_breakup
SET pb_ves3q = pb_ves3q - v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
v_pi_qty := v_pi_qty - v_least;
v_least := LEAST (v_pi_qty, NVL (r.pb_ves2q, 0));
UPDATE ot_po_breakup
SET pb_ves2q = pb_ves2q - v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
v_pi_qty := v_pi_qty - v_least;
v_least := LEAST (v_pi_qty, NVL (r.pb_ves1q, 0));
UPDATE ot_po_breakup
SET pb_ves1q = pb_ves1q - v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
v_pi_qty := v_pi_qty - v_least;
END IF;
END LOOP;
END LOOP;
END ot_ship_del;
/
SHOW ERRORS
-- DML and results:
INSERT INTO ot_req (ri_item, ri_sys_id, ri_qty) VALUES ('AA', 1, 20)
/
COMMIT
/
SELECT * FROM ot_req
/
INSERT INTO ot_po (pi_item, pi_qty, pi_sys_id) VALUES ('AA', 10, 33)
/
COMMIT
/
SELECT * FROM ot_po
/
SELECT * FROM ot_po_breakup
/
UPDATE ot_po SET pi_qty = 10 WHERE pi_sys_id= 33
/
COMMIT
/
SELECT * FROM ot_po
/
SELECT * FROM ot_po_breakup
/
INSERT INTO ot_ship (si_item, si_qty, si_sys_id) VALUES ('AA', 10, 44)
/
COMMIT
/
SELECT * FROM ot_ship
/
SELECT * FROM ot_ship_breakup
/
DELETE FROM ot_ship
/
COMMIT
/
SELECT * FROM ot_ship
/
SELECT * FROM ot_ship_breakup
/
SELECT * FROM ot_po_breakup
/
-- execution of full script:
SCOTT@orcl_11gR2> drop trigger ot_ship_del
2 /
Trigger dropped.
SCOTT@orcl_11gR2> drop trigger ot_ship_ins_break
2 /
Trigger dropped.
SCOTT@orcl_11gR2> drop trigger ot_po_air
2 /
Trigger dropped.
SCOTT@orcl_11gR2> drop trigger ot_po_aft
2 /
Trigger dropped.
SCOTT@orcl_11gR2> drop sequence pb_sys_id
2 /
Sequence dropped.
SCOTT@orcl_11gR2> drop table ot_ship_breakup
2 /
Table dropped.
SCOTT@orcl_11gR2> drop table ot_ship
2 /
Table dropped.
SCOTT@orcl_11gR2> drop table ot_po_breakup
2 /
Table dropped.
SCOTT@orcl_11gR2> drop table ot_po
2 /
Table dropped.
SCOTT@orcl_11gR2> drop table ot_req
2 /
Table dropped.
SCOTT@orcl_11gR2> -- tables:
SCOTT@orcl_11gR2> CREATE TABLE ot_req
2 (
3 ri_item VARCHAR2(20 BYTE),
4 ri_sys_id NUMBER,
5 ri_qty NUMBER
6 )
7 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE ot_po
2 (
3 pi_item VARCHAR2(12 BYTE),
4 pi_qty NUMBER,
5 pi_sys_id NUMBER
6 )
7 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE OT_PO_BREAKUP
2 (
3 PB_RI_SYS_ID NUMBER,
4 PB_PI_SYS_ID NUMBER,
5 PB_RI_QTY NUMBER,
6 PB_VES1Q NUMBER,
7 PB_VES2Q NUMBER,
8 PB_VES3Q NUMBER,
9 PB_SYS_ID NUMBER
10 )
11 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE ot_ship
2 (
3 si_item VARCHAR2(12 BYTE),
4 si_qty NUMBER,
5 si_sys_id NUMBER
6 )
7 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE OT_SHIP_BREAKUP
2 (
3 SB_RI_SYS_ID NUMBER,
4 SB_PB_SYS_ID NUMBER,
5 SB_SI_SYS_ID NUMBER,
6 SB_REF_QTY NUMBER
7 )
8 /
Table created.
SCOTT@orcl_11gR2> -- sequence:
SCOTT@orcl_11gR2> CREATE SEQUENCE pb_sys_id
2 START WITH 1
3 MAXVALUE 999999999999999999999999999
4 MINVALUE 1
5 NOCYCLE
6 CACHE 20
7 NOORDER
8 /
Sequence created.
SCOTT@orcl_11gR2> -- triggers:
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER ot_po_aft
2 AFTER INSERT
3 ON ot_po
4 FOR EACH ROW
5 DECLARE
6 m_val NUMBER;
7
8 CURSOR c1
9 IS
10 SELECT ri_sys_id, ri_qty,ri_item
11 FROM ot_req
12 WHERE ri_item = :NEW.pi_item;
13
14 BEGIN
15 SELECT pb_sys_id.NEXTVAL
16 INTO m_val
17 FROM DUAL;
18 FOR I IN C1
19 LOOP
20 INSERT INTO ot_po_breakup
21 VALUES (I.RI_sys_id, :NEW.pi_sys_id, I.RI_QTY, NULL, NULL, NULL, M_VAL);
22 END LOOP;
23 END;
24 /
Trigger created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER ot_po_air
2 AFTER update
3 ON ot_po
4 FOR EACH ROW
5 DECLARE
6 v_pi_qty NUMBER := NVL (:NEW.pi_qty, 0); -- quantity to distribute
7 v_pb_ri_qty NUMBER; -- space available
8 v_least NUMBER;
9 -- least of quantity to distribute and space available
10 BEGIN
11 -- fill ot_po_breakup rows in order of pb_ri_sys_id:
12 FOR r IN (SELECT *
13 FROM ot_po_breakup
14 ORDER BY pb_ri_sys_id)
15 LOOP
16 -- calculate currently available space in this row:
17 v_pb_ri_qty :=
18 NVL (r.pb_ri_qty, 0)
19 - NVL (r.pb_ves1q, 0)
20 - NVL (r.pb_ves2q, 0)
21 - NVL (r.pb_ves3q, 0);
22 -- calculate least of quantity to distribute and space available:
23 v_least := LEAST (v_pi_qty, v_pb_ri_qty);
24
25 -- fill vessels with above qantity in order: pb_ves1q, pb_ves2q, pb_ves3q:
26 IF v_least > 0
27 THEN
28 IF NVL (r.pb_ves1q, 0) = 0
29 THEN
30 UPDATE ot_po_breakup
31 SET pb_ves1q = v_least
32 WHERE pb_ri_sys_id = r.pb_ri_sys_id
33 AND pb_pi_sys_id = r.pb_pi_sys_id;
34 ELSIF NVL (r.pb_ves2q, 0) = 0
35 THEN
36 UPDATE ot_po_breakup
37 SET pb_ves2q = v_least
38 WHERE pb_ri_sys_id = r.pb_ri_sys_id
39 AND pb_pi_sys_id = r.pb_pi_sys_id;
40 ELSIF NVL (r.pb_ves3q, 0) = 0
41 THEN
42 UPDATE ot_po_breakup
43 SET pb_ves3q = v_least
44 WHERE pb_ri_sys_id = r.pb_ri_sys_id
45 AND pb_pi_sys_id = r.pb_pi_sys_id;
46 END IF;
47
48 -- update quantity to distribute:
49 v_pi_qty := v_pi_qty - v_least;
50 END IF;
51 END LOOP;
52 END ot_po_air;
53 /
Trigger created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER ot_ship_ins_break
2 AFTER INSERT
3 ON ot_ship
4 FOR EACH ROW
5 DECLARE
6 m_val NUMBER;
7
8 CURSOR c1
9 IS
10 SELECT ri_sys_id, pb_sys_id
11 FROM ot_req, ot_po_breakup
12 WHERE ri_sys_id = pb_ri_sys_id AND ri_item = :NEW.si_item;
13 BEGIN
14 FOR i IN c1
15 LOOP
16 INSERT INTO ot_ship_breakup
17 VALUES (i.ri_sys_id, i.pb_sys_id, :NEW.si_sys_id, :NEW.si_qty);
18 END LOOP;
19 END;
20 /
Trigger created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER ot_ship_del
2 AFTER DELETE
3 ON ot_ship
4 FOR EACH ROW
5 DECLARE
6 v_pi_qty NUMBER;
7 v_least NUMBER;
8 v_pb_ri_qty NUMBER;
9 v_sys_id NUMBER;
10 BEGIN
11 -- form trigger deletes from ot_ship_breakup:
12 -- DELETE FROM ot_ship_breakup WHERE sb_si_sys_id = :OLD.si_sys_id;
13 v_pi_qty := NVL (:OLD.si_qty, 0);
14 v_sys_id := :OLD.si_sys_id; -- quantity to remove
15 -- remove old values:
16 FOR s IN
17 (SELECT ot_req.ri_sys_id AS sb_ri_sys_id,
18 ot_po_breakup.pb_sys_id AS sb_pb_sys_id
19 FROM ot_req, ot_po_breakup
20 WHERE ot_req.ri_sys_id = ot_po_breakup.pb_ri_sys_id
21 AND ot_req.ri_item = :OLD.si_item)
22 LOOP
23 FOR r IN
24 (SELECT *
25 FROM ot_po_breakup
26 WHERE pb_ri_sys_id = s.sb_ri_sys_id
27 AND pb_sys_id = s.sb_pb_sys_id)
28 LOOP
29 IF v_pi_qty > 0
30 THEN
31 v_least := LEAST (v_pi_qty, NVL (r.pb_ves3q, 0));
32 UPDATE ot_po_breakup
33 SET pb_ves3q = pb_ves3q - v_least
34 WHERE pb_ri_sys_id = r.pb_ri_sys_id
35 AND pb_pi_sys_id = r.pb_pi_sys_id;
36 v_pi_qty := v_pi_qty - v_least;
37 v_least := LEAST (v_pi_qty, NVL (r.pb_ves2q, 0));
38 UPDATE ot_po_breakup
39 SET pb_ves2q = pb_ves2q - v_least
40 WHERE pb_ri_sys_id = r.pb_ri_sys_id
41 AND pb_pi_sys_id = r.pb_pi_sys_id;
42 v_pi_qty := v_pi_qty - v_least;
43 v_least := LEAST (v_pi_qty, NVL (r.pb_ves1q, 0));
44 UPDATE ot_po_breakup
45 SET pb_ves1q = pb_ves1q - v_least
46 WHERE pb_ri_sys_id = r.pb_ri_sys_id
47 AND pb_pi_sys_id = r.pb_pi_sys_id;
48 v_pi_qty := v_pi_qty - v_least;
49 END IF;
50 END LOOP;
51 END LOOP;
52 END ot_ship_del;
53 /
Trigger created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> -- DML and results:
SCOTT@orcl_11gR2> INSERT INTO ot_req (ri_item, ri_sys_id, ri_qty) VALUES ('AA', 1, 20)
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> SELECT * FROM ot_req
2 /
RI_ITEM RI_SYS_ID RI_QTY
-------------------- ---------- ----------
AA 1 20
1 row selected.
SCOTT@orcl_11gR2> INSERT INTO ot_po (pi_item, pi_qty, pi_sys_id) VALUES ('AA', 10, 33)
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> SELECT * FROM ot_po
2 /
PI_ITEM PI_QTY PI_SYS_ID
------------ ---------- ----------
AA 10 33
1 row selected.
SCOTT@orcl_11gR2> SELECT * FROM ot_po_breakup
2 /
PB_RI_SYS_ID PB_PI_SYS_ID PB_RI_QTY PB_VES1Q PB_VES2Q PB_VES3Q PB_SYS_ID
------------ ------------ ---------- ---------- ---------- ---------- ----------
1 33 20 1
1 row selected.
SCOTT@orcl_11gR2> UPDATE ot_po SET pi_qty = 10 WHERE pi_sys_id= 33
2 /
1 row updated.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> SELECT * FROM ot_po
2 /
PI_ITEM PI_QTY PI_SYS_ID
------------ ---------- ----------
AA 10 33
1 row selected.
SCOTT@orcl_11gR2> SELECT * FROM ot_po_breakup
2 /
PB_RI_SYS_ID PB_PI_SYS_ID PB_RI_QTY PB_VES1Q PB_VES2Q PB_VES3Q PB_SYS_ID
------------ ------------ ---------- ---------- ---------- ---------- ----------
1 33 20 10 1
1 row selected.
SCOTT@orcl_11gR2> INSERT INTO ot_ship (si_item, si_qty, si_sys_id) VALUES ('AA', 10, 44)
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> SELECT * FROM ot_ship
2 /
SI_ITEM SI_QTY SI_SYS_ID
------------ ---------- ----------
AA 10 44
1 row selected.
SCOTT@orcl_11gR2> SELECT * FROM ot_ship_breakup
2 /
SB_RI_SYS_ID SB_PB_SYS_ID SB_SI_SYS_ID SB_REF_QTY
------------ ------------ ------------ ----------
1 1 44 10
1 row selected.
SCOTT@orcl_11gR2> DELETE FROM ot_ship
2 /
1 row deleted.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> SELECT * FROM ot_ship
2 /
no rows selected
SCOTT@orcl_11gR2> SELECT * FROM ot_ship_breakup
2 /
SB_RI_SYS_ID SB_PB_SYS_ID SB_SI_SYS_ID SB_REF_QTY
------------ ------------ ------------ ----------
1 1 44 10
1 row selected.
SCOTT@orcl_11gR2> SELECT * FROM ot_po_breakup
2 /
PB_RI_SYS_ID PB_PI_SYS_ID PB_RI_QTY PB_VES1Q PB_VES2Q PB_VES3Q PB_SYS_ID
------------ ------------ ---------- ---------- ---------- ---------- ----------
1 33 20 0 1
1 row selected.
|
|
|
|
| Re: Complex trigger [message #553554 is a reply to message #553512] |
Sun, 06 May 2012 14:09 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
arif_md2009 wrote on Sat, 05 May 2012 22:57
... one more problem is while updating on ot_po i need to change or update the ot_po.pi_qty values then only that trigger ot_po_air gets fired but i want that trigger to do the work automatically because the data in ot_po will get populated based on form trigger . can we combine this insert and update together.
If you are asking if you can combine your ot_po_aft and ot_po_air triggers into one, then yes you can:
CREATE OR REPLACE TRIGGER ot_po_aft
AFTER INSERT ON ot_po FOR EACH ROW
DECLARE
m_val NUMBER;
v_pi_qty NUMBER := NVL (:NEW.pi_qty, 0); -- quantity to distribute
v_pb_ri_qty NUMBER; -- space available
v_least NUMBER; -- least of quantity to distribute and space available
BEGIN
-- insert into ot_po_breakup:
SELECT pb_sys_id.NEXTVAL INTO m_val FROM DUAL;
FOR i IN
(SELECT ri_sys_id, ri_qty,ri_item
FROM ot_req
WHERE ri_item = :NEW.pi_item)
LOOP
INSERT INTO ot_po_breakup (pb_ri_sys_id, pb_pi_sys_id, pb_ri_qty, pb_sys_id)
VALUES (i.ri_sys_id, :NEW.pi_sys_id, i.ri_qty, m_val);
END LOOP;
-- update ot_po_breakup:
-- fill ot_po_breakup rows in order of pb_ri_sys_id:
FOR r IN
(SELECT *
FROM ot_po_breakup
ORDER BY pb_ri_sys_id)
LOOP
-- calculate currently available space in this row:
v_pb_ri_qty :=
NVL (r.pb_ri_qty, 0)
- NVL (r.pb_ves1q, 0)
- NVL (r.pb_ves2q, 0)
- NVL (r.pb_ves3q, 0);
-- calculate least of quantity to distribute and space available:
v_least := LEAST (v_pi_qty, v_pb_ri_qty);
-- fill vessels with above qantity in order: pb_ves1q, pb_ves2q, pb_ves3q:
IF v_least > 0
THEN
IF NVL (r.pb_ves1q, 0) = 0
THEN
UPDATE ot_po_breakup
SET pb_ves1q = v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
ELSIF NVL (r.pb_ves2q, 0) = 0
THEN
UPDATE ot_po_breakup
SET pb_ves2q = v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
ELSIF NVL (r.pb_ves3q, 0) = 0
THEN
UPDATE ot_po_breakup
SET pb_ves3q = v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
END IF;
-- update quantity to distribute:
v_pi_qty := v_pi_qty - v_least;
END IF;
END LOOP;
END ot_po_aft;
/
SHOW ERRORS
-- revised complete script:
drop trigger ot_ship_del
/
drop trigger ot_ship_ins_break
/
drop trigger ot_po_aft
/
drop sequence pb_sys_id
/
drop table ot_ship_breakup
/
drop table ot_ship
/
drop table ot_po_breakup
/
drop table ot_po
/
drop table ot_req
/
-- tables:
CREATE TABLE ot_req
(
ri_item VARCHAR2(20 BYTE),
ri_sys_id NUMBER,
ri_qty NUMBER
)
/
CREATE TABLE ot_po
(
pi_item VARCHAR2(12 BYTE),
pi_qty NUMBER,
pi_sys_id NUMBER
)
/
CREATE TABLE OT_PO_BREAKUP
(
PB_RI_SYS_ID NUMBER,
PB_PI_SYS_ID NUMBER,
PB_RI_QTY NUMBER,
PB_VES1Q NUMBER,
PB_VES2Q NUMBER,
PB_VES3Q NUMBER,
PB_SYS_ID NUMBER
)
/
CREATE TABLE ot_ship
(
si_item VARCHAR2(12 BYTE),
si_qty NUMBER,
si_sys_id NUMBER
)
/
CREATE TABLE OT_SHIP_BREAKUP
(
SB_RI_SYS_ID NUMBER,
SB_PB_SYS_ID NUMBER,
SB_SI_SYS_ID NUMBER,
SB_REF_QTY NUMBER
)
/
-- sequence:
CREATE SEQUENCE pb_sys_id
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER
/
-- triggers:
CREATE OR REPLACE TRIGGER ot_po_aft
AFTER INSERT ON ot_po FOR EACH ROW
DECLARE
m_val NUMBER;
v_pi_qty NUMBER := NVL (:NEW.pi_qty, 0); -- quantity to distribute
v_pb_ri_qty NUMBER; -- space available
v_least NUMBER; -- least of quantity to distribute and space available
BEGIN
-- insert into ot_po_breakup:
SELECT pb_sys_id.NEXTVAL INTO m_val FROM DUAL;
FOR i IN
(SELECT ri_sys_id, ri_qty,ri_item
FROM ot_req
WHERE ri_item = :NEW.pi_item)
LOOP
INSERT INTO ot_po_breakup (pb_ri_sys_id, pb_pi_sys_id, pb_ri_qty, pb_sys_id)
VALUES (i.ri_sys_id, :NEW.pi_sys_id, i.ri_qty, m_val);
END LOOP;
-- update ot_po_breakup:
-- fill ot_po_breakup rows in order of pb_ri_sys_id:
FOR r IN
(SELECT *
FROM ot_po_breakup
ORDER BY pb_ri_sys_id)
LOOP
-- calculate currently available space in this row:
v_pb_ri_qty :=
NVL (r.pb_ri_qty, 0)
- NVL (r.pb_ves1q, 0)
- NVL (r.pb_ves2q, 0)
- NVL (r.pb_ves3q, 0);
-- calculate least of quantity to distribute and space available:
v_least := LEAST (v_pi_qty, v_pb_ri_qty);
-- fill vessels with above qantity in order: pb_ves1q, pb_ves2q, pb_ves3q:
IF v_least > 0
THEN
IF NVL (r.pb_ves1q, 0) = 0
THEN
UPDATE ot_po_breakup
SET pb_ves1q = v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
ELSIF NVL (r.pb_ves2q, 0) = 0
THEN
UPDATE ot_po_breakup
SET pb_ves2q = v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
ELSIF NVL (r.pb_ves3q, 0) = 0
THEN
UPDATE ot_po_breakup
SET pb_ves3q = v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
END IF;
-- update quantity to distribute:
v_pi_qty := v_pi_qty - v_least;
END IF;
END LOOP;
END ot_po_aft;
/
SHOW ERRORS
CREATE OR REPLACE TRIGGER ot_ship_ins_break
AFTER INSERT
ON ot_ship
FOR EACH ROW
DECLARE
m_val NUMBER;
CURSOR c1
IS
SELECT ri_sys_id, pb_sys_id
FROM ot_req, ot_po_breakup
WHERE ri_sys_id = pb_ri_sys_id AND ri_item = :NEW.si_item;
BEGIN
FOR i IN c1
LOOP
INSERT INTO ot_ship_breakup
VALUES (i.ri_sys_id, i.pb_sys_id, :NEW.si_sys_id, :NEW.si_qty);
END LOOP;
END;
/
SHOW ERRORS
CREATE OR REPLACE TRIGGER ot_ship_del
AFTER DELETE
ON ot_ship
FOR EACH ROW
DECLARE
v_pi_qty NUMBER;
v_least NUMBER;
v_pb_ri_qty NUMBER;
v_sys_id NUMBER;
BEGIN
-- form trigger deletes from ot_ship_breakup:
-- DELETE FROM ot_ship_breakup WHERE sb_si_sys_id = :OLD.si_sys_id;
v_pi_qty := NVL (:OLD.si_qty, 0);
v_sys_id := :OLD.si_sys_id; -- quantity to remove
-- remove old values:
FOR s IN
(SELECT ot_req.ri_sys_id AS sb_ri_sys_id,
ot_po_breakup.pb_sys_id AS sb_pb_sys_id
FROM ot_req, ot_po_breakup
WHERE ot_req.ri_sys_id = ot_po_breakup.pb_ri_sys_id
AND ot_req.ri_item = :OLD.si_item)
LOOP
FOR r IN
(SELECT *
FROM ot_po_breakup
WHERE pb_ri_sys_id = s.sb_ri_sys_id
AND pb_sys_id = s.sb_pb_sys_id)
LOOP
IF v_pi_qty > 0
THEN
v_least := LEAST (v_pi_qty, NVL (r.pb_ves3q, 0));
UPDATE ot_po_breakup
SET pb_ves3q = pb_ves3q - v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
v_pi_qty := v_pi_qty - v_least;
v_least := LEAST (v_pi_qty, NVL (r.pb_ves2q, 0));
UPDATE ot_po_breakup
SET pb_ves2q = pb_ves2q - v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
v_pi_qty := v_pi_qty - v_least;
v_least := LEAST (v_pi_qty, NVL (r.pb_ves1q, 0));
UPDATE ot_po_breakup
SET pb_ves1q = pb_ves1q - v_least
WHERE pb_ri_sys_id = r.pb_ri_sys_id
AND pb_pi_sys_id = r.pb_pi_sys_id;
v_pi_qty := v_pi_qty - v_least;
END IF;
END LOOP;
END LOOP;
END ot_ship_del;
/
SHOW ERRORS
-- DML and results:
INSERT INTO ot_req (ri_item, ri_sys_id, ri_qty) VALUES ('AA', 1, 20)
/
COMMIT
/
SELECT * FROM ot_req
/
INSERT INTO ot_po (pi_item, pi_qty, pi_sys_id) VALUES ('AA', 10, 33)
/
COMMIT
/
SELECT * FROM ot_po
/
SELECT * FROM ot_po_breakup
/
INSERT INTO ot_ship (si_item, si_qty, si_sys_id) VALUES ('AA', 10, 44)
/
COMMIT
/
SELECT * FROM ot_ship
/
SELECT * FROM ot_ship_breakup
/
DELETE FROM ot_ship
/
COMMIT
/
SELECT * FROM ot_ship
/
SELECT * FROM ot_ship_breakup
/
SELECT * FROM ot_po_breakup
/
-- execution of revised complete script:
SCOTT@orcl_11gR2> drop trigger ot_ship_del
2 /
Trigger dropped.
SCOTT@orcl_11gR2> drop trigger ot_ship_ins_break
2 /
Trigger dropped.
SCOTT@orcl_11gR2> drop trigger ot_po_aft
2 /
Trigger dropped.
SCOTT@orcl_11gR2> drop sequence pb_sys_id
2 /
Sequence dropped.
SCOTT@orcl_11gR2> drop table ot_ship_breakup
2 /
Table dropped.
SCOTT@orcl_11gR2> drop table ot_ship
2 /
Table dropped.
SCOTT@orcl_11gR2> drop table ot_po_breakup
2 /
Table dropped.
SCOTT@orcl_11gR2> drop table ot_po
2 /
Table dropped.
SCOTT@orcl_11gR2> drop table ot_req
2 /
Table dropped.
SCOTT@orcl_11gR2> -- tables:
SCOTT@orcl_11gR2> CREATE TABLE ot_req
2 (
3 ri_item VARCHAR2(20 BYTE),
4 ri_sys_id NUMBER,
5 ri_qty NUMBER
6 )
7 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE ot_po
2 (
3 pi_item VARCHAR2(12 BYTE),
4 pi_qty NUMBER,
5 pi_sys_id NUMBER
6 )
7 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE OT_PO_BREAKUP
2 (
3 PB_RI_SYS_ID NUMBER,
4 PB_PI_SYS_ID NUMBER,
5 PB_RI_QTY NUMBER,
6 PB_VES1Q NUMBER,
7 PB_VES2Q NUMBER,
8 PB_VES3Q NUMBER,
9 PB_SYS_ID NUMBER
10 )
11 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE ot_ship
2 (
3 si_item VARCHAR2(12 BYTE),
4 si_qty NUMBER,
5 si_sys_id NUMBER
6 )
7 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE OT_SHIP_BREAKUP
2 (
3 SB_RI_SYS_ID NUMBER,
4 SB_PB_SYS_ID NUMBER,
5 SB_SI_SYS_ID NUMBER,
6 SB_REF_QTY NUMBER
7 )
8 /
Table created.
SCOTT@orcl_11gR2> -- sequence:
SCOTT@orcl_11gR2> CREATE SEQUENCE pb_sys_id
2 START WITH 1
3 MAXVALUE 999999999999999999999999999
4 MINVALUE 1
5 NOCYCLE
6 CACHE 20
7 NOORDER
8 /
Sequence created.
SCOTT@orcl_11gR2> -- triggers:
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER ot_po_aft
2 AFTER INSERT ON ot_po FOR EACH ROW
3 DECLARE
4 m_val NUMBER;
5 v_pi_qty NUMBER := NVL (:NEW.pi_qty, 0); -- quantity to distribute
6 v_pb_ri_qty NUMBER; -- space available
7 v_least NUMBER; -- least of quantity to distribute and space available
8 BEGIN
9 -- insert into ot_po_breakup:
10 SELECT pb_sys_id.NEXTVAL INTO m_val FROM DUAL;
11 FOR i IN
12 (SELECT ri_sys_id, ri_qty,ri_item
13 FROM ot_req
14 WHERE ri_item = :NEW.pi_item)
15 LOOP
16 INSERT INTO ot_po_breakup (pb_ri_sys_id, pb_pi_sys_id, pb_ri_qty, pb_sys_id)
17 VALUES (i.ri_sys_id, :NEW.pi_sys_id, i.ri_qty, m_val);
18 END LOOP;
19 -- update ot_po_breakup:
20 -- fill ot_po_breakup rows in order of pb_ri_sys_id:
21 FOR r IN
22 (SELECT *
23 FROM ot_po_breakup
24 ORDER BY pb_ri_sys_id)
25 LOOP
26 -- calculate currently available space in this row:
27 v_pb_ri_qty :=
28 NVL (r.pb_ri_qty, 0)
29 - NVL (r.pb_ves1q, 0)
30 - NVL (r.pb_ves2q, 0)
31 - NVL (r.pb_ves3q, 0);
32 -- calculate least of quantity to distribute and space available:
33 v_least := LEAST (v_pi_qty, v_pb_ri_qty);
34 -- fill vessels with above qantity in order: pb_ves1q, pb_ves2q, pb_ves3q:
35 IF v_least > 0
36 THEN
37 IF NVL (r.pb_ves1q, 0) = 0
38 THEN
39 UPDATE ot_po_breakup
40 SET pb_ves1q = v_least
41 WHERE pb_ri_sys_id = r.pb_ri_sys_id
42 AND pb_pi_sys_id = r.pb_pi_sys_id;
43 ELSIF NVL (r.pb_ves2q, 0) = 0
44 THEN
45 UPDATE ot_po_breakup
46 SET pb_ves2q = v_least
47 WHERE pb_ri_sys_id = r.pb_ri_sys_id
48 AND pb_pi_sys_id = r.pb_pi_sys_id;
49 ELSIF NVL (r.pb_ves3q, 0) = 0
50 THEN
51 UPDATE ot_po_breakup
52 SET pb_ves3q = v_least
53 WHERE pb_ri_sys_id = r.pb_ri_sys_id
54 AND pb_pi_sys_id = r.pb_pi_sys_id;
55 END IF;
56 -- update quantity to distribute:
57 v_pi_qty := v_pi_qty - v_least;
58 END IF;
59 END LOOP;
60 END ot_po_aft;
61 /
Trigger created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER ot_ship_ins_break
2 AFTER INSERT
3 ON ot_ship
4 FOR EACH ROW
5 DECLARE
6 m_val NUMBER;
7
8 CURSOR c1
9 IS
10 SELECT ri_sys_id, pb_sys_id
11 FROM ot_req, ot_po_breakup
12 WHERE ri_sys_id = pb_ri_sys_id AND ri_item = :NEW.si_item;
13 BEGIN
14 FOR i IN c1
15 LOOP
16 INSERT INTO ot_ship_breakup
17 VALUES (i.ri_sys_id, i.pb_sys_id, :NEW.si_sys_id, :NEW.si_qty);
18 END LOOP;
19 END;
20 /
Trigger created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER ot_ship_del
2 AFTER DELETE
3 ON ot_ship
4 FOR EACH ROW
5 DECLARE
6 v_pi_qty NUMBER;
7 v_least NUMBER;
8 v_pb_ri_qty NUMBER;
9 v_sys_id NUMBER;
10 BEGIN
11 -- form trigger deletes from ot_ship_breakup:
12 -- DELETE FROM ot_ship_breakup WHERE sb_si_sys_id = :OLD.si_sys_id;
13 v_pi_qty := NVL (:OLD.si_qty, 0);
14 v_sys_id := :OLD.si_sys_id; -- quantity to remove
15 -- remove old values:
16 FOR s IN
17 (SELECT ot_req.ri_sys_id AS sb_ri_sys_id,
18 ot_po_breakup.pb_sys_id AS sb_pb_sys_id
19 FROM ot_req, ot_po_breakup
20 WHERE ot_req.ri_sys_id = ot_po_breakup.pb_ri_sys_id
21 AND ot_req.ri_item = :OLD.si_item)
22 LOOP
23 FOR r IN
24 (SELECT *
25 FROM ot_po_breakup
26 WHERE pb_ri_sys_id = s.sb_ri_sys_id
27 AND pb_sys_id = s.sb_pb_sys_id)
28 LOOP
29 IF v_pi_qty > 0
30 THEN
31 v_least := LEAST (v_pi_qty, NVL (r.pb_ves3q, 0));
32 UPDATE ot_po_breakup
33 SET pb_ves3q = pb_ves3q - v_least
34 WHERE pb_ri_sys_id = r.pb_ri_sys_id
35 AND pb_pi_sys_id = r.pb_pi_sys_id;
36 v_pi_qty := v_pi_qty - v_least;
37 v_least := LEAST (v_pi_qty, NVL (r.pb_ves2q, 0));
38 UPDATE ot_po_breakup
39 SET pb_ves2q = pb_ves2q - v_least
40 WHERE pb_ri_sys_id = r.pb_ri_sys_id
41 AND pb_pi_sys_id = r.pb_pi_sys_id;
42 v_pi_qty := v_pi_qty - v_least;
43 v_least := LEAST (v_pi_qty, NVL (r.pb_ves1q, 0));
44 UPDATE ot_po_breakup
45 SET pb_ves1q = pb_ves1q - v_least
46 WHERE pb_ri_sys_id = r.pb_ri_sys_id
47 AND pb_pi_sys_id = r.pb_pi_sys_id;
48 v_pi_qty := v_pi_qty - v_least;
49 END IF;
50 END LOOP;
51 END LOOP;
52 END ot_ship_del;
53 /
Trigger created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> -- DML and results:
SCOTT@orcl_11gR2> INSERT INTO ot_req (ri_item, ri_sys_id, ri_qty) VALUES ('AA', 1, 20)
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> SELECT * FROM ot_req
2 /
RI_ITEM RI_SYS_ID RI_QTY
-------------------- ---------- ----------
AA 1 20
1 row selected.
SCOTT@orcl_11gR2> INSERT INTO ot_po (pi_item, pi_qty, pi_sys_id) VALUES ('AA', 10, 33)
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> SELECT * FROM ot_po
2 /
PI_ITEM PI_QTY PI_SYS_ID
------------ ---------- ----------
AA 10 33
1 row selected.
SCOTT@orcl_11gR2> SELECT * FROM ot_po_breakup
2 /
PB_RI_SYS_ID PB_PI_SYS_ID PB_RI_QTY PB_VES1Q PB_VES2Q PB_VES3Q PB_SYS_ID
------------ ------------ ---------- ---------- ---------- ---------- ----------
1 33 20 10 1
1 row selected.
SCOTT@orcl_11gR2> INSERT INTO ot_ship (si_item, si_qty, si_sys_id) VALUES ('AA', 10, 44)
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> SELECT * FROM ot_ship
2 /
SI_ITEM SI_QTY SI_SYS_ID
------------ ---------- ----------
AA 10 44
1 row selected.
SCOTT@orcl_11gR2> SELECT * FROM ot_ship_breakup
2 /
SB_RI_SYS_ID SB_PB_SYS_ID SB_SI_SYS_ID SB_REF_QTY
------------ ------------ ------------ ----------
1 1 44 10
1 row selected.
SCOTT@orcl_11gR2> DELETE FROM ot_ship
2 /
1 row deleted.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> SELECT * FROM ot_ship
2 /
no rows selected
SCOTT@orcl_11gR2> SELECT * FROM ot_ship_breakup
2 /
SB_RI_SYS_ID SB_PB_SYS_ID SB_SI_SYS_ID SB_REF_QTY
------------ ------------ ------------ ----------
1 1 44 10
1 row selected.
SCOTT@orcl_11gR2> SELECT * FROM ot_po_breakup
2 /
PB_RI_SYS_ID PB_PI_SYS_ID PB_RI_QTY PB_VES1Q PB_VES2Q PB_VES3Q PB_SYS_ID
------------ ------------ ---------- ---------- ---------- ---------- ----------
1 33 20 0 1
1 row selected.
|
|
|
|
| Re: Complex trigger [message #553600 is a reply to message #553550] |
Mon, 07 May 2012 02:37 |
|
|
|
Thanks very much Barbara, you seem to have solution for almost any problem ,with true respect i highly appreciate your efforts in making my project a success.I also learned many things from you in course of this project , i would just want to share some ideas with you like how to improve on building programming logics and skills , are there any helpful websites you can suggest me ,by which i can improve the skill .Again thanks very much and may God bless you.Hat's off to you.Appreciate if you can give me your contact info like email or phone.
|
|
|
|
| Re: Complex trigger [message #554356 is a reply to message #553600] |
Sun, 13 May 2012 18:31 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Different people find it easier to learn in different ways. Some people do better with classes, some people do better with reading books, some people do better with reading the documentation, some people do better with browsing forums, carefully reading problems, and solutions, and trying to solve increasingly complex problems. Most of us do best with a combination of such things.
If you can get your company to send you to any classes taught by Oracle corporation, I believe that would help. I would start with the Introduction to Oracle SQL and PL/SQL, then PL/SQL programming units, then Advanced PL/SQL. If not, then you may be able to find some inexpensive classes at a junior college or some such thing. There are lots of books out there and you can find many of them at public libraries. The documentation is an excellent reference, but can be rather dry reading. This forum and the OTN forums and asktom.oracle.com are good forums to browse. There is also a separate sub-forum on these forums for advice about training and certification.
In the future, I believe you will find that, if you post a proper test case, with create table and insert statements that you have already tested, posted in a format that we can copy and paste in order to run it on our systems, along with a clear explanation of the problem and examples and some attempt of your own, you will find that there are plenty of other people capable and willing to help and you will likely receive quicker helpful responses. Many times you will find that if you just put together a good test case and description of a problem, you discover things that enable you to solve it in the process.
You can send private messages (PM's) and emails through this forum, but in general, I just tell people that do so to post their problems on the forums, so that everyone can contribute and learn, so it is quicker just to post it on the forum to begin with. I do not give out my phone number.
When you encounter problems, you need to go about analyzing them in a logical manner. Write and test one piece of code at a time and re-test each time that you add another little piece, so that you know where the problem starts. When you receive an error, look up the full text of the error message, look at the line that the error occurs on and the lines immediately below and above and try to figure out what is wrong. Search the internet for similar problems.
|
|
|
|
| Re: Complex trigger [message #554375 is a reply to message #554356] |
Mon, 14 May 2012 00:56 |
|
|
|
Thanks very much barbara, actually my company doesnt provide any training programs, all i have to do is learn on myself and i will follow the instructions given by you , moreover i am in a country where the resources for learning are very few and we are at remote place in desert and the office hours also are long.Anyway i will start with documentations that i have and i will keep in check with this forum.Really appreciate your help rendered to me.
|
|
|
|
Goto Forum:
Current Time: Wed Feb 05 08:48:03 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
