Link to home
Start Free TrialLog in
Avatar of Schuchert
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@ourdomain.com',recipient@ourdomain.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_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);        
END assy_build_items_D_Trigtest;





Any ideas why this trigger only works if the send_mail piece is in there?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Schuchert
Schuchert

ASKER

No it doesn't error. When we delete the record it runs (or we think it does) but nothing is updated and no errors are returned.
It seems to only run if we leave the send_mail piece in.
is the trigger actually valid when you apply it?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

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.
It appears to be working. Thanks for the help from both of you.