Schuchert
asked on
Oracle trigger only works after sending an email
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:
DECLARE
ASSEMBLY_NO VARCHAR(200);
ASSY_ITEM VARCHAR(200);
v_barcode varchar2(100);
comp_old varchar2(200) := :old.assy_item;
comp_substring varchar2(200);
v2_barcode varchar2(100);
BEGIN
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;
cpdbuser.send_mail('from@o urdomain.c om',recipi ent@ourdom ain.com',' ','test',v _barcode);
if v_barcode is not null then
update barcode_items set assembly_no = null, assembly_date = null where barcode = v_barcode;
end if;
end if;
INSERT INTO assembly_build_items_event s (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);
END assy_build_items_D_Trigtes t;
Any ideas why this trigger only works if the send_mail piece is in there?
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:
DECLARE
ASSEMBLY_NO VARCHAR(200);
ASSY_ITEM VARCHAR(200);
v_barcode varchar2(100);
comp_old varchar2(200) := :old.assy_item;
comp_substring varchar2(200);
v2_barcode varchar2(100);
BEGIN
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;
cpdbuser.send_mail('from@o
if v_barcode is not null then
update barcode_items set assembly_no = null, assembly_date = null where barcode = v_barcode;
end if;
end if;
INSERT INTO assembly_build_items_event
VALUES('Assembly Build Items', 1, :old.assembly_no, current_date, user, :old.assy_item);
END assy_build_items_D_Trigtes
Any ideas why this trigger only works if the send_mail piece is in there?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It seems to only run if we leave the send_mail piece in.
is the trigger actually valid when you apply it?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well... this isn't it fully. We are using the Web GUI and the trigger event is defined in another field. This is the action that is performed when the event happens. I will try the exception idea and see what I get.
ASKER
OK, for whatever reason when we came back from lunch, it all appears to work. We ahave removed the send_mail and tried with and without the exception and it seems to work. I don't know if Oracle was being lazy or what but we are goign to continue testing for a bit.
ASKER
It appears to be working. Thanks for the help from both of you.
ASKER