SQL On-Update Trigger notifying Java; Service Activator Pattern?

[Thomas Taeger]

SQL On-Update Trigger notifying Java;
Service Activator Pattern?
Hi all,
imagine the following scenario:
- using a RDBMS / SQL in a very optimistic way due to performance:
- locally storing quasi-constant values retrieved by one initial database query and after that considering them to be constant until an ON-UPDATE trigger is raised in the database
- on such a very seldom on-update (on-delete...) trigger notifying all objects in the Java app server (unfortunately using those quasi-constants at the moment) somehow (exception, rollback, ...) that they must refetch the quasi-constants and repeat or rollback their last task.

My questions are:
1) Did anybody solve that?
2) In a standard manner?
3) How?
4) Would you call this an implementation of the Service Activator pattern? D. Alur et al.: "Core J2EE Patters", page 410, sais about the Service Activator pattern:
"Forces: ...
- An application needs to provide daemon-like service so that an enterprise bean can be in a quiet mode until an event (or a message) triggers its activity. ...
Solution: ...
The Service Activator may optionally send an acknowledgement to the client after successfully completing the request processing. The Service Activator may also notify the client or other services on failure events if it fails to complete the asynchroneous request processing. ..."
In my scenario there is no real request processing but a daemon-like service.
5) How did your sql-trigger/storedProcedure/... call back the Java method (implementing "Invoke Business Processing" provided in Fig. 6.2 on page 141)?
What do you think?
Thomas.
[ February 04, 2003: Message edited by: Thomas Taeger ]

[Thomas Hofmann]

My questions are:
1) Did anybody solve that?
No, I don't but I had a similar problem to solve
3) How?
The quasi constant values can be stored in the JNDI tree. You can implement an MDB that receives a message from the DB trigger. On each message the MDB refreshes the quasi constants in the JNDI tree. The app server objects (I assume SB, EB) have to query the JNDI tree (it is faster to query the JNDI tree than DB for each TX). The server objects can decide to rollback the transaction according to the new values.
5) How did your sql-trigger/storedProcedure/... call back the Java method
via JMS. For Oracle e.g. you have to write the PL/SQL trigger which calls a Java stored procedure. The Java stored procedure acts as the JMS client to the MDB, mentioned before. To call the MDB you must load the JMS jar into the DB. If you use Oracle and Weblogic you must load the weblogic.jar with ORacle command "loadjava". But I don't recommend Oracle 8 i for this. Oracle 9i should be better.
Hope this helps.
Thomas
What do you think?

[Thomas Taeger]

Hi Thomas,
thanks a lot! I will try this.
Thomas.

[Thomas Taeger]

Originally posted by Thomas Taeger:
SQL On-Update Trigger notifying Java;
Service Activator Pattern?
...
4) Would you call this an implementation of the Service Activator pattern? ...

Hi all,
Point 4) is still open. Can anybody enlighten me?
Thanks in advance,
Thomas.