Unanswered: question with the trigger in oracle erp table?
Trying to create the following trigger
create or replace trigger ems_task_time_tri
after update of usage_rate_or_amount on bom_operation_resources
for each row
when (new.usage_rate_or_amount > 0)
-- local variables here
select wie.wip_entity_name,bos.operation_seq_num,mst.orga nization_id
from bom_operation_sequences bos,
where bou.assembly_item_id = msb.inventory_item_id and
bou.organization_id = msb.organization_id and
bou.routing_sequence_id = bos.routing_sequence_id and
bos.operation_sequence_id = ld.operation_sequence_id and
bos.standard_operation_id = bso.standard_operation_id and
wie.organization_id = msb.organization_id and
wie.primary_item_id = msb.inventory_item_id and
mst.inventory_item_id = msb.inventory_item_id and
mst.organization_id = msb.organization_id and
mst.language = 'ZHS';
if sql%found then
update ems_shoprow esr
set esr.er_de_hour = :new.usage_rate_or_amount
where esr.er_odd = n_odd
and esr.er_seq_code = n_seq
and esr.er_org_id = n_org_id;
but Oracle does not automatically executes a trigger when the specified conditions occur.
What does it do? Nothing, or do you get an error message?
Oracle usually fires its triggers - it doesn't work this way today and the other tomorrow. As the trigger seems to be correctly written, try to insert DBMS_OUTPUT.PUT_LINE into the trigger code to track its execution. Don't forget to enable output setting SET SERVEROUTPUT ON in your SQL*Plus session (or wherever you test it).
Error message might occur if SELECT statement doesn't return anything - you'll get NO-DATA-FOUND. SQL%FOUND does not capture this exception - you'll need to handle it in exception section of the trigger (WHEN NO_DATA_FOUND THEN ...).