-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|