Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Database Insertion - auto notification

Options
  • 09-07-2008 6:01pm
    #1
    oochie
    Closed Accounts Posts: 39 ✭


    Hi,

    I want to create a component for an application that monitors a table in a database for inserts/updates. When an insert/update occurs, I want to construct a notification email (probably using apache velocity) based on information from a few database tables, and then send an email. I dont have control over whats inserting the information to the database.

    The other parts of the application are all writeen in Java so Im wondering has anyone any suggestions on a good approach to achieving this. Would it be just a case of creating some sort of thread that checks the database at regular intervals for updates and then sends a notification? Or is there a better aapproach?

    Thanks,
    Colm


Comments

  • Options
    #2
    Tom Dunne
    Registered Users Posts: 23,212 ✭✭✭✭Tom Dunne


    Depends on the database, but that should be fairly easy to implement.

    I'm coming from an Oracle background, but I would imagine it would be the same in other databases - you basically want a trigger on the insert/update that would fire a stored procedure to send the e-mail. Very straight forward to do (in Oracle at least :)).


  • Options
    #3
    Cantab.
    Closed Accounts Posts: 1,444 ✭✭✭Cantab.


    I believe the latest versions of MySQL support events.


  • Options
    #4
    oochie
    Closed Accounts Posts: 39 ✭oochie


    yes it will be an oracle database so i could set up a trigger like you suggest.

    The email i want to compose before sending will need to pull information from a number of sources though. So it will need to populate different subject fields, and various other placeholder text in the email. Would this also be fairly straight-forward with an oracle trigger?


  • Options
    #5
    Tom Dunne
    Registered Users Posts: 23,212 ✭✭✭✭Tom Dunne


    oochie wrote: »
    yes it will be an oracle database so i could set up a trigger like you suggest.

    The email i want to compose before sending will need to pull information from a number of sources though. So it will need to populate different subject fields, and various other placeholder text in the email. Would this also be fairly straight-forward with an oracle trigger?

    The trigger will be trivial enough, the procedure the trigger calls will be where the work is done.

    As for the sources, what do you mean? If they are other tables, no bother. Even if they are external files, it should be no problem to Oracle.

    Here's an example from Oracle's website, with working code.


Advertisement