Home » SQL & PL/SQL » SQL & PL/SQL » Complex trigger (Oracle 10.0.2.1)
Complex trigger [message #553460] Fri, 04 May 2012 14:52 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 #553466 is a reply to message #553460] Fri, 04 May 2012 15:47 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
"it keeps failing" is not a valid Oracle error message.
Re: Complex trigger [message #553473 is a reply to message #553460] Fri, 04 May 2012 19:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 #553507 is a reply to message #553505] Sat, 05 May 2012 18:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Two of your triggers refer to a column prb_sys_id that does not exist in any of the tables that you have provided, so when I try to run your code, the triggers do not compile. You need to either provide the correct table structures or fix the column name in your triggers. There may be other errors. I just stopped when I encountered the first one. This is still obviously not an actual copy and paste of a complete execution of a test script. Every time that you post such things, all you do is delay getting help for perhaps another day or more.
Re: Complex trigger [message #553512 is a reply to message #553507] Sun, 06 May 2012 00:57 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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.
Previous Topic: ERROR: ORA-29280: invalid directory path
Next Topic: SQL Query
Goto Forum:
  


Current Time: Wed Feb 19 14:23:20 CST 2020