0

I have an oracle table which is updated by an external spring boot application. In my spring boot application, I want to create a database trigger so that whenever there is an update or insert operation happens on that table, my spring boot application can call a method in the class with new values.

Can someone please share an example with similar kind of set up.

APK
  • 155
  • 1
  • 15
  • Oracle PL/SQL will not have visibility of anything in your app directly. You would either have to 1) let your app poll the data periodically; 2) perhaps use Oracle Advanced Queueing and have your app poll a queue in the DB with messages from the trigger containing the new values; or 3) have your app provide a REST API that could be called by the trigger. Anything you do could affect the performance of the other app too, by adding more work and/or dependency to the insert/update transactions. e.g. trigger processing is part of the transaction and a failed API call could fail the transaction. – pmdba May 03 '22 at 11:02
  • Not sure if *any* of those options are viable in your situation: can't tell how difficult it would be to identify changed rows *without* a trigger; not sure about the SQL performance issues of polling the data directly; not sure if your app can work with Oracle AQ; not sure you want to create a dependency between your app and the other app (using an API, yours would have to be running in order for the other to process *any* transactions on the data). Too many unknowns in your situation to guess; you really, really need to come here with a specific code issue and not general design questions. – pmdba May 03 '22 at 11:12
  • @pmdba thank you for your suggestions, To your 1st approach, the table is not getting updated frequently, so not sure if polling would be feasible. About your 3rd approach, I couldn't understand it, If I implement a trigger in my springboot application then why do I need to provide an API?? OR I can only create a trigger in the application which updates the table is that the case ??? Can't I implement something like the below link. https://docs.oracle.com/en/database/oracle/oracle-database/12.2/jjdev/calling-Java-from-database-triggers.html#GUID-5C498DEF-0348-484D-AA26-2A88EF348D5C – APK May 03 '22 at 11:55
  • @pmdba can't we create a trigger in spring boot application like we create store procedures, I am new to the oracle database (haven't worked much on databases side). – APK May 03 '22 at 13:56
  • The trigger is created within the database and executes within the context of the database session performing the data transaction, not from within your application. Database constructs will have no visibility of your external classes, code, or application state. The example you linked to is for calling a Java stored procedure - also in the database and executing within the context of the transaction, not your external application. It would have to reach back over the network to your app, which would need an API for the DB to interact with. – pmdba May 03 '22 at 16:08
  • Also consider the dependency you're creating between the apps: what if the app changing the data wants to add new rows, but the second app is down? Do you fail the transactions until the second app is up? Do you queue the change records asynchronously with AQ so the second app can pick them up later? Do you have some other way to poll the data so you know what has changed since last you checked? What happens if the same record is changed *twice* while your app is down? Do you need to capture both changes, or just the most recent? Using AQ avoids dependency and increases robustness of your app. – pmdba May 03 '22 at 16:20

0 Answers0