Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 234 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.3K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 157 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 389 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
After inserting trigger is not working as expected.

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;
Answers
-
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).
-
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.
-
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.
-
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.
-
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_'