Metin Gönen wrote:

> I want to make it clear that ; when we are inserting/updating/deleting a
> record from table B
> if we use triggers for table B ( using only the results from the record
> which caused the trigger to fire) ,
> to update fields in table A, there will be no lock conflict.
> Also this is nothing to do with transactions autocommit property . Is it
> right ?

Unless you are trying to modify the same record in table A from another
user there should be no problem. I do see problems when two users are
trying to 'sell' the same stock, in which case you *DO* need to handle
the conflict. But that is just a matter of the order you allocate the
stock. I commit each item 'sale' so that everybody else knows that stock
is in use, and then rather than rolling back a complete invoice, I
'undo' each commit with a delete if the order is cancelled ;) People can
see stock available, and stock currently allocated, but not yet sold.
99x out of 100 the sale completes, so the stock is as good as sold once
allocated.

> Again , in this trigger if I visit some other records in table C or table D,
> Will there be a lock conflict ?
> ( not using records from Table B but gathering information from other tables
> )

Again, the trick is to keep to a minimum the information that is
modified. The triggers are not fired until a commit is made, so
everything should be stable, and nothing should lock because the commit
happens quickly. The problem comes when you start trying to scan
multiple records which other users may also be accessing ...

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services