DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Trigger : Need help with Coding Error Condition

  1. #1
    Join Date
    Nov 2003
    Posts
    31

    Trigger : Need help with Coding Error Condition

    Hi,

    Here is a trigger I have written. It is working fine. I just need to modify the error condition. Instead of WHEN NO DATA-FOUND NULL, I need to change it insert rows nto an error table. This table has the following structure.

    event_id varchar2(100),
    old_gid number(15),
    new_gid number(15),
    creation_date date;

    Whenever there is an error condition, instead of inseting into the ih_extract_company or ih_extract_address table, the values should binserted into the error table using the exact same logic below.

    Any help would be greatly appreciated.

    Thanks,
    Sankar.


    CREATE OR REPLACE TRIGGER trig_ih_extract
    AFTER INSERT ON iface_qevent_attrs FOR EACH ROW
    DECLARE
    v_event_class iface_qevent.event_class%TYPE;
    n_oldcgid gcr_company.company_id%TYPE;
    n_oldagid gcr_address.address_id%TYPE;
    BEGIN
    BEGIN
    SELECT event_class
    INTO v_event_class
    FROM iface_qevent
    WHERE event_id = :new.event_id;

    IF ( v_event_class = 'RegisteredCompany::registryIdReplaced'
    and :new.attr_name = 'registryId' ) THEN
    insert into ih_extract_company (event_id, old_cgid, new_cgid, cflag,
    creation_date, last_modified_dt)
    values (:new.event_id, :new.attr_value, null, null, sysdate, sysdate)
    ;
    ELSIF ( v_event_class = 'RegisteredCompany::registryIdReplaced'
    and :new.attr_name = 'replacementRegistryId' ) THEN
    update ih_extract_company set new_cgid = :new.attr_value,
    cflag = 'N', last_modified_dt = sysdate where event_id = :new.even
    t_id;
    END IF;

    IF ( v_event_class = 'RegisteredCompany::addressRegistryIdReplaced'
    and :new.attr_name = 'addressRegistryId' ) THEN
    insert into ih_extract_address (event_id, old_agid, new_agid, aflag,
    creation_date, last_modified_dt)
    values (:new.event_id, :new.attr_value, null, null, sysdate, sysdate)
    ;
    ELSIF ( v_event_class = 'RegisteredCompany::addressRegistryIdReplaced'
    and :new.attr_name = 'replacementAddressRegistryId' ) THEN
    update ih_extract_address set new_agid = :new.attr_value,
    aflag = 'N', last_modified_dt = sysdate where event_id = :new.even
    t_id;
    END IF;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    END;
    END;
    /

    SHOW ERRORS
    Sankar B. Mandalika

  2. #2
    Join Date
    Nov 2003
    Posts
    31
    On second thoughts, if its too hard to
    run thro' the logic of the above
    trigger, if you can kindly help me with the following, that would be great:

    If there is an error, fill the event_id and new_cgid/new_agid into the
    event_id and new_idfields of the error table.

    (Note: It can be new_cgid or new_agid as the case may be).
    Sankar B. Mandalika

Posting Permissions

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


Click Here to Expand Forum to Full Width