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;