Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006
    Posts
    4

    Unanswered: question with the trigger in oracle erp table?

    Hello All,

    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)
    declare
    -- local variables here
    n_odd varchar(200);
    n_seq number;
    n_org_id number;

    begin
    -- 得到任务号和工序号
    select wie.wip_entity_name,bos.operation_seq_num,mst.orga nization_id
    into n_odd,n_seq,n_org_id
    from bom_operation_sequences bos,
    bom_operational_routings bou,
    bom_standard_operations bso,
    wip_entities wie,
    mtl_system_items_tl mst,
    mtl_system_items_b msb
    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';

    -- 修改ems_shoprow的定额工时
    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;
    end if;
    ----------------------------------------------------------

    end ems_task_time_tri;

    but Oracle does not automatically executes a trigger when the specified conditions occur.

    Thanks much

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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 ...).

  3. #3
    Join Date
    Jun 2006
    Posts
    4
    no error message,Oracle does not automatically executes a trigger!

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did you use DBMS_OUTPUT.PUT_LINE as I've suggested? Could you post screen output when you do?

    BTW, is this trigger disabled?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •