Compare Related Links

Need to update the last modified date field in a table when only certain fields in and outside of that table are modified. For example, there is a NEWS table. In this table there are the columns: "body", "ID", "headline" and "related_links". Whenever the body, headline or related_links fields are modified, the last modified date should be updated with today's date (system date). This can be accomplished using a trigger which I'm testing now. The problem is that one of these fields - related links, is not actually stored in the NEWS table but rather a link ID value is stored in this table. This ID is joined to another table called "WORKING_LINKS" that actually stores the link label and link name. If either the link label or link name is changed, the last modified date in the NEWS table must be updated. Therefore, a trigger won't work because the data is stored in the "working_links" table and not the "news" table. Need a different solution. ## Deliverables I will provide code and database layout to explain how the functionality works today and how it must be modified to work based on the desired functionality. Currently (as depicted in [login to view URL]), when a record is added to the NEWS table, a sequential link ID is stored in a field called "related_links_list_id". This column joins to the "cacheid" column in the WORKING_LINK table. When a related link is added BEFORE the actual saving of the record, the sequential "cacheid" is added to the working link table along with the "linklabel" and "linkdata" columns for the link. When the item is actually saved, the values are removed from the WORKING_LINK table and added to the LINK table. In the LINK table, the "link_label" and "link_data" columns are stored along with the "content_link_id" column that matches the "cacheid" column from the WORKING_LINK table (which is now removed). On update to the record however, the record isn't modified but rather a new record is added to the LINK table (see last screen shot in attachment). When this new record is added, the old link is flagged as "N" in the "current" column and the "content_link_id" is updated based on the former "cacheid" column. This ID number is then added to the "related_links_list_id" in the NEWS table. If the item was updated again, a new record would be stored temporarily in the WORKING_LINKS table with a "cacheid" of 20. Once the data was saved, this record would be removed and a third record would be added to the LINK table with a "content_link_id" of 20. Also, in the NEWS table, the "related_links_list_id" would be updated as LINKSID:20. This mechanism makes it difficult to determine whether the related link has been modified because a new record (and not modified record) is added to the LINK table. This record must be compared to the value that has been removed from the WORKING_LINK table and NOT to the previous row in the LINK table as this is just a sequential ID and not necessarily the same record that has been modified. That is - multiple links can be added. I think that a SQL cursor used within a stored procedure might work and I have an example of this but I'm unsure how to use it. Another approach would be through Java code, where an object is stored that represents the WORKING_LINK table and is compared to an object that represents the LINK table after the save. If values are different for the "link_label" or "link_data", then the last modified date is updated to the system date in the NEWS table. I will add the Java code that stores the working link data and link data tomorrow morning. I am attaching a zip with the graphical depictions of the data storage, the possible SQL stored procedure code and table schema now. * * *This broadcast message was sent to all bidders on Thursday Feb 4, 2010 4:14:52 PM: I am attaching a very detailed description of the problem. It is titled "[login to view URL]". Please review this document before bidding. It should clear up any ambiguity. Also, please take a look at the SQL code that was previously attached within the zip file to ensure you understand the stored procedure approach which will probably work the best. * * *This broadcast message was sent to all bidders on Thursday Feb 4, 2010 7:55:22 PM: With regard to the previously attached SQL code, please note that there are other types of content besides Article that must be updated with the sys date when a change is made to certain fields. This one of the reasons why the SQL is so complicated. It's because it's actually doing an "else if" for each distinct content type. The other types are: Bundle Page HTML Attachment I am working on a detailed document to cover the Java code. I will post later tonight. * * *This broadcast message was sent to all bidders on Friday Feb 5, 2010 12:20:10 AM: I just attached an updated zip file that contains the applicable Java code plus all the previous files that were already attached. I will be online first thing tomorrow morning (US Central time) to answer any questions. Thanks!