DB触发器的应用实例

CREATE OR REPLACE TRIGGER CUX_BOM_TRI
AFTER
INSERT OR UPDATE
ON CUX_WIP_BOM
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
when (NVL(NEW.WIP_SUPPLY_TYPE,1) = 6)
DECLARE
l_return_err VARCHAR2(80);
BEGIN
BEGIN
insert into CUX_WIP_BOM1 nologging
select distinct
:new.Wip_Entity_Id
,bicv.item_num
,bbom.assembly_item_id
,msib.segment1
,msib.description
,bbom.uom
,bicv.component_item_id
,msib1.segment1 com_segment1
,msib1.description com_description
,bicv.primary_uom_code
,BICV.supply_subinventory
--,mil.segment1
,bicv.component_quantity
,BICV.wip_supply_type
from BOM_BILL_OF_MATERIALS_V bbom
,BOM_INVENTORY_COMPONENTS_V bicv
,mtl_system_items_b msib
,mtl_system_items_b msib1
--,MTL_ITEM_LOCATIONS mil
where bbom.organization_id=84
and bbom.bill_sequence_id=bicv.bill_sequence_id
and msib.inventory_item_id=bbom.assembly_item_id
and msib1.inventory_item_id=bicv.component_item_id
and bbom.bom_enabled_flag='Y'
AND BICV.disable_date IS NULL
and bbom.alternate_bom_designator is null
--and nvl(bicv.supply_locator_id,mil.inventory_location_id)=mil.inventory_location_id
and bbom.assembly_item_id= :new.COMPONENT_ITEM_ID;--:W_JOBS.Primary_Item_Id;
Exception when others then
null;
END;
EXCEPTION
WHEN OTHERS THEN
null;
END;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7730229/viewspace-1020421/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7730229/viewspace-1020421/