I've got a database getting used by two different programs. Application1 will work all of the procedures within the database. We don't have total control about this application
Application2 will have to be notified when there's a addition, modification or deletion of the record from the table. But we can't write some triggers in sql.
Example: There's a billing software which adds a bill towards the bills table and also the particular record within the customer table is going to be up-to-date using the new points acquired in the bill. We now have another software for analysis which retrieves the information in the clients table and problem some vouchers for them dynamically. I can not loop or query the db often unless of course it's the only option left.
I should also determine if there's an element in database engine.
Any suggestions please..
You might have a look in the following article.
App1 transmits a note to App2, before/following the DB manipulation.
Would you provide us with some context, for instance exactly what the application is, exactly what the changes are etc? Could it be just triggers which are prohibited? (Why?).
You may be left polling the DB inside a loop, checking
SELECT MAX(id) FROM table to ascertain if the ID is larger than before (i.e. new things can there be). Without triggers you cannot see modification or deletion, unless of course you authored App1 and may get it email an audit table simultaneously, logging all procedures.
"But we can't write some triggers in sql."
That's the only method possible, if you would like the body to become reliable and when you would like it provide correct information to the customers whatsoever occasions (and when a potential way there's - see further).
Someone clarified "App1 transmits a note to App2", that the remark was handed "Not Acidity compliant". The remark is correct (given some reasonable interpretation from the term 'ACID'). The thing is, your app2 only really wants to receive, say, place notices, at the stage where it's very sure that the place really has happened. That time of certainty is just arrived at once the DBMS has effectively committed this kind of place.
Hence, the trigger you'd require is a type of trigger that runs following a succesfull commit (do individuals exist ?).
And so the problem becomes, what happen to the committed data in the event that commit trigger fails unconditionally ? Your computer data is committed, your app1 thinks things are OK, however your app2 still has not received the notification it will have become.
The fix for your problem is your app1, as well as your DBMS, as well as your app2, really should be ready to participate in a 2-phase commit. I've little question that because you "canrrrt do almost anything to app1", it will likewise be impossible to get making it participate in a 2PC.
The "database audience" pattern is, basically, problematic design. A minimum of should you cherish "absolute consistency".
You should utilize database message push. Whenever a client adds, updates, and removes an archive, the customer can send a note to inform others. I understand the 3rdparty framework SocketPro can push messages from databases on window platforms http://www.udaparts.com/document/articles/dbpush.htm