[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 685
  • Last Modified:

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?
0
Schuchert
Asked:
Schuchert
  • 5
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean, trying to run this will not work/raise an error/block/... ?
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;      
  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;
0
 
SchuchertAuthor Commented:
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.
0
 
SchuchertAuthor Commented:
It seems to only run if we leave the send_mail piece in.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is the trigger actually valid when you apply it?
0
 
schwertnerCommented:
1.
This is not a definition of a trigger.
The trigger event is not defined.
So possibly the trigger sometimes doesn't fire.

2.
Look at
SUBSTR(comp_old,0,4);  
This is C or Java flavour notation

In Oracle the count of the characters begin from 1.
Correct will be

SUBSTR(comp_old,1,4);  

3. As AngelIII correctly said an EXCEPTION section will help you to
get more info aboput some errors in the code that Oracle doesn't
tell you but that stop the program



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,1,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);  
 EXCEPTION
  WHEN OTHERS THEN
     dbms_output.put_line('Errors by the Export encountered.');
     dbms_output.put_line(substr(sqlerrm,1,254));  
END assy_build_items_D_Trigtest;

You can also INSERT in an table the value
substr(sqlerrm,1,254) .
0
 
SchuchertAuthor Commented:
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.

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now