1

Possible Duplicate:
Oracle table change monitor

This is the question that is asked to me in one of the interviews:

I need to call some java method as soon as something gets updated in database. Can I call some java method through SQL trigger?

Community
  • 1
  • 1
Sunny Gupta
  • 6,929
  • 15
  • 52
  • 80
  • My solution was my SQL Trigger will generate a particular file on hard drive, One of my java thread will continuously poll for same location if some file has got generated as soon as file comes we can execute our work in run method's while loop. – Sunny Gupta Dec 22 '11 at 06:55
  • Don't write more details about question in comment, you can edit your question. – Harry Joy Dec 22 '11 at 06:56
  • http://docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm#CACEGFCH – Mithun Sasidharan Dec 22 '11 at 06:57
  • 1
    possible duplicate of [Oracle table change monitor](http://stackoverflow.com/questions/337335/oracle-table-change-monitor), see also http://stackoverflow.com/questions/812233/getting-events-from-a-database – Mat Dec 22 '11 at 06:59

2 Answers2

2

instead of polling main table, I would suggest to have one more table(with columns time stamp, reason) which contains summary of changes to main table. This improves the query performance.

Top 1 query on summary table gives the answer.

Balu
  • 128
  • 6
1

Generally, you should avoid filling the db ffom multiple apps. That's not always possible, so you need some ad-hoc mechanism. Polling is the best you can do, but you can simply poll the table you need, or another table populated by a trigger.

Invoking java from the database would violate your layer bounderies, and is a bad thing. Even if the DB somehow supports a magic callback mechaniam, I'd still be hesitant to use it.

Bozho
  • 588,226
  • 146
  • 1,060
  • 1,140