RE: Notification when something changes in the DB

Thanks to everybody who replied. We already have a solution similar to this implemented (via triggers) which allows us to not re-query the databse before doing an update, since the fields we are interested in are spread across multiple tables, etc., etc. I also realize that there multiple ways of doing it.

What I was really after is seeing if there is any "Oracle native" solution referred to by this 3rd party company or they were just filling up the review paper with smart cracks :-)

I guess, as I suspected, they were.

Thanks again to everybody who replied!

Val Gamerman.

To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:        (bcc: Val Gamerman/Victoria Financial)
Date:     09/05/2001 01:41:06 PM GMT

John,
we are using ADO, VB and ASP pages for our application. all transactions are stateless, much the same way you are explaining your system.
To ensure that an update can occur with a high degree of confidence that the
record did not change, we do the following: Every database table has a "last_update_date" column that is updated via a database trigger whenever the record is created or updated. Every query from the database selects this column using the to_char function to return the date string in mm/dd/yyyy hh24:mi:ss format. All updates are performed by stored procedures. When the web page needs to update a record, the date string is passed in as one of the parameters. The first thing the procedure
does is to re-query the record, getting the date string that is currently stored in the database. The two date strings are compared. If they differ,
an error is returned to the user indicating that the record was updated by another user, otherwise, the record is updated, and a new date-string is passed back to the web page.
there are probably a couple of hundred different ways to do this, but this is simple and it works for us.
hope this helps.
Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Tuesday, September 04, 2001 5:52 PM To: Multiple recipients of list ORACLE-L

>For whatever reasons this solutions was found to be not sound by a 3rd
>party "consulting" company which reccommended "Oracle native technology"
>to perform this check.

Hmm... a trigger seem native to me. Perhaps they meant auditing. -----Original Message-----
[mailto:Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com] Sent: Tuesday, September 04, 2001 2:33 PM To: Multiple recipients of list ORACLE-L

Hello fellow DBAs',
I have a weird question that I was asked. My first reaction was to answer: "There is no such thing", however I was talked into posting this question here :-)
1. An applicaiton selects certain records (from different tables) from a database..
2. When time comes to update some of these records in the datbase application needs to know if records that it's about to update have been modified by some other user.
3. Currently this is achieved with via triggers. Before performing an update application checks if trigger updated a certain field in the database which serves as an indication that records/fields of interest have been updated.
For whatever reasons this solutions was found to be not sound by a 3rd party "consulting" company which reccommended "Oracle native technology" to perform this check. This company is now unreachable and "management" is requesting to change the application to follow this "review". Management explanation of how it needs to be done is based on what ADO would do where if you perform an optimistoc lock and then later on try to update an already updated record it would tell you about this upfront., or you would be able to check upfront WITHOUT requiring the DB. I am not familiar with ADO, that much so I can not comment on it. Can somebody tell me if they can think of way to achieve this? I know it sounds rather weird request -- it is. Thanks in advance for any thoughts on this, Val Gamerman.
Oracle DBA

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Lewis
  INET: jlewis_at_punchnetworks.com
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).