Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2014
    Posts
    1

    Unanswered: oracle trigger for updating child table by comparing parent table

    hi frnz,

    i have one issue while updating child table by comparing parent table's row status by a trigger in child table...

    is it possible? or is there any alternate soln?

    in both the tables only one field is common and it's sys_id...

    parent table...

    create table ot_so_head
    (
    soh_sys_id number(12) not null,
    soh_ph_sys_id number(12),
    soh_eh_sys_id number(12),
    soh_comp_code varchar2(12 byte) not null,
    soh_locn_code varchar2(12 byte),
    soh_txn_code varchar2(12 byte) not null,
    soh_no number(10) not null,
    soh_dt date not null,
    soh_doc_src_locn_code varchar2(12 byte) not null);

    and pk(primary key) is "soh_sys_id"

    child table...

    create table ot_doc_ref
    (
    dr_sys_id number(12) not null,
    dr_dh_sys_id number(12) not null,
    dr_txn_code varchar2(12 byte) not null,
    dr_file_name varchar2(2000 byte),
    dr_file_type varchar2(12 byte),
    dr_remarks varchar2(2000 byte),
    dr_cr_uid varchar2(12 byte) not null,
    dr_cr_dt date not null,
    dr_upd_uid varchar2(12 byte),
    dr_upd_dt date
    )

    my sk(secondary key) is 'dr_dh_sys_id'

    and my trigger is

    create or replace trigger "ot_doc_ref_test"
    before insert or update or delete on ot_doc_ref for each row
    declare
    m_dr_sys_id number;
    m_dr_dh_sys_id number;
    m_txn_code varchar2(12);
    l_soh_txn_code varchar2(12);
    l_soh_sys_id number;
    l_soh_status number;
    m_userid ow_fs.user_id%type;
    m_mode ow_fs.m_mode%type;
    cursor c1 is
    select soh_sys_id,soh_appr_status from ot_so_head where soh_appr_status = '1' and soh_sys_id = '721';
    begin
    m_userid := ornpkg_get_userid.m_userid;
    if updating
    then
    m_mode := 'UPDATE';
    m_dr_sys_id := :new.dr_sys_id;
    m_dr_dh_sys_id := :new.dr_dh_sys_id;
    end if;

    if c1%isopen
    then
    close c1;
    end if;
    open c1;
    fetch c1
    into l_soh_sys_id, l_soh_status;
    close c1;
    for i in c1
    loop
    if l_soh_status = '1' then
    if m_dr_dh_sys_id = l_soh_sys_id then
    if m_mode = 'UPDATE' then
    doc_ref_t1(:new.dr_file_name,
    :new.dr_remarks,
    :new.dr_sys_id,
    :new.dr_dh_sys_id,
    :new.dr_txn_code,
    :new.dr_file_type,
    :new.dr_cr_uid,
    :new.dr_cr_dt,
    m_userid,
    sysdate);
    else raise_application_error(-20555,'UPDATE CHK IS WORKING');
    end if;
    else raise_application_error(-20555,'SYS_ID CHK IS WORKING');
    end if;
    else raise_application_error(-20555,'DOCUMENT CHK IS WORKING');
    end if;
    end loop;
    end;



    "m_dr_dh_sys_id = l_tl_sys_id"

    how to compare both the pk and sk sys_id?

    pls suggest me......

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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