Oracle trigger only works after sending an email
Posted on 2007-10-19
We are creating a trigger that will NULLify 2 fields in Table B after Table A has a correlating record deleted from it. So for example if the item "RCPI12345" is deleted from assembly_build_items then the record "RCPI12345" in barcode_items should have the assembly_no and assembly_date set to NULL.
It works for us but only when we leave in the send_mail procedure. If we comment it out or remove that line then it no longer works. We were using the sendmail just to help us output what the variable was set to. But now Oracle is requiring it for some reason. We can't figure out why.
This is what we have for our trigger:
comp_old varchar2(200) := :old.assy_item;
comp_substring := SUBSTR(comp_old,0,4);
if comp_substring = 'RCPI' then
select b.barcode into v_barcode from barcode_items b
where b.barcode = :old.assy_item;
if v_barcode is not null then
update barcode_items set assembly_no = null, assembly_date = null where barcode = v_barcode;
INSERT INTO assembly_build_items_events (table_name, deleted, assembly_no, date_time, get_name, old_value)
VALUES('Assembly Build Items', 1, :old.assembly_no, current_date, user, :old.assy_item);
Any ideas why this trigger only works if the send_mail piece is in there?