Re: Trigger Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1999/06/25
Message-ID: <377d6fb3.63252322_at_newshost.us.oracle.com>#1/1


A copy of this was sent to "Ashwin" <ashwinkl_at_email.msn.com> (if that email address didn't require changing) On Fri, 25 Jun 1999 07:28:39 -0400, you wrote:

>Hi all !
>
>Here's the situation....
>
>I have two tables (table A and table B). Whenever a row in tableA is
>inserted,
>I need to get the count of the rows(based on a criteria) in table A and
>update table B. This needs to be done automatically.
>Using views doesn't help.
>
>Has any one come across a similar situation ?
>
>Any help is greatly appreciated.
>
>Thanks,
>Ashwin
>
>

This is harder then it sounds. In a multi-user environment you will have some issues unless you serialize access to table A based on your 'criteria'. If you let 2 people simultaneously insert into A rows that match that 'criteria' -- neither will see eachothers insert and may come to erroneous conclusions.

See

http://www.deja.com/[ST_rn=ps]/getdoc.xp?AN=476679796&fmt=text

for a discussion of this and the way to implement it using triggers. No mutating tables, no multi-user issues.

-- 
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Fri Jun 25 1999 - 00:00:00 CEST

Original text of this message