After inserting trigger is not working as expected.

User_1BBI0

Hey,

I had created one trigger which is supposed to fired up after the new row entered in the table.

SImple process is that: The trigger will consist of 2 values: ID and table name, which it passes to procedure which call the external POST API.

Now, The procedure is working fine but upon checking the log in the API server, I got to understand that triggers are fired before the row is inserted as I was getting error of row not found in the API server.

I have my Triggers to be run after insert, no errors while compiling.

Here are the trigger and procedure, I am using:

***TRIGGER***

CREATE OR REPLACE TRIGGER 

MOB_TRANS_AF_INS

AFTER INSERT

  ON MOB_TRANS

  FOR EACH ROW

DECLARE

  table_name_ VARCHAR2(100) := 'MOB_TRANS';

  key_    NUMBER    := :NEW.transaction_id;

  

BEGIN  

  Add_Notification(table_name_, key_);

END;

***PROCEDURE***

create or replace PROCEDURE 

Add_Notification(

  table_name_ IN VARCHAR2,

  key_    IN VARCHAR2)

IS

  req_       UTL_HTTP.req;

  resp_      UTL_HTTP.resp;

    url_             VARCHAR2(4000) := 'http://dummy.apiurl.com/public/api/notification';    

  content_     VARCHAR2(4000) := 'table_name='||table_name_||CHR(38)||'unique_id='||key_;

buffer_ varchar2(4000);

BEGIN

  --rest API call

DBMS_OUTPUT.PUT_LINE('URL is: '|| url_);

  req := UTL_HTTP.Begin_Request(url, 'POST');

DBMS_OUTPUT.PUT_LINE('Request Begin');

  UTL_HTTP.Set_Header(req_, 'content-type', 'application/x-www-form-urlencoded');

DBMS_OUTPUT.PUT_LINE('Request content-type set.');

DBMS_OUTPUT.PUT_LINE('Content '|| content_);

  UTL_HTTP.Set_Header(req_, 'Content-Length', LENGTH(content_));

  DBMS_OUTPUT.PUT_LINE('Content '|| LENGTH(content_));  

  UTL_HTTP.Write_Text(req_, content_);

  resp_ := UTL_HTTP.Get_Response(req_);  

   

begin

  loop

   utl_http.read_line(resp_, buffer_);

   DBMS_OUTPUT.put_line(buffer_);

  end loop;

  UTL_HTTP.End_Response(resp_);

  EXCEPTION

    WHEN UTL_HTTP.end_of_body THEN

     UTL_HTTP.End_Response(resp_);

  End;

END Add_Notification;

Frank Kulash

Hi, @User_1BBI0

Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. (In this case, post some INSERT statements that should cause the trigger to fire, and the out[put from dmbs_output that you expect to see for each.) Always post your complete Oracle version (e.g. 18.4.0.0.0).

Lothar Armbrüster

Just a wild guess, but I think the trigger is fired before the transaction ends with commit or rollback. So your API server does not see the uncommitted new row.

BEDE

The trigger certainly fires before the transaction ends. And it does all that no matter if the transaction commits or is rolled back.

So, you need another approach if you wish that executed only when the transaction is committed. I'd rather go for the trigger writing to a log table and then having a job that loops through what is in that log table and does what you wish to do for each row, after which it deletes the processed row from the log table and commits.

BluShadow

As others indicate, you're putting your logic in the wrong place.

This is just the same as when people try and use triggers to fire an email off to someone... but then don't consider what happens if the transaction is rolled back (for whatever reason) after the trigger has fired. By that time the email has been sent and can't be stopped.

Likewise, issuing a POST doesn't account for if the transaction is rolled back, so the POST would happen regardless of whether the transaction is finally committed or not. On top of that (again as others have already indicated), the data isn't available outside of your current session until it's been committed, which will always be outside of the trigger code and inevitably after the API you've called tries to get at the data.

Your logic is application level logic and doesn't belong inside the database level. Have your application create the data on the database, then commit it, and then call the API.

EdStevens

Just as an aside, please give some more thought to your naming conventions, especially as applies to variable names within your pl/sql code.

Simply adding an underscore to a name (supposedly to distinguish it from a reserved word or name used elsewhere) is very prone to mis-reading when you are trying to debug your code. Here are some conventions I use to make names, unique, not colliding with reserved words, and self descriptive.

First, aside from the pl/sql code, all of my column names are in the format 'adective_noun'. So, for instance, instead of a column named simply ID (id of ... what?) I would name it 'emp_id', or 'po_id'; instead of 'name' (name of ... what?) I would name it 'employee_name' or 'model_name' or some such.

I'd then apply the same basic rule to all parms and variables in my pl/sql. If a parm or variable is providing or receiving data from a column, I'd name it the same as the related column, but with these easy to recognize distinctions:

All parms begin with 'p_'. So if a parm is related to the imp_id column, I name it p_emp_id. Some people go even further and distinguish between input and output parms with prefix of 'i_' or 'o_'.

All local variables begin with 'v_'. So if a variable is related to the emp_id column, I name it 'v_emp_id'.

Some other refinements are occasionally needed for specific use cases, but these rules have served me well for several decades. I hope you can see how this makes your code more readable and less error prone that simply 'name_'