Fetching data with parameter from one table to another

Question

I would like to fetch data from one table to another, given the parameter below..

So I have 4 tables, they are

M_InternalRequester, M_InternalRequesterLine, M_Inventory, M_InventoryLine

M_InternalRequester

-----------------------
m_internalrequester_id 
-----------------------
1001                   

M_InternalRequesterLine

------------------------------------------------------------------------------
m_internalrequesterline_id || m_internalrequester_id || m_product_id || qty || 
------------------------------------------------------------------------------
3001                       || 1001                   || 21001        ||  3  ||
3002                       || 1001                   || 21002        ||  4  ||

M_Inventory

----------------------------------------------------------------------
m_inventory_id || description             || m_internalrequester_id ||
----------------------------------------------------------------------
8001           || Referred from Internal  ||  1001                  ||

M_InventoryLine

--------------------------------------------------------------
m_inventoryline_id || m_inventory_id || m_product_id || qty || 
--------------------------------------------------------------
???????????        ||  8001          ||  ??????????? || ??  ||
???????????        ||  8001          ||  ??????????? || ??  ||

I have data that previously recorded in M_Internal Requester and M_InternalRequesterLine

I would like to fetch data from M_InternalRequesterLine to M_InventoryLine, based on the parameter m_internalrequester_id given in table M_Inventory

I've made a trigger like this

create or replace trigger TG_AI_M_INVENTORYSN
before update on m_inventory
for each row
declare
  internalrequester_id number;
  invline_id number;

CURSOR c1 is
select
    M_PRODUCT_ID, QTY
from m_internalrequesterline
where m_internalrequester_id=:new.m_internalrequester_id;

BEGIN

if inserting then 
SELECT M_INTERNALREQUESTER_ID INTO INTERNALREQUESTER_ID FROM M_INTERNALREQUESTER WHERE
m_internalrequester_ID = :new.m_internalrequester_id;

FOR insertline in C1
LOOP
select currentnext into invline_id from AD_Sequence WHERE
name = 'M_Inventoryline';

INSERT INTO M_INVENTORYLINE 

(m_inventoryline_id, m_product_id, qty
)
VALUES 
(invline_id, insertline.m_product_id, insertline.qty);


update AD_Sequence set currentnext=invline_id+1 where name = 'M_Inventoryline';
END LOOP;
END IF;


end;

It was created but when I execute it won't work. What is the problem and how can I solve it ?

Answer 1

This is BEFORE UPDATE trigger according to this declaration:

create or replace trigger TG_AI_M_INVENTORYSN
before update on m_inventory

so the trigger is fired only before update statements.

---

The IF statement is using INSERTING predicate to check if a trigger is fired after or before INSERT statement.

if inserting then ....

---

Because the trigger is fired only before UPDATE, the INSERTING predicate is always false, and a code between THEN and END IF is never executed.